For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #26

You can learn a lot about your company by looking at trends in ranked performance. This Excel chart shows performance automatically sorted and ranked.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Charley's Swipe File 26Often, you can learn a lot about your company by looking at the trend in ranked performance over time. As you see measures from similar groups rise to the top or fall to the bottom, you can study these outliers and try to learn why they’re performing as they are.

For example, you might look at rankings in sales by product or sales region, error rates by production process, sick pay per department, and so on.

To illustrate the technique, this chart shows actual trends in how states are ranked by their unemployment rates.

As you can see, the top-ranked states have been fairly consistent since 2009. In fact, North Dakota has had the lowest unemployment rate for four years. And at the other extreme, Nevada crashed from the middle to dead last.

What path has the top-ranked states taken that the bottom-ranked states have not?

Excel Issues

I got this data from the Federal Reserve Board of St. Louis. You can get it by using this link… https://fred.stlouisfed.org/series/AKUR …where the text in upper case is the Series ID, which you can find in column A of the Data sheet in the CharleyFile_026.xlsx workbook. 

The data worksheet automatically sorts the top- and bottom-five results into place. To do so, I used the RANK function, along with INDEX-MATCH. This is a very powerful technique for using formulas to automatically sort data in a worksheet.

To make this technique work, however, you must overcome one limitation of the RANK function: When RANK finds equal values it assigns the same rank.

So if the top five items have the same value, Excel would rank them ALL as number 1. This makes it impossible to use INDEX-MATCH formulas to sort the data.

To get around the problem, we...

(Continued in the documentation.)

Order Details

Excel Versions:

Excel 2007 and after, for either Windows or Mac

Availability:

Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.

Guarantee:

One full year, unconditional.

Currency:

All prices are in US Dollar currency.

Licensing:

Two copies: One for work plus one for home.

Credit Cards:

Credit Cards
 

Only $12.95 USD

New Excel