Search Results

Excel’s Find and Replace can do much more than meets the eye. You can Find a specific formatting and replace it with another format. You can also highlight all the results found in the cells. This tutorial will uncover some surprising features of Excel’s Find and Replace feature.

A simple solution can be Excel’s Text to Columns feature. With 4 clicks you can format your values as numbers and convert numbers or text to dates.

This is the easiest searchable drop-down list you’ll be creating in Excel. It uses the new Excel FILTER function and dynamic array functionality. Download the workbook to practice along.

Quickly learn how to create a funnel chart in Excel that actually looks like a funnel. The trick is so simple but well hidden. It involves transforming SmartArt to a chart.

What to do when VLOOKUP falls short? It can’t return multiple match results. But there are formulas that can. Here, I show you 2 different methods that return multiple matches: a combination of INDEX & AGGREGATE, which are available in all Excel versions, and TEXTJOIN, which was first introduced in Excel 2016 and Office 365.

Learn how to use Excel’s IF formula in your Excel reports. We start with a simple example, followed by using IF with multiple criteria. You also learn how to use AND / OR conditions inside the IF function. Download the workbook to practice along.

In case you’re interested to visualize a KPI on a customized map in Excel which works on any version of Excel, then this tutorial is for you. I show you in easy steps how to find the right map and how to set it up properly.

In this tutorial, we will address how to lookup values in multiple tabs using a single formula. You will see one method using VLOOKUP and direct worksheet and cell references, and another using INDEX & MATCH and Excel Table names and references. Both approaches rely on the INDIRECT function to return the correct sheet address in a dynamic way.

In this tutorial we tackle a complex lookup problem – how to return the header based on criteria in a matrix. I offer my solution using INDEX and SUMPRODUCT. We also cover alternative approaches proposed by the Excel community, including an interesting TEXTJOIN solution that is able to handle multiple match results.

Conditional formatting is great at bringing attention to specific values, whether high/low, deviations, variances. It makes interpreting the data easier. But it can also significantly slow down your workbook. There is a faster alternative. You can use Custom Number Formatting to bring attention to data. In part 2, learn how to add any symbol to custom number format, how to use up/down arrows for percentage variances, and how custom formatting works with thresholds and icons.

Learn how to perform a matrix lookup, i.e. when you need to return the value based on the header row and the category column, in a scenario where you have more than one header. The powerful INDEX/MATCH combo is capable of handling that a couple of ways.

Learn how the INDEX and MATCH functions work individually and how putting them together allows you to conduct flexible lookups.