For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #72

Managers often need to see the top- and bottom-performing items, including sales and expenses. This chart finds and displays those winners and losers automatically. 


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

Managers often need to see the top- and bottom-performing items, including sales and expenses. This chart finds and displays those winners and losers automatically.A recent chart like this in The Economist showed the best- and worst-governed countries in Africa. So I decided to use the same format to show the best- and worst-selling products in a company.

In fact you could use this chart to display top and bottom performers for any list of items you want.

Excel Issues

This figure relies on at least four tricks.

First, the table that arranges the data for the chart uses worksheet formulas to sort the data automatically. Therefore, you can enter data for the current period in any sequence you want and let the bottom table in the Data sheet arrange it for you. The key column for sorting is the Rank column in the top table, which relies on the RANK function.

Unfortunately, Excel’s RANK function gives the same rank for identical values. This is a problem because we need each rank to be unique. Therefore,..

(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 $16.95

New Excel