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.
13 Responses to “Using pivot tables to find out non performing customers”
To avoid the helper column and the macro, I would transpose the data into the format shown above (Name, Year, Sales). Now I can show more than one year, I can summarize - I can do many more things with it. ASAP Utilities (http://www.asap-utilities.com) has a new experimental feature that can easily transpose the table into the correct format. Much easier in my opinion.
David
Of course with alternative data structure, we can easily setup a slicer based solution so that everything works like clockwork with even less work.
David, I was just about to post the same!
In Contextures site, I remember there's a post on how to do that. Clearly, the way data is layed out on the very beginning is critical to get the best results, and even you may thinkg the original layout is the best way, it is clearly not. And that kind of mistakes are the ones I love ! because it teaches and trains you to avoid them, and how to think on the data structure the next time.
Eventually, you get to that place when you "see" the structure on the moment the client tells you the request, and then, you realized you had an ephiphany, that glorious moment when data is no longer a mistery to you!!!
Rgds,
Chandoo,
If the goal is to see the list of customers who have not business from yearX, I would change the helper column formula to :
=IF(selYear="all",sum(C4:M4),sum(offset(C4:M4,,selyear-2002,1,columns(C4:M4)-selyear+2002)))This formula will sum the sales from Selected Year to 2012.
JMarc
If you are already using a helper column and the combox box runs a macro after it changes, why not just adjust the macro and filter the source data?
Regards
I gotta say, it seems like you are giving 10 answers to 10 questions when your client REALLY wants to know is: "What is the last year "this" customer row had a non-zero Sales QTY?... You're missing the forest for the trees...
Change the helper column to:
=IFERROR(INDEX(tblSales[[#Headers],[Customer name]:[Sales 2012]],0,MATCH(9.99999999999999E+307,tblSales[[#This Row],[Customer name]:[Sales 2012]],1)),"NO SALES")
And yes, since I'm matching off of them for value, I would change the headers to straight "2002" instead of "Sales 2002" but you sort the table on the helper column and then and there you can answer all of your questions.
Hi thanks for this. Just can't figure out how you get the combo box to control the pivot table. Can you please advise?
Cheers
@Kevin.. You are welcome. To insert a combo box, go to Developer ribbon > Insert > form controls > combo box.
For more on various form controls and how to use them, please read this: http://chandoo.org/wp/2011/03/30/form-controls/
Thanks Chandoo. But I know how to insert a combobox, I was more referring to how does in control the year in the pivot table? Or is this obvious? I note that if I select the Selected Year from the PivotTable Field List it says "the field has no itens" whereas this would normally allow you to change the year??
Thanks again
worked it out thanks...
when =data!Q2 changes it changes the value in column N:N and then when you do a refreshall the pivottable vlaues get updated
Still not sure why PivotTable Field List says “the field has no itens"?? I created my own pivot table and could not repeat that.
Hi, I put the sales data in range(F5:P19) and added a column D with the title 'Last sales in year'. After that, in column D for each customer, the simple formula
=2000+MATCH(1000000,E5:P5)
will provide the last year in which that particular customer had any sales, which can than easily be managed by autofilter.
Somewhat longer but perhaps a bit more solid (with the column titles in row 4):
=RIGHT(INDEX($F$4:$P$19,1,MATCH(1000000,F5:P5)),4)
[…] Finding non-performing customers using Pivot Tables […]