top of page

Your Best Three


Hand holding up three fingers

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:


Screen shot of data table with customer ID, customer name, city, state, and sales dollars.

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:


Screen shot of the Conditional Formatting Top 10 option

When the dialog box pops up, I'll change the "10" to a "3", and choose a format:


Screen shot of the Top 10 Items dialog box

And there it is. The top 3 values are now highlighted:


Screen shot of the data with top three values highlighted in green

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:


Screen shot of data table with new row highlighted

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:


Screen shot showing Excel's RANK function

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:


Screen shot showing Rank values for all 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:


Screen shot of pivot table showing customer and sales

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:



Screen shot of Pivot Table's Top 10 menu option

On the dialog box that pops up, I change the "10" to a "3", and then click OK:


Screen shot of the Top 10 Filter dialog box

And here's the result:


Screen shot of pivot table showing only the top 3 customers

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

Photo of author Kimberlee Martin







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.




© North Port Solutions, LLC, 2023. All rights reserved.



22 views0 comments

Related Posts

See All

Komentar


Join our mailing list

Subscribers receive email notification of new posts.

Thanks for subscribing!

bottom of page