I create a lot of dashboards for my clients to help them understand the metrics that make their businesses successful. In addition to the expected items (like sales, revenue and backlog, among others), I'm often asked to identify a top three.
This could be a top three customers, top three sales people, or top three products, for example. Management teams are always looking for ways to improve, and what better way to do so than to know exactly where your biggest successes are coming from.
So today, let's look at using Excel to identify your top threes. As with most things in Excel, there's more than one way to accomplish this. I'm going to cover three of them today:
Conditional Formatting
Ranking
A Pivot Table with a "Top 3" filter
The Data
Here's the sample data that I'll be working with:
As you can see, it's a sales data sheet, showing each customer's total year-to-date sales. There's more data further down the sheet, but this gives you a good idea of what we're working with.
Conditional Formatting
Conditional Formatting is one of the easiest ways to highlight the top values in a data set. In recent years, this feature has been enhanced and now offers a "Top 10 Items" option. Don't let the name fool you - you can customize this option for any number of items.
I'll start by selecting all of the values in my Sales column, and then use the Conditional Formatting menu to choose the Top 10 Items option:
When the dialog box pops up, I'll change the "10" to a "3", and choose a format:
And there it is. The top 3 values are now highlighted:
The great part of this method is that its dynamic. If one of the numbers changes, the highlighting will adjust accordingly. I'll change the Sales amount for Camper's Scene on row 7. The list will then look like this:
As soon as I changed the data, the highlighting automatically got adjusted to show Camper's Scene as one of the top 3.
Ranking
Another option is to assign a relative rank to each row, so that I can see where each customer falls. I'll use the "RANK" function in Excel to do this. Here's the formula:
As you can see, the "Rank" function has two arguments. The first argument is the cell that you want to evaluate - in this case, cell E2. The second argument is the range of cells that you will compare that value to - E2 to E50, in my sample file.
I can now see that Candice Designs ranks 24th in sales. I'll carry that formula down to all of the rows:
Now I can quickly see how each customer ranks.
NOTE: "RANK" is an older formula that's been replaced with two newer ones: RANK.EQ and RANK.AVG. The "RANK.EQ" function works the same as the RANK function I've shown here, while the "RANK.AVG" function has an interesting way of handling ties. If you're curious, google has lots of good references on this.
Pivot Table with Top 3 Filter
Finally, I can use a pivot table with a filter that shows only the top 3 values. To do so, I add a pivot table to my sheet, with the following properties:
Customer name is on the rows
Sales $ are the Values
My Pivot Table looks like this:
Now I can quickly filter for the Top 3 by clicking the drop-down arrow in cell G2 (the "Customer" heading). I'll choose the "Value Filters" and then "Top 10". As we say previously, "Top 10" doesn't actually have to be 10 items - you can customize that. Here are the options I'm choosing:
On the dialog box that pops up, I change the "10" to a "3", and then click OK:
And here's the result:
Other Options
As I stated at the beginning, there are probably several other ways to accomplish this same task. I've highlighted three of my favorites here - all are quick and easy to use, making them my go-to solutions.
In my last post, I said that I'd be covering dynamic drop-down boxes in this one. Oops. I forgot about that one! So... I'll make that my next post. Subscribe below to be notified when that goes live.
And, of course, reach out if you'd like to discuss your Excel project. I work as a freelancer on a project-by-project basis, or as a fractional IT resource with recurring hours. What works best for you?
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.