Creating KPI Dashboards in Microsoft Excel [Part 2 or 6] – Adding One Click Sort

Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert from Munich, Germany.

This 6 Part Tutorial on KPI Dashboards Teaches YOU:

Creating a Scrollable List View in Dashboard
Add Ability to Sort on Any KPI to the Dashboard
Highlight KPIs Based on Percentile
Add Microcharts to KPI Dashboards
Compare 2 KPIs in the Dashboards Using Form Controls
Show the Distribution of a KPI using Box Plots

The Challenge – Sorting

With the post KPI Dashboard – Setting up a Scrollable Table we started a little series of posts on how to create interactive dashboard tables with Microsoft Excel. Showing an extract of a longer list of items and enabling the user to scroll up and down was only the first step. Allowing deeper data analysis on the executive dashboard definitely needs more features. One of the most simple but common techniques for data analysis is sorting. Again we want to enable the user to select the sort criteria and see the results immediately without leaving the dashboard. That is: no need to go to the sheet with the raw data, no need to select ranges, no need to use the sort commands on the Excel menu or ribbon. And of course we want to do this without using VBA.

The Solution

The table on our KPI dashboard looks almost the same as the first one, except the 5 option buttons to select the sort criteria beneath the column headers and the fact that the selected column is highlighted with a darker fill color.

Download the excel file with KPI Dashboards – Scroll and Sort and read below to find how it is done.

The implementation

After some smaller changes on the dashboard, like adding the option buttons, linking them to the same cell and adding simple conditional formatting to the columns, the interesting part is the sorting algorithm on the sheet “calculations”. There are various solutions for sorting in excel using formulas. Most of them are use array formulas, definitely the most elegant way of doing this, but hard to understand. The step-by-step solution with several “help columns” may not be as elegant as an array formula, but it will probably be easier to understand.

This is how the dashboard sorting works:

• Get the relevant data (depending on the sort criteria) by using the function OFFSET (column E)
• Make sure to have a list with unique entries by adding a very small number (column F)
• Sort the list using the function LARGE (column G)
• Use MATCH to find the corresponding position of every value within the unsorted list (column H)
• Put together the whole data table in a sorted form by using the results in column H and OFFSET (columns (J to O)

We are almost there. All we have to do now is changing the starting references in the OFFSET-functions on the dashboard (refer to row 9 on sheet calculation instead of row 5 on sheet data). That is all.

Final remarks

If you are using Excel 2007, you will notice that the conditional formatting of the cells underneath the option buttons will behave somehow strangely when clicking on another button. If you scroll down until the range is out of sight and scroll back again, everything looks fine. This doesn’t happen with Excel 2003, so it seems to be a bug in Excel 2007.

What next?

Download the KPI Dashboards Excel and learn

Read the next post in this series: Part 3: Highlight KPIs Based on Percentile

Also, Checkout our Excel Dashboards Page for more examples and resources.

Update on Aug 28, 2008 Justin commented that it would be better if the sort order could be reversed so that you can analyze bottom 10 of any KPI using the dashboard. Robert is kind enough to oblige the request. He sent me another excel with sort enhancement. Download it if you want to see this.

Chandoo‘s note: Robert is a regular reader of this blog. Leave your comments / questions / love here and I am sure he will respond during free time.