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.
94 Responses to “Creating KPI Dashboards in Microsoft Excel [Part 2 or 6] – Adding One Click Sort”
Nice implementation of dashboard sorting,
Robert,
Very, very nice article.
Chandoo, Robert,
Would you mind if I point my readers to your article
and present them your KPI Dashboards enhanced with MicroCharts
http://www.bonavistasystems.com/Images/ScrollSortBars.png
Thanks,
Andreas
http://blog.xlcubed.com/
@Andreas: sure, the ideas are meant to be shared and taken further. Even though you may want to enhance the dashboard with microcharts, that part has already been done by Robert and coming as 4th installment of this series. But go ahead and implement your own, at the end readers are the ones who benefit.
Welcome to PHD blog. I am regular reader of xlcubed and I enjoy your articles 🙂
@Jon:
Thanks for taking the time to read the post and thanks for the compliment.
@Andreas:
Obviously this dashboard is crying for some bar charts ;-).
As Chandoo said: the idea is meant to be downloaded, used and (if someone takes the time to) improved by the readers. Use the workbook in any way you want to use it. I am glad that you liked the post and the workbook.
Hi,
lets jump in the contest...
http://2.bp.blogspot.com/_giCGdxVQHS0/SLXCdVEta5I/AAAAAAAAAZo/d6Er5aPAmnI/s1600-h/CustomMap008.gif
Waiting for your version Robert !
Great way to cram infomation and functionality into a small grid of cells.
Adding a toggle for sort direction is pretty quick to implement with a spin button and a SMALL clause to the 'Sorted' formula...
add a comment below the radio button to indicate current sort direction.
Here the KPI Dashboard table enhanced with MicroCharts
http://blog.xlcubed.com/smart-dashboard-ranking-tables
Andreas
http://blog.xlcubed.com/
@Justin:
Great idea to let the user change the sort order with a spin button. I just sent an updated version to Chandoo. Maybe he will put a download link for the updated file as an amendment to the post.
Thanks for sharing the idea!
@Everyone, Post is updated with sort enhancement excel link, commenting so that subscribers are aware of this 🙂
I am able to recreate the file using your provided data. However, when I tried to add in more rows I get #VALUE! everywhere when I want to sort by KPI 2. I've modified formulas where necessary.
Please help.
Thanks
I think I figured it out...this sorting can only be done on numbers and not text. I plugged in my own data which contains both numbers and text.
Is there a way to sort on text as well?
Thanks
@Vivian:
it is always difficult to consult offline if I can't see what happened with your workbook, so - if you don't mind - I would deliver a step-by-step-decription what to do, if you want to have more rows:
1. Go to the sheet data: Go to row 95 (# 90 of the data) and insert - let's say -another 100 rows.
2. Copy the simple formula in C94 (or above) down to the end of the list.
3. Insert your data into the columns D to I of the inserted new rows.
4. Go to sheet calculations and to a row above product number 90 e.g. product number 30, that is row 40 on the sheet. By the way, now you already have the #VALUE problem, but don't worry we will fix that.
5. Insert 100 new rows.
6. Go to row 29 and copy down the complete row to the end of the list.
7. Go to the dashboard and change the maximum value of the scroll bar from 91 to 191.
That's it.
Please do not hesitate to come back if it still does not work....
@Vivian:
sorry I haven't seen your second post before answering.
Sorting texts is by far more complicated and as far as I know, there is no way to do this without array formulas. John Walkenbach offers an array-formula solution on Daily Dose of Excel:
http://www.dailydoseofexcel.com/archives/2006/10/11/dynamic-text-sorting/
Thanks Robert for your quick response. Here's another question for you.
I have two data sheets for two screens on the dashboard tab with scrolls. Unfortunately, the radio buttons can only be associated with only one linked cell in one data tab. Is there a way to separate the radio buttons so that radio buttons # 1 to 4 are for tab 1 and radio buttons (either 1 to 4 or 5 to 8) are for tab 2?
@Vivian:
No sweat: Just add a frame around the option buttons that shall be liked to reference one and a second frame around the others.
That's it.
Got it. I can't tell you how useful this is...Thanks again for being so helpful.
[...] 2 of this series, Creating Key Performance Indicator Dashboards in Excel with Scroll Controls, Adding sort options to excel dashboards before reading this [...]
[...] Adding sort options to excel dashboards [...]
Here's how to toggle the sort. Each column has two sort option buttons, one for (+) and one for (-). The user clicks either button, and the formulas do the magic. It would be only marginally more intricate than single direction sorting by option button.
Jon,
maybe I misunderstand you comment, but the update excel file at end of the post allows toggling the sort order. Not by another option button per column but by one single spin button. See also Justin's comment.
My bad. I hadn't opened that workbook, so I didn't see how the sort order was accomplished. I was thinking it needed some VBA to support the sort. I'll pay more attention.
Jon, please, no need to apologize. I guess you just haven’t seen the updated version…
You may have noticed that post #4 of the KPI dashboard series is predominantly based on your tutorials. Actually almost everything I know about visualization in Microsoft Excel I learned from your website / blog (and almost everything I know about Microsoft Excel formulae I learned from John Walkenbach’s books…). I am just one of the guys that are using all of your knowledge to create everyday business solutions…
By the way: did you already have a chance to look at the heat map files I sent to you? I already sent them to James (who was asking for this on your PTS-blog), but I would be very interested in your feedback as well. Whenever you got time to, please tell me what you think.
[...] Add sorting to the Dashboard [...]
[...] Post 2 - Add Sorting to the Excel Dashboard [...]
[...] Creating KPI Dashboards in Microsoft Excel [Part 2 of 4] - Adding One Click Sort. This is part two of a four-part guest series by Robert on Chandoo’s Pointy-Haired Dilbert blog. This part of the series shows an elegant way to allow sorting by a user selected column in a dashboard table, a very useful technique. [...]
This system does not allow extra lines to be added..
You will need something like this
=LARGE(INDIRECT(("$F$10:$F$" & LARGE($C$10:$C$5000,1)+ 9)),$C10)
And basically detect the largest record. Which is the last record in the C column and add 9 to it to determine the true last row.
You will need to do this for several other field.
And a macro to detect when the Scrollbar sheet is selected in which it would update the max value of record as appearing in the calculation worksheet.
"Needs work",
you do not have to change the formulas as long as you insert the additional rows somewhere in the middle of the tables on the sheets "Data" and "Calculation" and copy down the formulas from above.
With regards to the maximum value of the scrollbar: this has already been discussed in the comments on the first post of the series. You may want to read the comment of AdaptiveDervish and the following comments:
http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/#comment-19241/
Thanks seem to work that way.. oh well I done it my way.. and you can just post new record directly to the bottom and everything is updated. Excellent tutorial.. part 4 looks awesume going to look at it right now.
I dont think it will work that way very well.. some of the figures would have negative numbers wont the zero appear in between the rows when it is sorted by that KPI?
I used a macro and put it into sheet1
Private Sub Worksheet_Activate()
Dim X As String
X = ActiveSheet.Name
If X = "Dashboard" Then
ActiveSheet.Shapes("ScrollBar1").ControlFormat.Max = Sheets("Calculation").Range("E6")
End If
End Sub
End If
End Sub
In theory the manager won't be adding any new records, and anyone that does should have macros turned on ^^
Hi, I was trying this in my workbook and everything works fine, all but the sort. The options buttons are not working and I am not able to sort the columns. Can you pl provide step by step instructions for the sort ?
Rvlane,
you are writing that you are using the technique in your workbook, so I assume you have rebuilt the technique in your own model. Am I right? If so, check the target cell of the option buttons and make sure the sorting formulas are using this target cell to select the KPI.
Download the file provided in the post above as a reference and double check the formulas and links in your workbook.
Hi Robert, Thanks for replying. I've just started with Dashboards in excel, and hope to get more help. Yes, I am trying to rebuild this technique in my own model. I've tried working with the reference workbook and its not working. Can you please elaborate more on the target cell and sorting formula?. Specifically how the sorting formula uses the target cell to select the KPI. My guess is that there is just one link missing before I get it right and I'am having trouble figuring it out. Is there a way that I could forward you my excel sheet?.
Thanks a lot for your help.
I did it .. thanks Robert. "check the target cell of the option buttons and make sure the sorting formulas are using this target cell to select the KPI .. " got me thinking.
Really, thanks a lot. It works !!! 🙂
Rvlane,
cool. I am glad I could help. For future reference: If you want to send me an email, please go to my blog http://www.clearlyandsimply.com. You will find an email-link there.
Dear Sir
Tanks for your excellent file. I’ve just started with Dashboards in excel, and hope to get more help. Yes, I am to follow your istruction but if i have more than 5 kpi than the deshborards sheet will not work.
pls could you advice how i can change the link in option button so it will link to other sheet.
Thanks
Aqeel
Aqueel,
if you need more than 5 KPI (let's say 10 KPI in total) on your dashboard you have to do the following steps:
1. Insert additional 5 columns on all 3 sheets (dashboard, data, calculation)
2. Fill the additional columns with your data on the data sheet
3. Copy the formulas of column O on the sheet calculation to the 5 new columns
4. Copy the formulas of column J on the dashboard to the 5 new columns
5. On the dashboard sheet, select one option button, copy it, paste it and position it on the header cell of the first new dashboard column. Repeat this step for the other 4 new columns. You do not have to change anything regarding the option buttons, since they are all linked to the same cell. Copy and paste will do the job.
6. Change the conditional formatting formula (=mySortCriteria = 5) for all new dashboard columns (=mySortCriteria = 6, =mySortCriteria = 7, etc.).
That's it.
Dear Sir
Thank very mush for your excellent support
Aqeel,
you are very welcome. If you have any further issues with the workbook or questions on the used techniques, please do not hesitate to come back. I am happy to help.
Don't get me wrong, but would you please do me a favor? Stop the formalities. Don't call me 'Sir'. I am Robert, ok?
Great stuff - wonderful site!!!
Have you got an example using a horizonatl list & scroll bar?
Can't figure out the formula:-{
Phil,
use the target cell of the horizontal scroll bar as the 3rd parameter in your OFFSET formulas (column offset).
Here is an example for download:
http://www.box.net/shared/n0ojl367vu
You need OFFSET formulas in the column headers as well.
Please be advised that you may run into a problem, if you have different number formats per column. OFFSET simply fetches the data. It does not bring back the number formats.
Your sample KPI Dashboard is awesome. I was trying to figure a way to add a combobox that would allow filtering of KPI on the dashboard but couldn't think of anything. Would you think that this is possible or do you have any ideas?
Ray,
depends on what you mean by ‘filtering of KPI’. You can add Excel’s filter to an additional row above the data, however, if you filter the data, the scroll bar will not adjust its size and this looks a bit weird. Furthermore the filter does not adjust itself to any user changes on the dashboard like scrolling up or down, changing sort criteria or sort order. You can write some VBA code, of course, to overcome these drawbacks, but I think there is no way of doing this without VBA.
Having said that, if you want to select a certain KPI in a column (like having five KPIs in total, but only showing one or two of them on the dashboard), you can use combo boxes and change the third parameter of the OFFSET formulas on the worksheet Calculation to use the target cell of your combo boxes.
Hi Chandoo,
a little help with a twist: I have a list with 3 columns: ID, Name and UniqueCode. I wanto to show a dropdown to select a single item either by Name (sorted ascending), or by the UniqueCode (sorted ascending). I've just inserted 2 radio buttons to select the criteria, but I don't know how to continue.. PLease Help !!!
Thanks,
Martín
@Martin: You can change the range of a list box, data validation or named range reference by creating a dynamic range formula. We use OFFSET() formula to do this. You can learn more about this trick by reading these 2 articles:
http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/
http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
Let me know if you still have a problem implementing this.
Chandoo:
sorry, still don't get it...and probably i didn't make myself clear...
The list I have is as follows:
Col A (ID): from 1 increasing by 1
Col B (Name): text, not ordered (that's the problem !), and must respect the input order.
Col C (UniqueCode):this is a 11-digit unique code.
What I have done so far is, using this post, create an sorted UniqueCode list, which I'm feeding a dropdown with.
My problem now is how to create another sorted list, now using the Name field ( and no macros,if possible), since I cannot see how to order text if possible. I thought on a very, very very complex formula to translate each letteer of the text into a number, and then order by that, but I'm sure there is a better, more efficient, and much more simple way to do so.
Hope this helps.
Thanks, Martín.
How must you "respect" the input order? Could you add a column, 1 to N for the position the item held in the original list? Whenever you need to original order, sort by this column.
Jon,
the first Column (ID) is the one I use.
Nevertheless, I've found the solution using Chandoo's sorting with alphabetical formulas' post, using countif to determine the order of the text, and it worked wonderfully !!!
Thanks to all !!
Chandoo,
a twist: I'd like to have a single drop-down in my spreadsheet, along with 2 radio buttons, to select the content of the drop-down (Name or UniqueCode). I remember using CHOOSE to select from a list of ranges, and passing the linked cell of the radio buttons as the first parameter to the CHOOSE function. Something like:
=CHOOSE(radio_select, Range1, Range2) as a named range, and use it on the DropDown. but it doesn't seem to work. any ideas?
Thanks as always for force me to use my rusty grey matter !!!
Hi i am trying to build a dashboard for MIS report, can u plz suggest me how to design it
Snowwhite
A few pointers
1. Start with jotting down the ideas you are trying to show your audience
2. Draw some sketches to get ideas of what you want to layout,
Do it to scale, as if it is for an A4 printout, it will be a different aspect ratio to an onscreen dashboard
3. What interactivity do you want to provide the user
4. The above will give you a list of data you need to have available
5. Organise how to get the data , ie: from Others, Spreadsheets, files or SQL sources etc
6. Rearrange the data to fir with the above
7. Make the dashboard
Don't try and invent Rome in a day, work on one part of it until it works and you understand how it works, then move to the next bit.
Understanding what you are doing as you go along is important as invariably you will want to alter change or rearrange whatever you make later on.
Ask questions, That what we're here for.
Good luck
[...] 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 [...]
[...] 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 [...]
[...] Showing a sortable KPI grid to provide on-demand analysis (tutorial: making KPI dashboards in excel) [...]
I followed every step to the "T" but when I actually try the sorting by clicking on each button, nothing happends. I have all the algorithms set up exactly, etc. What could I be missing that is stopping the buttons from triggering the sorts?
Smitty,
did you set the target cells of the radio buttons? Do they all have the same target cell (E7 on [Calculation] in the workbook posted for download) and is this cell used in the OFFSET formulas to fetch the values of the selected sort criterion?
Chandoo,
I want to have mutiple list boxes in my excel. all the list boxes will lookup the data from another sheet. i just need one output based on the selection made in different list boxes. how do i do without using macros..? For ex
sheet 1:
List box1 = displays Customer name
List box2 = location
List box 3 = status
Sheet 2: Column A: Cust name Colum B location Column C; Status Column D: Remarks
based on single/mutiple selection made in 3 list boxes. i need one out put in Sheet 1. Please help
Shankar,
I am not sure that I understood your question correctly, but have a look at the comments number 40 to 44 on the first article of the series. It seems to me that your question is pretty similar to what John asked there and I posted an example file for free download in comment 43. The file is still available.
Hello everybody, Please I need some help with this.
I have some problems with the shading of the boxes. When I pass from one option (KPI) to another option I can not see the shadow, and when I paint with fill color it doesn't move from one option to another. What can I do?
Thanks for yours answers!
Dalmiro,
I suspect you are using Excel 2010, right?
I can reproduce this problem on my Excel 2010 installation. This is pretty strange and it seems to be a compatibility bug of Microsoft Excel. If you delete all conditional formatting on the dashboard and insert it again, it works with Excel 2010 as well.
I hope this will be helpful.
Robert
Thanks Robert,
It was helpful, right now I'm in step number 4: Add Microcharts to KPI Dashboards. I'm having some problems with the graphics (Charts), Fortunately I know you can help me if I need some help.
Thank you very much!! and Merry Christmas although a little late.
Greetings from Perú!
Dalmiro,
You are most welcome. I am glad to hear that my comment helped to solve the issue.
If you have any further issues with the Excel dashboard series, please leave a comment and ask your question(s). I'll be happy to help. However, I check the comments here only from time to time. Thus, it might take a few days until I am able to reply. I have to ask for your patience.
Belated Merry Christmas to you too, from Munich, Germany!
I am unable to do the formatting that shades the column when the option button is selected. I am working in Excel 2003. Can someone please help? Thank you!
Ryan,
the shading of the selected column is done by simple conditional formatting based on a formula. The fill color is set to grey, if "mySortCriterion" (the name of cell E7 on the worksheet calculation) is equal to the number of the column in our dashboard table. Download the workbook provided in the article, select any cell within the dashboard table and check the conditional formatting to see how this works.
I hope this will be helpful.
Hi Chandoo
let me first compliment you on the wonderful website you have created. i have one doubt though. In the above table-scroll-sort excel file you have used a functio called as mysortcriteria. Can you explain this funda more. i tried to understand this using your excel file and reading the comments above but not able to get it. Also there wasnt much on google when i tried to look it up there. Thanks in advance.
Parinita,
mySortCriteria is not a function. It is the name of cell E7 on worksheet calculation.
Hi! I am part of your excel school but I cant figure out how to create a sorting list or filter for the months. I am using (Excel based Sales Dashboard by Aires ) as an example and they have at the top a reference drop down. Can you please tell me where to look for that lesson? I need the drop down by month.
Chandoo,
How to Protect all cells except scroll bar..??
Anna,
if you want to protect the cells on the dashboard, you can simply protect all cells on the worksheet, since the target cells of the radio buttons and the scrollbar are on sheet "Calculations" and they do not change anything on the dashboard itself.
Or am I misunderstanding you?
I have a problem with this part of the excerise. 5th bullet. when i enter the formula of offset with the H coloum, for sorting whole data table for products, i get some zero values in the product range.and i have checked all the columbs that were sorted
sorry in addtion there is also some repetiton of prodcut names. ths is really weird as i cant fidn the source. all my sorted unique and postion colums have the same values as ur template values
Trishhh,
if you get empty cells or duplicate entries in the product names column, please check if you have unique product names in the first column of your data table. The OFFSET formula simply returns what is in the according row in column D on Calculations respectively on Data. If you have empty entries there, it will return zero values.
Can you post your workbook for download somewhere? If so, I will have a look and tell you what the issue is in your workbook.
Robert
I have a problem with the sorting, i have done everything else fine , just when i click the radio button to sort it doesnt work
Rishi
make sure the radio buttons are all linked to the same cell on worksheet [Calculation] (i.e. the target cell of the radio button). Furthermore make sure the OFFSET formulas fetching the selected KPI for sorting (formulas on sheet [Calculation] in column E) are using the target cell of the radio buttons to get the correct column from the data.
I verified that , and they are all point to the Sort by cell on the sheet
Did you check the formula, too? If it still does not work, you can send me your file by email and I will have a look. You will find an email link on my blog http://www.clearlyandsimply.com.
Hi Chandoo, great site and have been using it heaps so thank you very much!
An issue I've encountered is having two separate tables on the same worksheet and getting them to sort independently of each other. I've separated my data into separate tables on my sorting sheet but when I set up a new sort button for the second table that links to a new 'sort by' cell on the sorting sheet, all the other buttons on the first table no longer function. Can you have two separate tables on the same worksheet with the ability to sort independently? Appreciate any help you can give me.
Hi all,
I am new to this website and i found all of the contents fascinating. One question on this tool, when I click the KPI 1 button, how come the value in CalculationE7 automatically change to 1, and when i clicp KPI 2 button in the dashboard, the value is changed to 2 in CalculationE7? Your help is greatly appreciated.
Kevin
Hi Chandoo,
Great work Chandoo, I am big fan of chandoo and chandoo.org,
How can we sort with radio buttons if data is in text format?
Great site, and have been using ....
Thank you so much,
Roopalee
I have done every part as u mentioned, but the radio buttons are not working. i have given the correct OFFSET Formula and all the radio buttons are connected to one respective cell(mysortcriteria). anything i have missed out? Please do the need full.
[…] to Sorting KPIs using Formulas article for more on this […]
Hello,
How can I force the initial radio button to calculate as sort 2 rather than sort 1?
Thanks for you blog and support.
Was reviewing your KPI code, since there is a sequence number which seems to be unique (or you can assign a sequence #) the calculation on unique seems to be redundant. I really enjoy your tutorials, I'm no close to be an expert and I can be wrong on this.....
Thanks
Hi,
First I would thank Chandoo for the awesome excel resources which have helped me learn a lot.
@ALX: The calculation unique helps while using the MATCH function, in order to avoid getting duplicate vales. While using lookup functions like Vlookup, Match and index, only the value which occurs first in the array is returned for every duplicate value.
The alternative to this method would be to find a way to avoid getting duplicate values. I found such a method on Youtube channel of Excelisfun.
You can use the following formula in the Position coloumn as well to avoid duplicates:
INDEX($C$10:$C$109,AGGREGATE(15,6,ROW($E$10:$E$109)-ROW($E$10)+1/($E$10:$E$109=F12),COUNTIF($F$10:F12,F12)))
Hope it helps.
Thanks,
Mohit
Hi,
First of all just want to say I really like the tutorial, its helped my so much.
I am now using the techniques I have learnt in my own dashboards, but I have come across a few issues though, hopefully you'll be able to help.
- The data I am using is in the HH:MM:SS format and I am unable to sort it using the ways mentioned in your tutorial.
- Another issue I have come across is I get duplicate data in the sorted column in the Calculations tab, which causes duplicates to be shown on the dashboard. I have checked all the formulas and can’t see anything wrong.
Hope you can help,
Thanks
Dan
Hi..:)
It is really good and helpful. Can you please guide me how to sort the data. I did not understand the sorting thing. where is that "mySortCriteria". Please help
Hello,
I start by thanking you in advance for the wonderful tips and ideas this post has. It truly helped me out building my own with my own data. Everything works just fine, the thing is that I have 24 KPI's. Until I sort the number 9 (ninth KPI) everything works, but, when I put "10" in "sort by" all the calculations turn into "#Value". Can you please tell what possibly could be wrong? The sorting works perfectly until the KPI number 10.
I really appreciate your help.
Best regards,
Isabel
I've recreated the first and second portions of your examples here. The issue that I am running into right now is that the offset doesn't match everyone. I've played around with the unique identified column and I think the issue might be there, but I can't quite get it.
So most of the people actually show up in the tables that are to be sorted (on your calculated tab), however a good 35% don't. They just show 0 in all fields.
Any suggestions?
I found the cause of the issue I reported, for I had my absolute reference in the wrong place for those fields. Moving the $ to the number and away fro the letter of the cell fixed it. However, when I sort on my second KPI, the values are not in order, but random.
The change from the original unsorted table to the table sorted by selected KPI assumes you will always want to sort by one or other KPI. What change would be necessary to retain the original unsorted table as a 6th "None" option?
Hi Robert & Chandoo, thanks for sharing this great file with us.
I'm trying to do a second dashboard; side by side within the same dashboard tab.
The idea is that this file will be used by multiple users so I am trying to let each user have ownership and be able to look at their own dashboard. However, I have been unable to get past "mycriteria" even though I have switched most of the formulas directing to "mycriteria" to specific cell E7.
The biggest problem would be the radio buttons. When I clicked on dashboard A, button 1; somehow the data in dashboard B changes as well. Totally at a loss here.
I would be happy to provide the file so you can help me with this and hope to get your help on this.
Thanks in advance!!
@Charmaine
Radio Buttons which are ungrouped act as a collection of Radio Buttons
To have different Groups of Radio Buttons, select the few you want grouped
Then draw a Group Object around them
Select them all and the Group Object and Group them
Repeat for other Groups of Buttons
Hi, Awesome knowledge. Thanks.
I want to ask something about the second bullet point "Make sure to have a list with unique entries by adding a very small number (column F)"
In real the world there can be Same values for KPI or sales for two or more individuals, how to deal with in such situations?
I saw that, If the KPIs are same, then the position colomn in the calculation sheet has duplicate numbers and this affects the product name column which also gets repeated.
Good night all right? How do I return single values using function, saw this function in the worksheet, but don't understand how it works = E11 + $C 11/1000000000, if you can help me understand!
@Claudio
= E11 + $C 11/1000000000
means take the value in C11
divide it by 1 Billion and add E11 to it
eg: So C11 will be say 12,000,000,000
if E11 is 2.5
the formula will be =12,000,000,000/1,000,000,000 + 2.5
= 12/1 +2.5
= 12 + 2.5
=14.5