## How to convert text to a formula in Excel (3 ways)

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

## How to create a relative named range in Excel

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

## How to calculate Weighted Average in Excel (4 easy examples)

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

## Don’t trust data validation in Excel!

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

## Change number format based on a cell value in Excel

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

## How to use Table slicers for advanced interactivity in Excel

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

## How to use slicers with PIVOTBY, GROUPBY & FILTER in Excel

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

## EOMONTH function in Excel (How to + 8 examples)

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

## How to make a Dumbbell Dot Plot in Excel (100% dynamic)

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

## How to use SUMIFS with arrays (Excel problem solved!)

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