In this article we take a look at how to remove formulas from cells and replace them by their calculated values. But even more: We explore, how to deal with filtered lists or if you only want to convert some selected Excel cells to values. Let’s get started!
We have already explored the IFERROR function in Excel. IFERROR (and IFNA) provide great ways to catch errors in Excel formulas and functions. Now, let’s take it to the next level: Bulk insert IFERROR and IFNA to existing functions. That means, you have many functions already and just want to wrap IFERROR or IFNA around… Continue reading How to bulk insert IFERROR to existing Excel formulas!
This is a common scenario: You have sent an Excel file to a coworker and received the revised version back. Unfortunately, your colleague has only highlighted the changes using the strikethrough formatting. Even worse, in many cases only parts of cells are crossed out. But don’t worry – help is here! In this article you… Continue reading How to Find and Select All (Partial) Strikethrough Cells in Excel!
You probably don’t need this every day: But once a file you might want to have a list of all files within a folder or directory in Excel. The good thing: There are many methods available. If you Google it, you will find a lot of different methods to create a file list in Excel.… Continue reading List of all Files and Folders: How to Easily Insert a Directory in Excel!
Insert $-signs into Excel formulas right when entering the formula is quite easy: Either type the dollar sign manually or press F4 on the keyboard to insert a dollar sign. But what about existing formulas – that are different? For example, how to insert $-signs into a large range of cells at once? With just… Continue reading Insert $-Signs to Different Existing Excel Formulas: Simple and Fast!
The new XLOOKUP function in Excel not only offers great advanced features, but can be also used for 2D XLOOKUPs. Before XLOOKUP, the most common way for searching in rows and columns at the same time was INDEX/MATCH/MATCH. A combination of XLOOKUP and XLOOKUP can do the same. Let’s see how it works and explore… Continue reading 2D XLOOKUPs: How to use the new XLOOKUP for searching in two dimensions
We’ve explored the basics of XLOOKUP, discovered a wide range of advanced use cases for XLOOKUP and the advantages towards VLOOKUP and INDEX/MATCH. What we have not talked about so far is the performance of XLOOKUP. Especially for larger Excel models with possibly hundreds of thousands of functions to calculate, performance is a major concern.… Continue reading Performance of XLOOKUP: How fast is the new XLOOKUP vs. VLOOKUP?
It seems as if Microsoft has listened to many complaints of Excel users and introduced a new formula: XLOOKUP. It’s supposed to improve all the disadvantages of the “traditional” lookup functions VLOOKUP, INDEX/MATCH and SUMIFS. This article describes in what case and how to use it. Please feel also free to download all example in… Continue reading XLOOKUP in Excel: Easily Solve all VLOOKUP Problems!
The new XLOOKUP formula in Excel is great. It’s very powerful and solves many problems the iconic VLOOKUP (or HLOOKUP) formula cannot do. But there is one problem: It’s only available in the newest Excel versions. Older versions – which are still around a lot – can’t use them. In this article we explore 4… Continue reading Convert XLOOKUP to VLOOKUP With These 4 Easy Methods!
Excel is a great software. It’s easy to use (at least the basic functions…) and very flexible. Unfortunately, coming with the flexibility, users tend to misuse the options and disobey certain basic rules. One thing I’ve seen multiple times is to transport important information in the formatting of a cell. It might be the background… Continue reading Return Number Format Codes in Excel – 4 Ways to Get the Formatting Code from a Cell