AVERAGE is one of those easy functions in Excel. Like, super easy! It has just one argument, the cells, or numbers to be averaged. So what’s the problem? It’s too easy to get wrong, yet we might not notice for weeks, months, years or ever. Look at the screenshot below. The result of each method Read More
If you read a lot of Excel blogs and sites, you will see a common topic arise which encourages us all to use the INDEX MATCH formula combination, rather than VLOOKUP. I’m not going to disagree with that recommendation, however I feel the question of “Why is INDEX MATCH better than VLOOKUP?” is often not Read More
In Excel 2010, Microsoft introduced a new function called AGGREGATE. It is one of the most powerful functions in Excel, yet I’ve rarely seen it used in a real-life scenario. My aim with this post is to show you how good AGGREGATE is, so that you can make use of its power. What does Read More
Wouldn’t it be great to change the color of a worksheet tab based on a calculation? If you’re not convinced, here are some possible scenarios: When a schedule reconciles or includes a specific cell value If there is an error in a range of cells To color code tabs to match a contents page You Read More
Listing the files in a folder is one of the activities which cannot be achieved using normal Excel formulas. I could tell you to turn to VBA macros or PowerQuery, but then any non-VBA and non-PowerQuery users would close this post instantly. But wait! Back away from the close button, there is another option. For Read More
At the start of this year, I wrote a post entitled Turn a string into a formula with EVALUATE. This post used an Excel 4 Macro as a possible option for turning a string into a formula. These types of Macros were superseded when VBA was introduced in Excel version 5, hence why any macros Read More
In this post, I want to cover one of the most powerful lookup functions available in Excel, INDEX MATCH MATCH. Actually, to call it a function is poor terminology, as it is three functions used together within a single formula. Before digging into this formula, let’s look at when to use it. The screenshot above Read More
I’ve posted many times about named ranges in various guises. However, one thing I have not covered is relative named ranges. Relative named ranges don’t sound too exciting, but once you’ve read this post I’m sure there will be plenty of examples where you could have used them and now wish you had. Creating named Read More
I love using the double VLOOKUP trick to get some lighting fast calculation times in Excel. The drawback is that the list being looked up has to be sorted to prevent errors. If a list were not sorted, it would be great to see a warning. Then the user can take action to sort the Read More
From stock parts to holiday villas, from employees to logos, there are many reasons you may want to automatically change an image based on a cell value. In this post, we’ll explore 3 different methods to achieve this task: Named range + INDEX/MATCH + linked picture Chart fill + #N/A VBA User-Defined Function Each solution Read More