I encountered a situation recently where I needed to convert text to a formula in Excel. I was expecting to find a function that would achieve this. We can convert text to a range using the INDIRECT function, so there has got to be a function to convert text to a formula… right? Well… no… yes, … Read more
Using named ranges in Excel is a core skill. Normally, we refer to a specific range. However, named ranges have a secret feature… the ability to use a relative named range. They don’t sound too exciting, but they open up a new world of opportunity, which you may have never considered before. Let’s start with … Read more
A weighted average (also known as the weighted mean) is a calculation that provides an average where values have different levels of importance. In certain scenarios, Excel’s AVERAGE function is not the right chose. It can provide misleading results as it assumes each value has the same impact on the result. Thankfully, we can easily … Read more
Excel has a dirty little secret – The data validation does not stop a user from entering an incorrect value. That’s pretty shocking, as that’s the whole point of data validation. 😲 Many of us rely on data validation to ensure other users only provide valid data entry. However, many are not aware of how … Read more
You’ve built an amazing dynamic report; a user selects a business unit to view, and everything updates automatically – it’s beautiful. The only issue is that Company A has sales of $30,000,000, Company B has sales of $30,000. Displaying values in 1 decimal place millions makes sense for Company A. $30,000,000 becomes $30.0m. But it … Read more
Having looked at how to use slicers with PIVOTBY and FILTER in a previous post. Let’s take this a step further and discover how we can use Table slicers for more advanced user interactivity. Table of Contents Example Getting the table slicer selections Changing and applying table slicer selections Disconnected slicers Slicer selections LAMBDA Conclusion … Read more
Microsoft recently announced the GROUPBY and PIVOTBY functions for Excel. Because these new functions provide PivotTable-type functionality, many have asked if we can use slicers with these functions. The answer is Yes! So, in this post, we look at how to use slicers with PIVOTBY in Excel. But, we also go one step further and … Read more
One of Excel’s most useful functions for accountants, financial modellers and data analysts is the EOMONTH function. This function calculates the last day of a month, based on a specified number of months in the past or future. From the name, EOMONTH, it may not be clear what the function does. Once we realize EOMONTH … Read more
Dumbbell dot plots are an excellent chart style for presenting comparative data. These chart styles quickly show the difference or progress between two data points. Often, Excel tutorials for dumbbell dot plots create charts that require manual updating for new data. But using the right Excel techniques these charts can be fully dynamic. So, in … Read more
Over the past months, the topic of using SUMIFS with arrays has come up a few times, so I thought I would write a post to help anyone with the same question. While I’m using SUMIFS throughout this post, it equally applies to SUMIF, COUNTIF, COUNTIFS, MAXIF, MAXIFS, MINIF, MINIFS, AVERAGEIF, and AVERAGEIFS. Table of … Read more