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 – Adding Percentile Information
Let’s get back to our last week’s KPI dashboard example: Adding sort options to excel dashboards. In today’s post we want our dashboard to take a step forward by adding another data analysis feature. Up to now the user is able to view a window of 10 rows out of a much larger list and to sort by any given decision parameter. But the KPI dashboard falls short if we want to evaluate the performance of the displayed items regarding the other 4 KPIs.
Imagine we are at the top of the list and the table is sorted by KPI 1 (see left). We see that “Product Name 36” is the TOP performer regarding KPI 1. But how does it perform regarding KPI 3? The value of 2% is probably rather poor, but how poor? Sure, we can change the sort order to KPI 3 and scroll down until we find product 36 and look at the ranking in the first column. But changing the sort order back and forth is in-convenient, time-consuming and not user-friendly.
The solution
One statistical method to examine a list of data is the percentile. A percentile is the value of a variable below which a certain percent of observations fall (more). The 10% percentile of our list of values for KPI 3 returns the threshold at which 10% of all values are smaller than this threshold. We will use this method to classify the values of the KPIs that are not selected as the sort criteria by highlighting the values above the 90% percentile in green (10% best performers) and by highlighting the values below the 10% percentile in red (10% poorest performers).
After the highlighting we are now able to see immediately that Product 36 is best in class regarding KPI 1 but it belongs to the poorest 10% of all products regarding KPI 3.
Download the Excel file with KPI Dashboards and read on below how it is done.
The implementation
Implementation needs a simple conditional formatting and the excel spreadsheet function PERCENTILE. The syntax of this function is PERCENTILE (array, k), where ‘array’ is the range with the data and ‘k’ is the percentile value in a range between 0 and 1. PERCENTILE (A1:A100, 0.10) returns the threshold at which 10% of all values in the range are smaller than this value and the remaining 90% are larger than this value.
Here is the description how to change the workbook:
- Add two additional rows to the data worksheet to define the upper and lower percentile value.
- Insert five new columns on the dashboard each of them right to the existing column with the data.
- To simplify the formula, insert the number of each KPI in the cells below the header (F6 = 1; H6 = 2, and so on).
Fill the new columns with the following formula (example for cell G8):=IF (mySortCriteria=F$6,"",
IF (F8>PERCENTILE (Calculation!$K$10:$K$109,Data!$E$5),"<+",
IF (F8<-","")))If the actual column is the one the table is sorted by, a blank would be returned. Otherwise: if the value
in the cell left is larger than the e.g. 90% percentile, “<+”, if the value is smaller than the 10% percentile “<-” will be returned. For all other values the result of the formula is a blank.
Format the new columns with a red font color and add additional formatting that changes the font color to green if the cell value is “<+”.- Finally add a caption under the table to help the user understand what the triangles are representing.
Fill the new columns with the following formula (example for cell G8):
Final remarks
If you don’t like the triangles, you could easily replace them by a dot or a diamond or whatever you choose. Or you might want to change the colors or put the triangles to the left of the columns instead of the right. If you don’t like the extra columns next to the data, you could also use the described formula to conditionally format the cells with the data (e.g. with red and green fill color).
What’s next?
Make sure you have downloaded the KPI Dashboards XLS files – Click here
Up to now we have limited our dashboard to texts and numbers. Of course graphical visualization can always add much value for analysis. See next post: Part 4: Add Microcharts to KPI Dashboards
Also, Checkout our Excel Dashboards Page for more examples and resources.
Chandoo’s note: Robert is a regular reader of this blog, please leave your comments, questions, appreciations here and he will respond.
39 Responses to “KPI Dashboards – Highlight KPIs Based on Percentile [Part 3 or 6]”
@All:
The formula shown in the how-to section (4.) of the post is not complete. It should be:
=IF (mySortCriteria=F$6,"",
IF (F8>PERCENTILE (Calculation!$K$10:$K$109,Data!$E$5),"?+",
IF (F8<PERCENTILE (Calculation!$K$10:$K$109,Data!$E$6),"?-","")))
I recommend to download the file and have look at the formulas there.
Hello Sir,
can you tell me how to use symbols in if Condition Like
=IF(Calculation!$D$5>1,"?","")
I am waiting for your reply
Thanks
Atul
Atul,
sorry for the late reply, I just discovered your comment.
However, the answer to your question is already on this page in the comments below. See my answer to Linda on September 13, 2010.
Another great post, Robert!
Can barely wait for the final part!
Great info, I'm looking forward to the visualization part!
Regards,
Dan
[...] enables the user to scroll within a larger set of data, to sort by a selected KPI with one click, to identify the best and poorest performers with quartiles and to visualize the data, the averages and the targets with bar-line-combination [...]
This is some great stuff!! One issue...when I enter the percentile formula I receive a number error. I've checked and re-checked that my formula matches yours, but can't find the problem. Any ideas? Thanks for the time you have taken with these postings and your help!
Brian,
thanks for your feedback and question.
First of all: I guess you have seen the first comment? The formula in the text of the post itself is incomplete. I recommend to download the workbook and look at the formulas there.
Another reason why it is not working in your workbook might be the defined range name "SortCriteria" used in the condition of the IF-clause. Did you define this range name in your workbook as well? If you did not but still have the name in the formula you will receive an error (#NAME?).
If this does not solve your problem, please come back again.
Thanks for replying, Robert:
Yeah, it seems I have everything in order (I've followed everything step-by-step), but I am getting the #NAME? error. The first IF clause works fine: nothing is displayed when sorted by the referenced column. But when I sort by another column, the error is displayed.
Brian,
I guess you are using an English version of Excel?
If not, this could be the problem.
E.g. I am using a German version and the English function PERCENTILE is QUANTIL in the German version. So if you are using a version with another language than English, you have to replace PERCENTILE by the name of this function in the used language.
If this does not help either, maybe you want to send me your workbook to trm001 at online dot de and I will have a look at it.
[...] enables the user to scroll within a larger set of data, to sort by a selected KPI with one click, to identify the best and poorest performers with quartiles and to visualize the data, the averages and the targets with bar-line-combination charts. Also we [...]
Great! - simple, effective, and informative. You don't get better with dashboarding than that.
[...] Part 3: Highlight Values Based on Percentile [...]
[...] Part 3: Highlight Values Based on Percentile [...]
These are great tutorials! Congratulations, Robert and Chandoo.
I have a probably simple question: I can't figure out how do you insert the triangle symbols on the excel formula. Inserting symbol on menu doesn't seem to work. Can anyone help me with it?
Best regards, Aires
@Aires.. copy paste 😛
Copy the symbol and then paste it while writing the formula.
Heh, I was wondering where did you insert it without "cheating"! 😉
But thanks anyway! 🙂
Hello,
I am trying to figure out how to add more products to the 100 product list. I cannot figure out how to make the change.
Frank
@Frank.. for example purpose we limited this at 100, but you can easily change the dashboard to include more KPIs. Just read the first post of this series to understand the structure and mess the download file to get it working.
[...] Part 3: Highlight Values Based on Percentile [...]
Chandoo,
This is fantastic and is really developing my knowledge of excel and dashboards in general.
I have the same problem as Aires, I would like to be able to insert the triangles without having to copy and paste them (or at least know how to create them somewhere else so that I can copy them). I can use the char command to get the triangle but only if I format the text to Windings 3, but then my "+" or "-" signs don't work. Any suggestions would be great.
Thanks
Linda,
sorry for not responding earlier. I just saw your comment.
You do not have to use Windings3. The triangles are available in Arial as well. Go to an empty cell, insert a symbol, select Arial as the font and scroll down and search for the triangles. The code is 25BA and 25C4 (unicode(hex)). Insert both triangles into the cell. Then, copy the triangles and insert them into the formula.
That's it.
I hope this answers your question.
I want to know how to count Only Alpha in MS Excel????
Dhiraj,
sorry for the late reply. I just saw your comment. I am sorry, but I do not understand your question: what do you exactly mean by "count only alpha"?
@Dhiraj
To count the number of Numerical cells in a Range =Count(A1:A10)
To count the number of Cells in a Range with something in =Counta(A1:A10)
To count the number of non-Numeric Cells =Counta(A1:A10)-count(A1:A10)
To count the number of characters in a cell =Len(A1)
[...] Post 3 – Add Percentile Information to the Excel Dashboard [...]
This is soooooooooooooooooooo fabulous, inspiring, world class...... I just could not find the right english vocabulary to describe it.
Chandoo, many thanks for sharing it.
I am looking forward to seeing your new creative ideas.
Bing
example needed
There's an example available for download in the middle of the post
Download the Excel file with KPI Dashboards and read on below how it is done.
I have been using your examples for my KPI but have a question. I show data for the number of calls managed by clinicians in a monthly period (KPI 1) KPI 2 measures their ability to manage those calls over the phone. (A percentil measurement does this well because a high percentage is good)...KPI 2 however is % of cases referred to Emergency Services..here a low percentage is good not a high one. So my question is..How do you reverse the view in one column?
Stephen,
you have to define 5 different sort order cells and change the formula to toggle between ascending and descending sort. Have a look at this example:
dashboard-table-scroll-and-sort-individual
Hi Robert,
Thank You for the interesting read and delightful idea of dashboards.
I admit I'm a novice at Excel, yet I have been able to follow the first two parts of the series pretty well.
However, with this one, I am stuck at the point:
=IF (mySortCriteria=F$6,”",
IF (F8>PERCENTILE (Calculation!$K$10:$K$109,Data!$E$5),”?+”,
IF (F8<PERCENTILE (Calculation!$K$10:$K$109,Data!$E$6),”?-”,”")))
No matter how much I replicate the formula, I still can't get it right.
Please help.
Could you also explain the break-down of the formula as a huge favour.
Thanks a lot.
Asar,
thanks for your comment. I am not sure I understand you correctly: When you are saying you "can't get it right", do you mean you don't understand what it is doing and how it is working or are you having trouble to make it work in your own workbook? If the latter, can you please elaborate a bit on what is not working for you?
In any case, here is a short explanation of this formula:
The purpose: the single purpose of the formula is to display a triangle and a plus for all values belonging to the top 10% percent of all values of each KPI and a triangle with a minus to indicate the values which belong to the smallest 10% percent. You can choose other percentage values on the worksheet [data] in cells E5 and E6. The triangles and plus / minus signs shall only be displayed for the KPI which is currently not selected as the sort criterion (since in column the table is sorted by, the plus signs will all be at the top and the minus at the bottom of the list, so this would not add any value to the dashboard).
A percentile is the value of a variable below which a certain percent of observations fall. In other words, if one value is smaller than the 10% percentile, it belongs to the smallest 10% of all values. If a value is greater than the 90% percentile, it belongs to highest 10% of all values.
The formula is a simple nested if. Here it is in pseudo code:
IF (this KPI is the sort criterion) THEN result is empty
ELSE
IF KPI value is greater than the 90% percentile THEN
result is "triangle and a plus sign"
ELSE IF KPI value is less than the 10% percentile THEN
result is "triangle and a minus sign"
ELSE (i.e. cell is between 90% percentile and 10% percentile)
result is empty
END IF (inner IF)
END IF (outer IF)
So, the formula checks if you are in the column of the KPI the table is sorted by, first. If so, the formula returns an empty string. Then it checks if the value left to it is greater than the 90% percentile. If so, the formula returns the string with the triangle and the plus sign. If not AND the value left to the cell is smaller than the 10% percentile, it returns a triangle and the minus sign. In all other cases, it returns an empty string again.
Am I making sense or am I still as clear as mud?
@Robert: Thanks a ton for the awesome explanation. It worked as clean as a whistle. Guess my opaque memory does not absorb much at a time. 😉
However, there is one issue I still face, and hope you bail me out:
Could you kindly advise how do we control the spin button to sort the items on the Dashboard? I try the Format control option in the same manner as you had shown, but the dashboard does not get sorted, although data in the Calculation sheet does.
Thanks,
Asar
Asar,
you are welcome.
With regards to your question: the spin button on the dashboard to control the sort order is linked to cell E8 on the Calculation sheet and it is limited to the values 1 or 2.
1 stands for a sort largest to smallest
2 stands for a sort smallest to largest
However, this is only the first step. Next, you have to change the formula which is doing the main part of the sorting. This is in column G on the Calculation sheet.
In the simple first version (no selection of sort order, i.e. the table is always sorted descending), the formula is the following:
=LARGE($F$10:$F$109,$C10)
In the enhanced version with the user defined sort order, you have to switch between LARGE and SMALL depending on what is in cell E8 (the target cell of the spin button). The formula looks like this:
=CHOOSE($E$8,LARGE($F$11:$F$110,$C11),SMALL($F$11:$F$110,$C11))
I used the CHOOSE function here, you could also use a simple IF.
Basically the formula does nothing else than
IF E8 = 1 use LARGE to sort the table
IF E8 = 2 use SMALL to sort the table
That’s it. I hope this will answer your question.
Hi Robert,
Thanks once again. It works. Truly stupendous... 🙂
Many thanks Robert for such great ideas for the dashboards. Life could not be better.
awesome!
in this second series, when we add the sort functionality, I see that once we have created the sort icons, it becomes mandatory that the list is sorted by any of the 5 KPIs. How can we make it flexible in order to see the table with no sorting done?
There is a formula in cells E7:M7 in the dashboard sheet:
=IF(mySortCriteria =E6,CHOOSE(Calculation!$E$8,"È","Ç"),"")
I could not figure out the use of this formula. Please clarify.