10. Ranking Metrics

Subtitles Enabled

Next lesson: Formatting Your Dashboard

Preview next lesson


To rank our sales managers in terms of performance, we'll need use the RANK() function for each of our sales team metrics.


Ranking numbers in Excel

- Use the RANK() function to rank a number within an array of numbers
--- number: The value you wish to rank
--- ref: The total array of values
--- [order]: Optional argument that so you specify an ascending/descending order

Keyboard shortcuts

SHIFT + →: Select next cell
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
F2: Jump back inside a formula
CTRL + SHIFT + →: Select all cells within data region


The last remaining piece of analysis for our dashboard will rank the selected sales manager along our three metrics of revenue, new revenue rate, and lapsed revenue rate. In this lesson, we'll create formulas ranking the sales people by these three metrics. To complete this task, we'll use the "rank" function. The "rank" function accepts a value, and then ranks that value within an array of other values. So, for example, to rank the revenue of the sales manager Moll, we select the revenue for Moll, and then rank that revenue among all revenues for the 15 sales managers.

So, I'll go back to my dashboard, and write "= rank".

I'll then open a bracket, and the number will be Moll's revenue.

The reference will then be the array.

So going to my dashboard input sheet, and selecting the 15 sales manager array, closing the bracket, and press Enter.

And this tells me that Moll has a revenue rank of five. If I look across at the chart to the left, where sales managers are ordered by revenue, I can see that this number is correct. I can repeat this for new revenue rate.

So again, I'll use the rank function, open the bracket, the number will be the new revenue rate for Moll, the ref will be the new revenue rate array, for all 15 sales managers, I'll select with Control + Shift + Down Arrow, close the bracket, and press Enter.

And this tells me that Moll's new revenue rate is very high. In fact, it's the second highest among all sales managers. And finally, for the lapsed revenue rate, I'll use rank one more time. So again, the number will be Moll's specific rate, the ref will be the full array, and as before, we can close the bracket.

"Rank by default" orders the largest number in an array, number one, the second largest, number two, et cetera. So, the default order is descending. However, for lapsed revenue rate, the lower the value, the better. So, I need to reverse the order by which rank is calculated. To do this, I'll jump back into the formula with F2, and if I enter a comma after my last value, you'll see that we have an optional value of "order", which can be specified as descending or ascending. I'll type "one" for ascending, and close the bracket.

And this now changes Moll's rank from 10 to 6, which is correct. Whenever you're using the "rank" function, always be sure that you select the correct order of ascending or descending, depending on the metric that you're measuring. While it's nice to have the rank number under each title, I'd like to know how many sales managers in total are in the ranking. To do this, I'll add a string onto the value. So, I'll press F2 to jump back into the formula.

I'll use the "&" sign so I can add text to the string.

I'll open inverted commas, I'll write a space, and then say, "out of 15", which is the total number of sales managers. I'll close the inverted commas, and press Enter.

And this looks much better than having the bare ranking on its own. Off-camera, I'll complete this change for the other two rankings.

These rankings conserve to tell you much more about sales manager performance than the chart on the left hand side. Let's take, for example, Batiste, who has the second highest revenue of all sales managers.

Given this position, you might assume that Batiste had a very strong sales year in 2013. However, this is not the case. Last year, his ability to keep customers was in the lower half of the group, and his ability to attract new customers was even worse, coming in third last overall.

Batiste's high revenue in 2013 was in fact due to a stellar year in 2012, and since then, he has been relying heavily on his existing customers to renew their business this year. If we had not calculated these rankings, however, we would never have known this. As this lesson has hopefully shown you, rankings are very effective when you want to compare metrics within a group, be it customers, sales managers, or even geographic regions. They're also very easy and quick to create, and should always be considered when you're building a dashboard.

Excel Excel for Business Analytics Learning Plan
Build Your First Dashboard


My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial