A few years back, I created a YouTube video about using slicers with formulas in Excel. It was a pretty popular video. That method used a dummy PivotTable, to act as the criteria for the formulas to calculate on. Today, I want to bring you a cleaner option for using slicers with formulas. This method … Read more
We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas. As great as PivotTables are for analysis, they are not always the best for presentation, which is why extracting data using a formula is so useful. We currently have … Read more
Tables are one of the best features of Excel. While it is possible to use the standard cell referencing with a Table, they have their own referencing style called structured references. We have to think a little differently to create a running total in an Excel Table using structured references. We will look at all … Read more
The INFO function in Excel returns information about the current operating environment. In total, there are seven pieces of information available: Table of Contents The arguments of the INFO function Argument options Basic usage Examples of using the INFO function Display system information Display warning messages Insert line breaks with CHAR function Conclusion Download the … Read more
The CHAR function in Excel returns a specific character based on a code number from the operating system’s character set. Each operating system has it’s own character set: The character set contains the primary printed and non-printed characters available on a computer. Those characters can be letters, numbers, punctuation, symbols, and even characters that are … Read more
Office 365 subscribers now have Excel’s new calculation engine available to them, meaning lots of users suddenly have access to dynamic arrays. This got me thinking about whether VBA user-defined functions (UDFs) can make use of the new functionality too. I’m not an expert in this; I’m just sharing as much as I’ve learned to … Read more
Data comes in all shapes and sizes, and since Excel is not a database, we face those shapes and sizes on a daily basis. One advantage of Tables in Excel is that they force us to apply some standards to our data structure, such as: There can only be one header row The headers must … Read more
The Excel team recently announced new dynamic array formulas, which can create unique lists, sort and filter with a simple formula. These new formulas are being rolled out to Office 365 subscribers over the next few months. However, not everybody has the subscription and will upgrade when it is sensible for their business, often combining it with … Read more
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