Creating KPI Dashboards in Microsoft Excel [Part 1 of 6]

Posted on August 20th, 2008 in All Time Hits , Charts and Graphs , Featured , Learn Excel - 214 comments

Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert.

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

Dashboards have become quite popular in the last few years and in spite of all the Business Intelligence software products that provide dashboards, a lot of dashboards are still implemented with Microsoft Excel.

What is a Dashboard?

According to Stephen Few, one of the world-wide leading authorities on visualization and dashboard design,

a dashboard is a visual display of the most important information […] which fits entirely on a single computer screen […]
(Information Dashboard Design, 2006)

The Scrolling Problem

Fitting on a single computer screen is the challenge this post will solve. Imagine you have a large list of 100 or more items (e.g. products, sales regions, etc.) with several corresponding Key Performance Indicators (e.g. prices, costs of goods sold, sales, etc.) and you want to show this in a table on your management dashboard. The whole table will not fit on a single computer screen anymore. Most of the time it will be sufficient to show the first or largest 10 items only. But what if the user of your dashboard wants to scroll down the table and see the rest of the data? Sure, you might teach him to go to the sheet with the data and scroll up and down there. But this is not convenient, not user-friendly, insecure and not the purpose of a dashboard.

The solution

kpi-dashboard-excel-with-scrolling

The table on our dashboard doesn’t need much explanation. The only thing that differs from millions of other numeric tables in Excel is the slider scroll-bar between the names of the items and the data. This scroll-bar allows the user of the dashboard to walk through the whole list and see all items without leaving the dash-board. The table is small and leaves a lot of space for tables or charts on the dashboard.

Download the excel sheet containing KPI Dashboard solution to learn this better.

The implementation

  • First have our raw data ready in a separate sheet, this is the easy step, you know how to get your data in to one sheet. So skip to next one.
  • Next create a 10 row table for the dash board
  • forms-toolbar-spreadsheets-excel

  • Insert a scroll bar form control Go to Menu > view > tool bars and select “forms” to see the forms tool bar. Select the scrollbar control from forms tool bar and draw one on your spreadsheet. scroll-bar-form-control
  • Assign the scroll bar control to a cell right click on it and select format control option. In the dialog box, go to “control” tab and adjust the values as shown below:
    scroll-bar-contrl-excel-properties
  • Finally write OFFSET() formula to display any consecutive 10 values in our scrollable table: OFFSET is used on the dashboard to bring back those 10 lines from the sheet with the raw data that are selected by using the scroll bar. A sample formula is shown here: =OFFSET(Data!E5,Calculation!$D$5,0) where Data!E5 refers to the column containing the required data, Calculation!$d$5 has the current scroll bar value. That is all, you will have a small table that you can use to see all data using scroll

What next?

Make sure you have downloaded KPI Dashboard solution workbook to learn this better.

Read the next article in this series:Part 2: Add Ability to Sort on Any KPI to the Dashboard

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

Chandoo’s note: Robert is a regular reader and commenter on this blog. Drop your comments / questions here and I am sure he will answer them :)

Learn How to make Excel Dashboards - Join Excel School

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

214 Responses to “Creating KPI Dashboards in Microsoft Excel [Part 1 of 6]”

  1. DPizz says:

    Pretty cool!

  2. robert says:

    Thanks!
    Nice Post !!!
    Expecting your next article!

  3. Robert says:

    @DPizz and robert:
    Thanks for taking the time to read the post and for your comments. We will add more interesting dashboard techniques in the next posts. Stay tuned!

  4. AdaptiveDervish says:

    What a shame Maximum Value can’t be set as a formula so it would dynamically count the no of entries in your source data.

    I guess you could set it to 999 and then use conditional formatting to hide the ugly zero entries. Still means users will see scroll through blank rows at the end of the data but maybe I could live with that.

    Great tip though.

  5. Chandoo says:

    @Adaptive Dash… thanks and welcome to PHD. Indeed the maximum count can be set as a formula in the calculation tab. We can try to do so in the next part of the tutorial. Thanks for asking :)

  6. Marino says:

    Very nice. I Hope more tips. Thanks!

  7. Robert says:

    @AdaptiveDervish: Thanks for the comment!

    Yes you are right: you can’t link the maximum value of a forms scroll bar from to a cell reference. You would have to change it manually every time the number of data rows changes.

    One possible solution could be the idea you described. But I think this wouldn’t be convenient and user-friendly enough. The user would scroll down to empty parts of the list without knowing where the real list ends and he would not be able get to the end of the real list quickly.

    Probably the better way would be to use a small vba routine to set the maximum of the scroll bar. Go to the vba editor and copy the following code to the sheet “Dashboard”:

    Private Sub Worksheet_Activate()
    ActiveSheet.Shapes (“ScrollBar Liste”).ControlFormat.Max =Sheets(“Calculation”).Range(“$D$6″).Value
    End Sub

    Whenever the dashboard is activated, vba will change the maximum of the scroll bar according to the value calculated in cell calculation!D6 (=number of data rows minus number of items displayed on dashboard +1).

    The formula for calculating the maximum is already in the version posted for download. The result of the formula was used to control one of the two small triangles on top and at the bottom of the scroll bar. I haven’t mentioned the triangles in the post because they are not important and not really necessary for the technique. All they are doing is giving the user additional information whether he has reached the top or the bottom of the scroll bar.

    So: you already have the formula to calculate the maximum value; all you have to do is copying the procedure (see above) into the vba-editor.
    We haven’t included that routine intentionally. We tried to show some useful dashboard techniques without vba.

  8. Chandoo says:

    @AdaptiveDash: I incorrectly understood your comment, read Roberts follow up to understand the limitation in using scroll bar controls.

    as Robert pointed, the intention is to keep VBA out so that this technique can be used with ease. If you know some unconventional way of getting around this hitch let us know… as usual a donut awaits you :D

  9. Robert says:

    @AdaptiveDervish:

    Here is another work-around for your request without vba:

    1. Use another cell on the sheet calculation (e.g. D4)

    2. Select the scroll bar, click properties and set the maximum to e.g. 500 (or even higher) and the cell link to your cell D4 (see 1.)

    3. Change the formula in calculation!D6 to =Data!$C$105-ROWS(Dashboard!$E$6:$E$15)+1

    This is necessary to avoid circular references

    4. Insert a MIN-formula in calculation!D5 to calculate the minimum of D4 and D6 =MIN (D4,D6)

    That’s it.

    The effect: When scrolling down, the displayed data will stop at the last row of the raw data.

    Shortfalls:

    a. To be on the safe side you would have to use a rather high maximum value for the scroll bar.

    b. The slider of the scroll bar would indicate that you can still scroll down (actually you can, but the displayed data do not change anymore). This might be confusing for the user.

    What do you think?

  10. Justin says:

    Very stylish. Definitely one to add to the toolbox.
    ?Borrowing? from some of Jon Peltier chart examples you can quickly add a dymanic display of the measures and show the relationship between the 10 items you see and the rest of the data..
    Nice!
    (I added a bar chart for KPI 1 with linear trend line on the average of the ten products shown and linear trend line on the average of the 100 products, then hid the series for the averages, made for good “at a glance” understanding)

  11. Robert says:

    @Justin:

    Thanks for your comment.

    You are ahead of your times! Actually adding bar charts with additional lines to the dashboard will be the topic of the 4th post of this little series.

    Anyway: I recommend watching out for the next 3 posts. They might include one or two more ideas for you…

  12. aj says:

    have been followin the blog for sometime nw..and it has helped..thnks a zillion!!

  13. Chandoo says:

    @aj … thanks :) welcome to commenting. Without wonderful readers like you all, this blog is just another site.

  14. AdaptiveDervish says:

    Robert, I only just popped back after reading the 2nd post in the series http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/ and I’m really impressed with how responsive you have both been. i’ll play around with your solution but it looks great – thanks!

  15. Robert says:

    @AdaptiveDervish

    Thanks for your comment! Let me know, if you have any problems with the vba-solution or the non-vba-work-around. I think there is little potential for improvement of the vba code, but if you have another / better idea to solve the problem without vba, please let us know.

  16. [...] part 1 & 2 of this series, Creating Key Performance Indicator Dashboards in Excel with Scroll Controls, Adding sort options to excel dashboards before reading this [...]

  17. Akash says:

    Hey Chandoo,
    I am able to create a dashboard but the issue I am facing is that all the data is coming in the first row of my dashboard one by one when i scroll down.I wrote the offset formula in the first row only.Could this be a reason??
    Please suggest.
    Many Thanks.
    Akash

  18. Akash says:

    Hey Chando,
    Got it…..many thanks…

    Regards,
    Akash

  19. Robert says:

    @Akash,

    just copy down the offset formulas of your first row to all 10 rows of the dashboard table and it should work. If it still does not work in your file, you might want to download the excel file (see above) and have a look at the way it is done there.

  20. [...] 4 post series here at Pointy Haired Dilbert we developed a small dashboard that 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 [...]

  21. [...] Post 1 – Implementing a Scrolling Excel Dashboard Table [...]

  22. [...] excel dashboard tutorials: Creating KPI (Key Performance Indicator) Dashboards in Excel – 4 part tutorial Making Visualizations for Dashboards Too much data? Use tables [...]

  23. azmat says:

    guyz this is just awesome …….

  24. [...] Juice analytics lists down 8 features that dashboard makers should keep in mind while designing. Of course if you are stuck at building, do check our tutorials on excel based dashboards. [...]

  25. Sanjay says:

    I was not aware abt how to draw dashboard tables but the published notes/exmple helped me to undersstand /draw table now.
    Thanks

    • Steve says:

      Chandoo, 
      What version of Excel is this tutorial written in or for?  I’m on 2010, but I’m struggling with the first step … building the dashboard table … 
       
      Thanks!

      • Robert says:

        Steve,
        the workbooks of this series have been implemented using Excel 2003, but the same technique is possible with 2007/2010/2013, too.

  26. Chandoo says:

    @Azmat and Sanjay: Welcome. I am happy you liked it.

  27. [...] Excel KPI Dashboards – 4 Post Tutorial and Free Downloads [...]

  28. [...] DIY Excel Training: Learn Excel Formulas in Plain English | Executive Dashboards in Excel – 4 Part Tutorial | 15 Excel Fun [...]

  29. [...] you probably don’t know who he is. Robert is a very exprienced excel user and the author of Executive Dashboards posts on PHD – a 6 post series describing how to create excel based dashboard [...]

  30. lieben says:

    Interessante Informationen.

  31. P.J. says:

    Thank you both for taking the time to put this out for the masses. Can you provide some details on the “calculation” worksheet and why it is set-up the way is. I realize that the result of the maximum position calculation is linked to the scroll bar, but being a newbie I don’t seem to understand the reason for the why the max position is calculated the way it is.
    Thanks again,
    P.J.

  32. Robert says:

    P.J.,

    Thanks for your appreciation.

    You are right: the cell D6 on the calculation sheet is calculating the maximum value of the scrollbar on the dashboard.

    The calculation formula is =Data!$C$105-COUNTA(Dashboard!$D$6:$D$15)+1, i.e.

    Number of last entry in the original list (i.e. number or rows in original data table = 100) –

    Number of rows on dashboard table (=10)

    +1

    Result: 91.

    Having 100 items in the original table and 10 items in the dashboard table, you can scroll down until the scroll bar value is 91 and the dashboard excerpt displays the rows 91, 92, 93, … 100 of the original table.

    But the scrollbar maximum is not linked to the cell D6 on the calculation sheet, because Excel does not allow you to link the parameters of a scrollbar to a cell reference. See also the comment of AdaptiveDervish (comment no. 4) and Chandoo’s and my following comments.

    The result of cell D6 on the calculation sheet is used in the formula of cell E16 of the dashboard. Its only purpose is to display the little black triangle beneath the scrollbar to indicate whether or not you are still able to scroll down or not. If you have reached the end of the list (i.e. scrollbar value = maximum value), the triangle disappears.

  33. P.J. says:

    Robert,
    Thank you for the speedy reply and thorough answer. I can’t wait to apply your methods to my lastest project.

    Thanks again for the very useful material.
    P.J.

  34. Angel Ramos says:

    Hello Robert. I find very useful your series and I’m not an excel pro user. I’d like to use the excel sheets to measure my KPIs monthly. I put in product name the names of the each month and then sorted the data. However when the KPI of a particular month is zero the month disappear from the KPI table. Any comments?

  35. Robert says:

    Angel,

    I am not sure that I understood your problem correctly: You are using months instead of the product names. That shouldn’t make a difference. And you are sorting the table by a KPI. So it seems as if you are using the download file of part 2 of this series. Correct?

    If I got it right you do not see a month with a KPI value 0 after you sorted by this KPI. Is this the problem?

    If so, the month will be at the end of the list, since the sorting algorithm is sorting by descending order and if you are having positive values only, the month will be the last on the dashboard. Use the dashboard scroll bar to scroll down to the end of the list and check wether the month is down there.

    If I misunderstood your question, please write another comment and clarify. Thanks.

  36. Alex says:

    Hi,
    How did you change the font color to red for negative #s in Data form “dashboard-table-scroll”? I use 2007 and checked the conditional formatting, table formatting and there are none in your example.
    I considered myself a knowledgeable excel 2003 person until I found your site: your work is tremendously helpful. Now I am learning 2007.
    Thank you.
    Alex

  37. Robert says:

    Alex,

    you do not need conditional formatting to change the font color. It is simple cell number formatting. Go to format cell (ctrl 1) and check out the number format.

  38. [...] on dashboards: KPI Dashboards using Excel (6 part tutorial and downloads), Excel Dashboards theory, principles and [...]

  39. I was just now looking for info about this when I stumbled on your post. I’m just stopping by to say that I very much liked reading this post, it’s very clear and well written. Are you considering posting more about this? It seems like there is more fodder here for later posts.

  40. Robert says:

    This post is the first article of a 6 post series, but I assume you have noticed that. Actually, for the time being we are not planning to continue the series, but there are a lot of other dashboard examples and templates here on Pointy Haired Dilbert (oops, sorry, Excel@Work). Furthermore you may want to check my blog http://www.clearlyandsimply.com. There are some posts on Excel dashboards as well.

  41. Chandoo says:

    @”w I Got a Free iPhone With Free iPhone Apps” We have a slew of posts on Dashboard reporting using excel. You can find them all here: http://chandoo.org/wp/management-dashboards-excel/

    @Robert: no more excel@work.. we have the beloved dilbert back on track.. :) I realized it was stupid to take him off…

  42. Marion says:

    very nice – and, as a side benefit, I’m looking forward to going off on my own to learn more about offset.

    FYI – I added an AutoFilter to the title row of your dashboard. just to see what would happen. Unfortunately, the vertical scrollbar goes wonky (ie, it stretches out).

  43. Chandoo says:

    @Marion: have you read the OFFSET tutorial here: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

    I am not sure if the data filters on the dashboard would work that nicely.

  44. Pam says:

    Hi,
    I came across your very informative site while searching for info on dashboards. Thank you for sharing the tips and tricks that help to make programs so much easier to use. Some I knew, but the one that really stood out and I will use quite often is the double-clicking the bottom right of a cell and having it fill series to the end of the data. I’ve always done this the long way around with edit>go to first and last cell and then fill. This will definately be quicker.

    I do have a question, if you don’t mind having a look at it. I like the way you have added a scroll bar to a table to reduce the size of info displayed, but wonder if this can be done horizontally. I have a spreadsheet that displays approx 15 products (changes during year) vertically with sales and profit columns for six months into the future displayed horizontally. For now the last month ends on column R. I would like to make a three column table (Vendor, Sales, Profit) and have user scroll to each month’s sales and profit columns. I’ve tried replicating and changing your example but without success. Can you point me in the right direction? I’ve googled looking for answers, but nothing that seems to answer how I want my dashboard table to look.

    Thank you in advance for any advice you may have. Thanks again for all the great tips, templates, etc.
    Pam

  45. Chandoo says:

    Hi Pam… Thanks for your compliments. You can add horizontal scrollbar just as easily as you did the vertical scroll bar. While pasting the scrollbar, just adjust its dimensions (ie make its width more than height) and excel makes it horizontal scrollbar. Once you have it, you need to set its properties in such a way that whenever you scroll, the value is incremented by 3. Rest is writing formulas and offsetting the references accordingly.

    You can find a horizontal scrollbar example here: http://chandoo.org/wp/2009/03/12/comparison-charts-1/

  46. HAN says:

    Is this Excel 2007??
    I didn’t find tgis forms toolbar anywhere??!!!

  47. HAN says:

    Is this Excel 2007??

    I didn’t find forms toolbar anywhere??!!!

  48. Robert says:

    Han,

    to insert a form control in Excel 2007 go to the Developer Tab and click on insert and the control you want to insert.

    To show the forms toolbar in Excel 2003 click on the view menu and toolbars and select the forms toolbar.

  49. John says:

    I’ve gone through the entire series of posts and must say that each part was great – I’ve implemented all techniques to my dashboard with success. However, I have one little wrinkle with my data set: in addition to the KPIs in my dashboard, each row also has 3 other columns of identifying information (but these are all text so I do not sort by these). For ease of explanation, consider the current example posted but imagine the addition of columns “Group”, “Region”, “Manager” – and each of these may only have a few different possibilities. For example, for “Region”, the 100 different products could be in “USA”, “EMEA”, or “APAC” – is it possible to add functionality where the user can filter by this “Region” column and now the dashboard will only show rows that are in any of the given regions selected (but still only 10 rows at a time)?

  50. Robert says:

    John,

    thanks for the compliment. Yes, filtering is possible to some extent, but this is hard to describe within a comment. I uploaded a file for you that might help you implementing what you need:

    http://www.box.net/shared/bkvn5qjoye

    The drawback: After filtering by a region, the user is still able to scroll down to row 100. Nothing will be displayed in these rows, but this might be confusing…

    The problem: as mentioned above in other comments, you cannot change the maximum of the scroll bar without VBA. If you want to avoid VBA, this is the only way I know to get what you want.

    Let me know what you think.

  51. John says:

    Hi Robert,

    The implementation you showed is what I’m talking about – how would the approach change if there were other columns/fields that the user would like to filter by (e.g. in addition to filtering by region, the user might also like to filter by a column such as “Manager”)?

  52. Robert says:

    John,

    here is the modified approach using 3 filters (region, manager, sales channel), but still having the disadvantage metioned above:

    http://www.box.net/shared/ffhf06d32f

    Let me know, what you think.

  53. John says:

    Robert,

    I just tried your solution – exactly what I was thinking of…thanks for sharing your workbook – it enabled me to figure out the algorithm/calculation to display only the entries for the filtered categories. Many thanks! I may end up adding the little vba code as described in earlier comments to limit the max amount of rows displayed.

  54. manish says:

    hi Chandoo!

    Thanks for excel tricks! It realy helps me. Well I am working on a dashboard.I am not able to link my cells. When I do right click on the scroll bar I get the format option but not the control where i can go and link the cells. Please suggest how I can view the control option. I am using excel 2003.

    Thanking you in advance!
    Manish

  55. [...] vele dashboards op die vrij te gebruiken zijn en daarna zelf te bewerken zijn. Een voorbeeld is een goed dashboard om KPI’s te meten.   Ook staat er van iedere mogelijk denkbare chart wel een voorbeelden kun je iedere formule [...]

  56. Veronica says:

    I really love the website but I have not been able to get the scroll to work. I think these post are not step by step. Can you please make them a little more detailed? I am having a lot of difficulties getting any of them to work.

  57. Chandoo says:

    @Manish.. Sorry, I didnt notice your comment until now. Do you still need help?

    @Veronica: Welcome to Chandoo.org. Can you tell me where exactly you are facing the problem?

  58. Alexander says:

    I too am having trouble following this. I guess the people who are able to follow this are for more advanced on Excel than me. I think your site is great and you are very very good at what you do, but I do agree with the above poster, a more detailed step by step guide would be useful for somebody like me.

    Thanks

  59. Chandoo says:

    @Alexander: I am planning to do a small video course explaining this and other techniques to create excel based dashboards. Please give me a couple of weeks as I am still working on the mechanics of this. :)

  60. Andre says:

    Hi Robert,

    I`m playing with your KPI Dashboard revised II.
    Very nice dashbaord. I will use this chart in future.
    I need this tool with a new drop down for different customers.
    Let me know, if you can help me.
    Thanks

  61. Robert says:

    Andre,

    sorry for responding that late. I just noticed your comment.

    I suggest adding a drop down at top of the dashboard for selecting the customer. Then, add another worksheet and use OFFSET formulas to fetch the relevant data for the selected customer from the data worksheet. Change all formulas on the calculation worksheet to refer to the new worksheet with the selected data.

    Here is an example:

    http://www.box.net/shared/5d9t5hfb1x

    I hope this will be helpful.

  62. Andre says:

    Robert,
    thanks for your help. Your Example are very good.
    Now, I can better work with this dashboard.
    I have another 2 questions for optimaze the dashboard for my work.
    1.
    I have different numbers of products for the different customer. I think this is a problem for the combinated bar/line Chart. The minumum and the averages is not okay. Which is the right way for this problem?
    2.
    I have in my data the productname, manufactuers and brands (800 products from different brands and manufactures). Now I will analyzie the hole products and in the next step only for one manufacture or for one brand. How I can complete this KPI dashboard with the different selections?
    Let me know, if you can help me.
    Thanks

  63. Robert says:

    Andre,

    you are welcome.

    With regards to your questions:

    1. Different numbers of products for different customers

    You are right with regards to the calculation of the averages. You have to calculate the number of products for each customer (e.g. using COUNTA) and change all formulas calculating averages. An OFFSET function using the calculated number of products of the selected customer could dynamically change the cell range to be included in the AVERAGE function. Furthermore you have to make the data source of the XY scatter chart dynamic as well (again using OFFSET functions).

    2. Products, manufacturers and brands

    One option would be inserting a Pivot Table and retrieve the selected data from there. Another option would be additional drop downs to select manufacturers and brands and to use array formulas to apply the filter on the data used for your dashboard. Finally you could also use VBA code instead of the array formulas to filter the data. There is always more than one way to skin the cat, so probably there are other possibilities as well.

    I hope this will be helpful.

  64. Andre says:

    Thank you for your advice.
    I`m finished my first problem with the averages.
    Can you help me with the dynamic XY scatter chart and by the combination with the drop downs for customer, brands and manufactures? I dont understand this part.
    Thanking you in advance!
    André

  65. Robert says:

    Andre,

    you will find a detailed tutorial how to create a dynamic chart over at Jon Peltier’s website:

    http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

  66. Milind says:

    Excellent site and tips for creating dashboard!

    I am running into one challenge. How do I seamlessly intergrate a vertical scrollbar and a horizontal scroll bar. I started with the vertical and used the OFFSET formulas sucessfully. I also have the sort feature. Now I realize to take my dashboard to the next step, I need to also have horizontal scrolling. Since all my cells already have a OFFSET formuals and have the sort logic, is there an easy way to make this formula to also switch to horizontal scrolling and keep my sort logic intact ?

    Thanks again !

  67. Robert says:

    Milind,

    I think I already answered this question in a comment on the second post of this series. Have a look here:

    http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/#comment-62805

  68. Andre says:

    Hi Robert,
    thank your for your Link to the dynamic Chart of the Website
    http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
    After many hours I finished my work. I don´t found the right answer.
    Please help me.
    I need a second drop down menü “Manufactures” in the dashboard in Cell J3.
    http://www.box.net/shared/5d9t5hfb1x
    In my data page I have the differenct manufactures after the product name.
    Let me know, if you can help me.
    Thanks André

  69. Robert says:

    Andre,

    you are writing that you finished your work but didn’t find what you have been looking for. The link in your comment, however, takes me to my own example with the additional customers drop down.

    If you will upload a file with the work you have done so far and describe where you are hitting a road block, I will have a look at it and give my 2 cents.

  70. Milind says:

    Thanks Robert!

    Got another question and would appreciate your suggestion. I now have 3 scroll bars in my excel dashboard, complete with the sort logic and all. They all have an equivalent to “Product Name” that can be scrolled down to see different KPI’s. Can I put a search bar at the bottom of my dashboard with a button or so, that when I type in a specific product name and hit the button, all my 3 scroll bars should scroll down or up and find the “product name” while keeping the sort order that it was being sorted on.

    Thanks again!

  71. Robert says:

    Milind,

    you will need some VBA to do this.

    In a first step you could use a cell at the bottom of your dashboard as the entry cell for your search string. You could then write a MATCH formula in another cell to find the position of the entered search string in your data. But now you need VBA to overwrite the target cell of your scroll bars with the number the MATCH formula brings back. If you want to have a scroll bar and a search functionality at the same time, I do not see a way of doing this without VBA.

  72. Milind says:

    Thanks for suggestion. So once I use the MATCH formula, is there a simple VBA code that I could use to overwrite the cell link for the scroll bars, when the search is triggerred.

    Or is this too involved ? I am not good at writing VBA code at all…if its too complicated, I will probably drop the feature of having a search funtionality.

    Thanks for your help!

  73. Hui... says:

    @Milind
    As Robert said, If you setup a cell where you can type your search term and then use a Match to lookup the value from a validation list,

    You can then use something like this:

    Private Sub Worksheet_Change(ByVal target As Range)

    If target.Address = “$B$10″ Then
    [C1].Value = target.Value
    [C2].Value = target.Value
    [C3].Value = target.Value
    End If

    End Sub

    The above code must be pasted onto a code page for the sheet you are working on, not a general code module.

    Change
    B10 to the cell which has the Match formula refered to above
    C1..C3 are the 3 Scroll Bars Cell Links cells, Change to suit.

  74. Milind says:

    Hui…Thanks for suggestion. Please excuse my lack of VBA skills…Here’s what I have now. I wanted to use the search feature for one of the scroll bars first.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = “$F$37″ Then
    Sheets(“Calculation”).Range(“$E$5″).Value = Target.Value
    End If
    End Sub

    Nothing happens…So, I have a button (form control) next to my search box and I right-click to assign macro that will get triggered when the button is clicked. (I had used this same approach for controlling the max value of data in the scroll as I have a combo drop down list for the scroll )

    This is what I have as the macro code when I click on the search button
    Sub Button4759_Click()
    If Target.Address = “$F$37″ Then
    Sheets(“Calculation”).Range(“$E$5″).Value = Target.Value
    End If
    End Sub

    So, now I am getting a run-time error and the code stops with yellow markers on “If Target.Address = “$F$37″ Then”

    F37 is the result of my MATCH formula and Sheet(Calculation.E5) is my cell link for the scroll bar.

    Please let me know if you have any suggestions. Thanks !

  75. Hui... says:

    Your original code below
    `Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = “$F$37″ Then Sheets(“Calculation”).Range(“$E$5″).Value = Target.Value
    End Sub`

    Has to be on a Worksheet Code page not on a Code Module
    That is in VBA Double Click the worksheet you want this to apply to and paste it on the Right Hand pane
    Your scroll bar needs to have Calculation $E$5 as the cell link

  76. Milind says:

    That worked just great ! Thanks again!

  77. Robert says:

    Milind,

    I like your idea very much. Although Hui beat me to answering your questions (thanks Hui!), I decided to write an article elaborating on different options of how to implement this search functionality:

    http://www.clearlyandsimply.com/clearly_and_simply/2010/09/scroll-and-search-in-excel-dashboard-tables.html

    I thought you might be interested.

  78. Milind says:

    Robert – Thanks for taking the time to detail this out ! The entire concept of scroll, scort and search to display as a dashboard is Way cool

  79. Steve says:

    My question has to do with the chart used on the dashboard. When I try practicing building the dashboard I add a chart and go to select data the options are different. I.E. you chart for the average has series name, series X values, and series Y values but when I add a chart it only ives me series name and series X values options. How do I getthe option for series Y? I apologize if you already answered this I did not see a previous post. Thanks for your time.

  80. Robert says:

    Steve,

    the charts are using 2 different chart types: a horizontal bar chart for the values and an XY scatter chart for the average line and the scale dummies. If you add a new data series to the chart it will be a bar chart by default. You have to change the chart type to an XY scatter chart and then select the data sources for the X and Y series.

    I hope this will be helpful.

  81. jawad hassan says:

    your dash board formula is not working and giving error of reference is not valid

  82. Hui... says:

    @Jawan
    Can you be more specific about what your problem is, as hundreds of people have downloaded this without problem

  83. Ryan says:

    Can a similar dashboard be constructed, but with filtering capabilities? I want the charts to depend on the multiple filters selected by the user.

  84. Satbir Singh says:

    Hey!!…gr8 job man…

    this help me a lot…
    Looking forwrd for coaching. kindly guide me.

    Cont no 9999066800

  85. Robert says:

    Ryan,

    have a look here:

    http://www.box.net/shared/5d9t5hfb1x

    Something like this?

  86. Neil says:

    Great website, here comes the silly question so apologies now:
    How can you change the scroll window to show more than 10 rows?

  87. Robert says:

    Neil,

    no sweat:

    1. Insert empty rows somewhere within the table on the dashboard, e.g. the row above the last row. Let’s say you insert 10 additional rows.

    2. Copy the formulas from the row above the inserted rows down to the last row of the table.

    3. Increase the height of the scrollbar until the scrollbar covers all rows again.

    4. Right click on the scroll bar, go to the control tab and change the maximum of the scrollbar (e.g. to 81 if you have 20 rows instead of 10).

    I hope this will be helpful.

  88. Neil says:

    Hi Robert

    That’s great much appreciated, how do you re-align the chart to the correct rows is it a case of manually adjusting the size to get them to align properly?
    Thanks Again
    Neil

  89. Robert says:

    Neil,

    in the end you have to some manual alignments to make the charts look good within the table.

    There is one helpful trick, though: Keeping the ALT-key pressed during moving and resizing a chart will make the edges of the chart snap to the grid of the worksheet. This helps positioning and resizing the chart. In Excel 2007 and 2010 this works not only for the chart itself, but also for the plot area.

    And this works for all objects in an Excel worksheet, by the way, shapes, form controls, ActiveX controls, etc.

    I hope this will be helpful.

  90. Neil says:

    Thanks Robert worked a treat.
    Sorry for my noddy questions new to dashboard creation.
    Keep up the good work!!

  91. Robert says:

    Neil,

    no need to apologize. There is no such thing as a stupid question. However there are bad, unclear, insufficient explanations…

    You are most welcome. I am glad you liked it and I am happy to help.

  92. Joe says:

    Hello

    Thanks for the tip very handy. Is there a way to link it to a drop down filter in a dashboard? For instance say you have a drop down of years in the example, and you have 100 different years you want to link to. Is there a way to do that so if you change the year you change the data in the table. It would look something like this…

    Years [Drop Down Filter]
    KPI 1 KPI 2 KPI 3
    1 Product 1 XXX
    2 Product 2
    3 Product 3

  93. Joe says:

    Thanks for any help you can give me.

  94. Robert says:

    Joe,

    have a look at the file provided for download in my answer to Ryan’s question (comment no. 75).

    Does this answer your question?

  95. [...] with Robert Mundgil of clearlyandsimply.com. Robert is an Excel wizard. You may know him thru the KPI Dashboard articles he has written on chandoo.org a while [...]

  96. Milind says:

    Hi Robert – Is there a simple VBA code I could use to create a mouseover effect when I hover over a range of cells that form my scrolling list ? What I want to do is provide some neat information specific to each “Product Name” when I hover the mouse on that cell. Ofcourse the pop-up needs to disappear when I am not on that range of cells.

    Any help would be greatly appreciated ! Thanks as always

  97. Robert says:

    Milind,

    the easiest way of doing this is using cell comments. Instead of writing a detailed description here in a comment, I uploaded an example:

    http://www.box.net/shared/c5trieh1tz

    2 simple worksheets, 2 range names, 2 command buttons, 2 simple VBA subs. Have a look and let me know if you have any questions.

  98. [...] Below that I have used Scroll Bars (2 & 3) to allow a lot of data to be shown in a small space, scroll bars do this very nicely. The data in here would be a list of all the products within the current range of orderable parts ( the detail behind the table and bar chart above ). [Related tip: How to create a scrollable list in Excel Dashboards?] [...]

  99. Milind says:

    Thanks Robert. I think that will work just fine. I am going to give this a try. Another question…Is it possible in the same VBA subs to hide the small red triangle that you see on the upper left corner of the cell (that denotes that there is a comment ) ?

    Thanks as always !

  100. Robert says:

    Milind,

    you are welcome. I do not know a way of how to hide the red triangle indicator for commented cells. I am pretty sure that there is no way to do this with Excel options. I am not that sure with reagrds to a VBA solution, but I doubt there is a way to hide the indicators in Excel. Doesn’t mean there isn’t a workaround. I just don’t know one. Sorry.

  101. Robert says:

    Milind,

    on second thought: if you are really getting the needle with the indicators, another possible approach would be using the input message of the data validation functionality. However, I am not sure that this would be worth the effort. Just an idea.

  102. Milind says:

    Robert – After some googling, I got this…but can’t get the macro to trigger. Would something like this work and how should I invoke it ?

    Sub RemoveIndicatorShapes()

    Dim ws As Worksheet
    Dim shp As Shape

    Set ws = ActiveSheet

    For Each shp In ws.Shapes
    If Not shp.TopLeftCell.Comment Is Nothing Then
    If shp.AutoShapeType = _
    msoShapeRightTriangle Then
    shp.Delete
    End If
    End If
    Next shp

    End Sub

  103. Robert says:

    Milind,

    I guess you have taken this piece of code from Debra Dalgleish’s website?

    http://www.contextures.com/xlcomments03.html

    Debra solves the issue by inserting rectangles (shapes) to cover the comment indicators. There is a sub called CoverCommentIndicator on Debra’s site directly above the sub you copied into your comment. The routine RemoveIndicatorShapes only removes the rectancles that cover the comment indicators again. You have to run the CoverCommentIndicator sub first to “hide” the comment indicators.

  104. [...] original KPI dashboards using Excel article was so popular. They still help around 12,000 people around the globe every month. Many of [...]

  105. Milind says:

    Robert – When I try to use the code that you had for assigning the comments to myproducts, I get the error ” Object variable or With block variable not set”, when the code is trying to execute the statement

    rng_Target.Cells(lng_count, 1).Comment.Text rng_Tooltips.Cells(lng_count, 1).Value

    Any ideas ?

    Thanks again !

  106. Robert says:

    Milind,

    I suspect you didn’t define the range names (myProductNames and myToolTips) or you defined different names than I did and didn’t change the Set statements in the VBA accordingly.

  107. Milind says:

    Robert – Just to continue the dialogue…

    I got the names defined correctly as well as referenced them in the SET statement. I have been researching this error in several posts, and it looks like for whatever reason the excel VBA is considering that line of code as an object variable in play that is not being referenced….still combing through all these posts.

    Thanks

  108. philippe says:

    Hi,
    When I add a product 101, this one is not added dynamically.
    Is there a way to achieve that ?
    I’ve seen that in the calculations sheet we use the following formulas (=tblKPIs) but this one does not automatically expand.
    Moreover I’m not even able to extend the table myself ?
    Could someone clarify ?
    thanks

  109. Robert says:

    Milind,

    if the code is the same and the names are defined, it should work. I can’t tell you why it doesn’t. If you have an anonymized version, you can send me your workbook (you will find an “email me” link on my blog Clearly and Simply) and I will have a look.

  110. Robert says:

    Philippe,

    since you are talking about tblKPIs, I suspect you are referring to Chandoo’s revisited version not to the version posted here, right?

    Chandoo is using a table in Excel 2007 and you can expand the table on the worksheet data by simply clicking on the handle at the lower right cell of the table and dragging this down. If you do so, the name tblKPI will be updated automatically.

    However, you have to adapt all cell ranges on the worksheet [calculation] as well, because the calculated ranges (in rows 20 to 119 in Chandoo’s workbook) only cover 100 entries. Best way of doing this is inserting as many new rows as you need above the last row of the calulcation range and copy down the formulas from the row above.

    Finally you have to increase the maximum value of the scrollbar on the dashboard.

    I hope this answers your question.

  111. Philippe says:

    Yes. Robert you’re right I was looking at the revised file of Chandoo.
    But my question remains.
    How can have a dynamic scrollbar ? How can we give the revised example of Chandoo to any user without having to update the file for him when he will add a product ?
    I used often vba & pivot table to cope with this situation but I’m wondering if there are other solutions.

    For example: 1) If new products are added, I have to change the maximum value of the scrollbar. 2) Same in the calculation sheet in Chandoo ‘s file where there is the “tblKPI” array that I will have to extend
    T

  112. Robert says:

    Philippe,

    there are different ways of solving this. For instance, you could use named formulas to create dynamic ranges to be included in your calculations. One COUNT or COUNTA formula to detect the actual number of rows in the data worksheet and named formulas using OFFSETs based on this actual number of rows. Also, you have to perform all calculations for the sort by formulas (Chandoo’s rows 20 to 119) in dynamic named formulas. Some more work to do, but this gives you more flexibility if the amount of data changes.

    Another approach would be using a VBA sub to detect the number of rows and automatically perform all the steps I described in my comment above.

    Finally: the problem with the scrollbar and the maximum value: Have a look at the discussion in the comment section above: comments no 4) to 9).

  113. G says:

    Hello-
    I cannot get the data to scroll. I’m not sure what I’m doing wrong…I’ve downloaded the form and tried it on my own…no luck. the max value does change when it scrolls (on the calculation page), but, the data doesn’t scroll!?

    Any help is appreciated. Not sure if I should format any of the data?

    Thanks
    G

  114. Robert says:

    G,

    the target cell of the scrollbar has to be D5 on sheet calculation, not D6 (the maximum). The OFFSET formulas on the dashboard use calculation!C5 to scroll through the data. The maximum formula in calculation!D6 is only used to display the little triangle below the scrollbar.

  115. Ron says:

    As a noob to details about Dashboards (only familiar with very general concept) I have a simple question. What … is a KPI? You mention it 59 times in the 6 parts, but never define it.

    OK, I googled it: KPI = Key Performance Indicator (found it on another of your pages, <grin} )

  116. pibfer says:

    Hi Robert!!!
    This is an awesome tutorial, thanks a lot for sharing.
    If I have about 13 KPI’s to show in the same area, how can I put an horizontal scrollbar to get it?
    I want to use this scrolling idea but adding a new horizontal bar.
    I appreciate your big efforts to share your knowledge with us.

    Thanks a lot Robert!

    King regards,
    pibfer

  117. Johan says:

    Very nice article!

    I am wondering if it is possible to use OFFSET formula another time in another table but in the same worksheet. For example if I would display two kinds of tables with both their own scrollbar? Now I end up with one scrollbar scrolling two tables, while the other one is not working, very inconvenient.

    Any help appreciated!

    Kind regards,
    Johan

  118. Robert says:

    @pibfer,

    thanks!

    This has already been answered in a comment on the second post of the series. Have a look here:

    http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/#comment-62805

    The comment is providing a link to an example worksheet. I hope this will be helpful.

    I hope this will be helpful.

  119. Robert says:

    @Johan,
    thanks! Sure, this is possible. Simply link the second scroll bar to another cell (i.e. the target cell of the scroll bar) and use this cell in the OFFSET formulas for your second table. The technique is exactly the same, all you need is another target cell for the second scroll bar.

  120. [...] Chandoo and Robert over at the PHD blog have a nice a 4 post series of posts about Creating KPI Dashboards in Microsoft Excel. [...]

  121. Angela says:

    Lots of useful info on your site – I found this article especially helpful :-)

    I am using Excel to compensate for not having systemic reporting on the status of products for up to 50 affiliates. Rather than create 50 separate reports, I have created one master report that they can filter by country. I am trying to make it all less intimidating and more relevant by structuring the data in a dashboard, which ideally would include a scrollable table limited to only their records.

    I have successfully reproduced the table, but my question is, is there any way to make such a table editable :-). Since the actual table is filled with formulas and not values, editing the cells would just write over the formulas and ruin the table. I want to use the table to focus on their products with issues (a small percentage) and have them write respective comments concerning each, without forcing the user to go to the source table, which may have thousands of rows in it… (they have to ‘vet’ the products… they could do that either by a comments or Y/N field, or by highlighting with cell fill – but none of these means will work on the formula-filled table…)

    Ideas? :-D

  122. Robert says:

    Angela,

    if I understand your requirement correctly, you want to fetch data from a raw data table based on a filter (the affiliate), display it in a scrollable table on the dashboard and let the user add some comments to the data. This is possible, but I can’t see a way of doing this without VBA.

    You can easily add one or several columns to the table of the dashboard (without formulas) and the users can write their comments to these columns. The challenge, however, is writing these user inputs back to the raw data. A small VBA sub can do this, storing the inputs on the raw data table e.g. after every user input (or after scrolling up/down, leaving the dashboard, closing the workbook, etc.). As I said, I do not think this will be possible without VBA.

    The only way I can see to do this without VBA is working directly on the raw data sheet using an autofilter. Yet, you won’t have a scrollable table on the dashboard then.

  123. Angela says:

    Thanks for replying so quickly Robert! I really don’t mind using VBA and I’ve used it often in the past…You said, “You can easily add one or several columns to the table of the dashboard (without formulas) and the users can write their comments to these columns.” But if there are no formulas in the columns, then how is the scrollbar going to keep the comments in sync with the rest of the data in the dashboard table? After the user adds their comment, as soon as they scroll up or down, their comment will now appear adjacent to data from different rows that the refreshed OFFSET formula is reflecting from the raw table.

    It seems to me that using VBA, this is the minimum required to solve the problem:

    1) add the “comments” column to the source ‘raw data’ table 2) make sure this column is included and visible in the scrollable table using the OFFSET formulas 3) have a VBA macro run on worksheet_change event 4) if the change occurs in the target range, then the macro will write the value to the corresponding row in the raw data column 5) the macro then replaces the value in the target range with the original OFFSET formula that was overwritten.

    If there is a better way to do this please let me know. If not, I will give it a try next week and let you know how it works out… :-D

  124. Robert says:

    Angela,

    instead of a lengthy description in a comment here, I created a very simple example workbook for you. This is the download link:

    http://www.box.net/shared/puz160r5k1jg7teqg7d6

    The VBAis based on 2 very simple subs called GetComments and StoreComments (one line of code each), fetching the comments and storing them back to the raw data table.

    Please notice that

    1. I defined 3 named formulas (named ranges) to be used in the VBA.

    2. The subs are not only called in the Worksheet_SelectionChange and the Worksheet_Activate event subs of the dashboard worksheet. The GetComments sub is also used when using the scrollbar.

    3. The example is a simple as can be. The filtering by affiliate you mentioned in your first comment is not implemented.

    I hope this will be helpful.

  125. Angela says:

    I get it… the scrollbar is assigned a macro that places the appropriate values from the raw data table into the dashboard table (Sub GetComments). When the user enters data triggering a worksheet_change event, the StoreComments routine is called, which resizes the raw data comments range and writes the 10 dashboard comment rows to it.

    I modified your sheet to implement my idea, which is an alternative solution. Basically the comment col in the dashboard is filled with the OFFSET formulas on open of the doc. No macro is assigned to the scrollbar, and I use only the “StoreComments” sub and added one line to it:

    Range(“E5″, “D14″).FillRight
    [if you want to use named ranges, you can name E5:D14 and it works the same:
    Range("myFillRange").FillRight
    ]
    They both have pros and cons. The version with the formulas doesn’t have a ‘lag’ for the comments col to update when the scrollbar is advanced. If the user decides to hold the up or down button on the scrollbar, the formula version scrolls smoothly while the values version appears to ‘freeze’ the comments col while the other columns scroll along speedily. So formulas are much better for the ‘scrolling’ user experience. However, the values version is superior if the user, after entering in a long comment, decides they want to re-select the cell to edit just a part of it. If the cell is now filled with formulas, when they double-click it they will see the formula instead of getting a cursor and being able to select portions of the text.

    Since I may only be asking the user for boolean Y/N values, I might go with the formulas. But if ever I would require the user to enter lengthy comments, your version is definitely worth considering despite the lag time. Thanks again, very helpful… looks like I’m on my way to a very user-friendly dashboard :-)

  126. Robert says:

    Angela,

    well there is always more than one way to skin the cat, isn’t it?

    To be honest, I think the only downside of my solution is the fact that the comments column of the dashboard does not update when keeping the mouse key pressed on the scrollbar (because the GetComments sub is only executed after the scrollbar lost focus, i.e. the mouse key is released). But I think this does not really matter. I would expect, if a user searches something he will click several times on the scrollbar. If he keeps the mouse key pressed, I think it doesn’t matter that the comment values are not updating. The user is just trying to jump to another part of the table quickly and will not really look in detail to what is displayed during scrolling.

    Anyway: your workbook, your call, of course.

    If you are interested in some more ideas on how to improve the usability of your dashboard, you may want to have a look at the following article on my blog:

    http://www.clearlyandsimply.com/clearly_and_simply/2010/09/scroll-and-search-in-excel-dashboard-tables.html

    This might be an interesting additional feature for your workbook.

  127. Angela says:

    Thanks Robert for all your help! And thanks for posting the link to your article on the search functionality – very useful functionality to have.

  128. Robert says:

    Angela,

    You are most welcome. Let me know if you have any further questions, either on the KPI dashboard articles here or on the articles on Clearly and Simply.

  129. MANOHAR says:

    Hai Chandoo,

    U R Just Awesome

  130. thowfeek says:

    Hi all in the scroll KPI , how add more kPI column ,

  131. Hui... says:

    @Thowfeek

    Insert a column/s on the data page as required.
    Insert data

    Insert Columns on the dashboard page in the same positions
    Copy formulas across from a column to the left into the new column/s

  132. thowfeek says:

    Hi Hui

    thanks
    i had tried same but scroll not working properly after KPI 5
    pls guide me

  133. Hui... says:

    @Thowfeek
    I suspect that you added a Column to the end, not Inserted a column as I suggested.
    I have emailed you the file with a column added

  134. Brian Moore says:

    Amazing model… Great visualizations but one fatal flow: neither this nor the Excel 2007 revised version posted cannot handle more than 100 data points dynamically.

    I modified this to use a dynamic Excel 2007 table range and set the formulas so that it can automatically expand if more rows are added to the data table like you’d have in a situation where you were using data served by MS Query.

    The ONLY macro I had to add was to set the maximum scroll threshold in the scroll bar.

  135. Hui... says:

    @Brian
    Did you change the maximum value of the scroll bar ?
    Right Click on it and Format Control
    Change the maximum value to say 120 or what ever you want.
    unfortunately there is no way to link that to a cell or formula.

  136. Brian Moore says:

    I had to resort to VBA for the Maximum value but at least it is dynamic…. the macro counts the rows in the table data (refreshed from MS Query so it’s dynamic) and then sets the scroll max to the number of rows… too bad other than that it stayed true to using no macros

  137. Nabil Khan says:

    Hello,

    I love this dashboard, but I cant seem to figure out how to add more products, I am trying to get this to work with 1000 products, but I cant seem to figure it out, any help would be greatly appreciated.

    Thanks

  138. Hui... says:

    @Nabil
    .
    Insert the extra rows on the data page as required.
    Make sure you insert the extra rows before Row 105, Don’t add it to the end
    On the Dashboard Page right click on the Slider, Format Control
    Change the Maximum value to suit your new data (Number of rows)
    .
    You may also want to read Post 117 above, How to add extra fields

  139. Nabil Khan says:

    Thanks for the help, I did what you recommend but now I get a large white section in the chart on the dashboard for KPI 4

    Any recommendations?

  140. Hui... says:

    @Nabil
    Did you populate the new rows with extra data?
    .
    What Chart ?
    This post doesn’t have a chart ?

  141. Nabil Khan says:

    Yes I did it its working ok now, I was talking about the Max, Min, Average and Targets per KPI bar charts.

    Also I was wondering if there is any way to have the Product names of the top 10 how up on the scatter chart when the two KPIs are selected? As it shows 10 displayed in red but its hard to know what those are, am thinking if its possible to have the name show up if you scroll over it?

  142. Nabil Khan says:

    The white space on the bar charts is called “Series Invisible Point KPI 4 value 256″ its showing up on the KPI 4 bar chart.

  143. Hui... says:

    @Nabil
    If you email me the file I’ll have a look at your questions

  144. Nabil Khan says:

    Thanks again to everyone, especially Hui for all your help on this. I have another question and it probably wont be the last, I tried to change some of the KPIs to percentages in the data by formatting the cells and values. however this is not reflected in the dashboard as percentages. Any assistance would be greatly appreciated.

    Also would like some thoughts on changing the last KPI to show inventory levels, I was thinking would it be possible to show the levels in a format like a gas tank? So when the levels get low ( have this as an assumption to input by the user) its shows up on the dashboard as amber, really low as red and green for ok. How could this been done? would it be a hard challenge?

    Another idea I had was to have another section added to show bands. these would show what percentage of total sales would be within a certain price range. Then to set up the price ranges as an assumption. 0 -50, 50-100, 100 – 200, 200 to 500, 500 to 1000, 1000 and above. Not sure what would be the best graphical representation of this.

    Lastly had an idea to have a cloud showing product names in terms of how slow the inventory is moving so these would be in red and then to have a cloud to show what is moving the fastest in Green or Black. By cloud I mean it would have all the product names and the larger the volume of sales the larger the text. And the opposite for the non moving items. Not sure if this can be done via Excel, but it would be a cool addition for sure.

    any help and or comments are appreciated and if any excel geniuses out there can do the suggestions I put please contact me and i would love to work on this and would be eternally grateful.

  145. Hui... says:

    “I tried to change some of the KPIs to percentages in the data by formatting the cells and values.”
    You also have to change the same fields in the calculations and on the dashboard pages so that they all match
    .
    The Minimum, maximum, averages and targets per KPI are not setup to handle negatives properly
    In Calculation!BF23: =IF(BG23<0,0,AZ23)
    Copy down

  146. Nabil Khan says:

    Thanks again,

    Do you think its possible to do the cloud idea and the gas tank indicator as per my previous post?

  147. Nabil Khan says:

    Also shouldn’t the formula for In Calculation!BF23: =IF(BG23<0,0,AZ23) be In Calculation!BF23: =IF(BG23<0,0,BA23) ? Right now it changes the values to "KPI1", "KPI2"

  148. Hui... says:

    Possibly ?
    I had already started thinking with another idea and had added columns and maybe didn’t allow it correctly when I answered
    .
    As for the Gas Tank have a look at: http://chandoo.org/wp/2011/04/13/how-to-make-a-5-star-chart/
    .
    As for the cloud, Yes It can be done provided you limit it to a known number of items.
    I’m not sure about the animation bit, It can be done but sounds tacky!

  149. Ramon says:

    Greetings,

    Has there been any new methods of getting around the max value issue mentioned in comment 4. I have tried Robert’s suggested VBA but am getting a compile error when I run it. I’m using excel 2010.

    • Robert says:

      Ramon,

      the code I provided in my comment above works like a charm for me in all versions of Excel. What kind of error do you get? Can you post your example workbook for download somewhere?

  150. Ksandra2901 says:

    Hiya,

    This stuff will come in really useful thanks ever so much!

    In the mean time, I agree with some of the comments above. A more step-by-step guide would have been helpful ie do you create the Calculation tab first, last or does it not matter?

    I am finding that when I recreate this I am able to create the scrolling table (which I guess is the main objective!) but on the calculation tab the formula goes missing and resets to a scrolling number (if that makes sense!) I have copied your sheet like for like but it still does it and I can’t work it out! Any ideas?

    The fact that I can successfully create the scrolling table means I should be happy, but it is bugging me! lol

    • Robert says:

      Ksandra2901,

      I agree, a more detailed step-by-step may have been helpful, but you still have the workbook for download and it is not a very complex one. On to your specific question: the usual way of creating such a workbook is

      1. Input data
      2. Calculations
      3. Visualization / Dashboard

      What do you exactly mean by “the formula goes missing and resets to a scrolling number”? Are you referring to the target cell of the scrollbar? Actually there is no formula, because this cell is used by the scrollbar and all formulas are based on the actual value of this cell.

      Can you please clarify what exactly your problem is?

  151. ksandra2901 says:

    Hi thanks for replying. I’ve sorted it now thanks!

  152. Anup says:

    Hi Chandoo & Robert,

    I am basically new to excel and just stumbled around on this website by chance. I am currently in my 1st year of MBA and the excel stuff out on this website really amazes me! I never knew a scroll bar can do such stuff. :)

    Awesome stuff. You guys rock \m/

  153. Vineet says:

    This website is really amazing. :)

  154. nitin says:

    when i select scrollbar from developer insert menu. In format control option there is no “control” option because of that scroll bar is not working. i am using excel 2010.
    pls guide how i activate scroll bar

    • Hui... says:

      It sounds like you have selected an Active X Scroll bar not a Form Controls Scroll Bar
      Try inserting the other scroll bar type (at the top of the Insert menu)

  155. tua022012 says:

    Thanks for your link. It’s useful for our community.
    Same material can be found at : http://keyperformanceindicators.info/airline-kpis/
    I hope it’s useful for you and you like it. Please continue sharing more information at this topic.
    Best rgs!

  156. [...] KPI (Key Performance Indicator) Dashboards in Excel – Tutorial [Part … Link to this post!No related posts. [...]

  157. Rudd says:

    I love you chandoo . Because you are professionally exciting and very intelligent .
    Thank you for your labouruous site

  158. Kanchan says:

    how to make a dash board in excel ?
    Is that dashboard is not the part off excel ? 

  159. [...] Charting & Dashboards: Dynamic range charts | Top x chart | Analyzing large datasets | KPI dashboards [...]

  160. GiGi says:

    Great post! I was wondering if a form button (that uses a macro to go to another page/chart) can be used in the scrolling list? I tried but cant figure out the formula. Any help would be greatly appreciated.

    • Chandoo says:

      @Gigi… You need to apply this new style to your table.

      • Robert says:

        Gigi, Chandoo,
        I have to admit I do neither understand Gigi’s question nor Chandoo’s answer.
        @Gigi: Do you mean you want to have a button in each row of the table which would start a specific VBA routine for exactly the data record of this row? If so, you would need additional VBA code to change the .OnAction property of each command button in each row after changing the scroll bar.
        Can you explain in more detail what you are looking for?

      • Chandoo says:

        I am sorry for the messup. I was replying some other commenter and it ended up here. I guess I was too sleepy.

        As Robert said, you need either VBA or creative use of HYPERLINK() so that you can dynamically redirect user to relevant drill-down / chart which row they clicked. For more on HYPERLINK based solution, see this example and apply similar technique

        http://chandoo.org/wp/2011/07/14/dynamic-hyperlinks-in-excel/

         

  161. Karthik says:

    Hi Chandoo. Can you please tell me how did you type “?” in the cell(E5, E16) formula. I hope it is not symbol!

  162. Geekyard says:

    This will be highly while getting increments and performance appraisal ;) DATA talks more

  163. [...] avec des exemples c'est plus facile … KPI (Key Performance Indicator) Dashboards in Excel – Tutorial [Part 1 of 6] | Chandoo.org – Learn M… d'autres exemples : Excel Dashboard Examples – 66 Dashboards to Visualize Excel salaries around [...]

  164. [...] dashboards – Information, examples & tutorials Creating a KPI dashboard using Excel – 6 part tutorial Sales dashboards – 32 examples & [...]

  165. antoine says:

    Hi Chandoo!
    We also use to make this kind of dashboards few months ago, with our Excel power user. Then we had too much data to keep on using Excel. We decided to create our own dashboarding solution, jolicharts:

    https://jolicharts.com/

    It is now open to every one, and you can use it with a free account. Upgrading from Excel dashboards to Jolicharts was a little revolution for us: It increased (a lot) our productivity as well as the dashboard quality of our Saas products.

    You can easily connect your data or databases (no size limit), create KPIs from charts and dashboard, share these dashboards, even embed it in your software if needed! Do not hesitate to give us feedback!
    Cheers

  166. Michelle says:

    Hello,

    I’m trying to implement the scrollable table however arose a problem where one of my columns has some blank date fields. Though instead of just pulling the blank data “” with the offset function, it is populating the corresponding field in my scroll table with a phantom pseudo date of 1/0/1900. ;-p There’s not even so much a zero in the field I’m referencing and the fields in both tables (i.e. scroll set up table on my summary tab and the main data table) are formatted as dates. I did try the isblank to no avail unfortunately.

    Can anyone please tell me why I’m getting this error? My formula is below:

    =if(isblank(OFFSET(‘Inventory Status’!k1,Sheet1!!$Z$1,0)),””,offset(‘Inventory Status’!k1,Sheet1!!$Z$1,0))

    Also, while I’m here, some of the rows in main table got hidden but the scrollable table forces those rows in anyway, is there a way to avoid that?
    I also was wondering if there was a way to filter the table by the click of a button (say radio button option) where you can click on or the other to filter down the scroll table to only show properties that are Under Contract or when clicking the other radio button to get only properties that are actively listed and not under contract

    This site IS Awesome btW!!

  167. Jason says:

    Is it possible to pull data from two different sheets using the offset formula? I have created the scrolling table using Sheet 1 of data, but now want to include columns in my scrolling table with data from Sheet 2 of data. Sheet 2 and Sheet 1 both contain similar data fields, but in different positions, so you could reference say the matter number. Any help is appreciated!

    Great site by the way!

  168. Ursula Lampsas says:

    Great Information Can’t Watch for Another Post. Great Stuff

  169. vlatro says:

    Hi.

    GREAT SOLUTION!!! I’m an addicted Excel user, but not a pro. Implementation was easy!

    I’m having a problem tough.

    When I click the scrolbar arrow, it keeps scrolling until i remove my mouse pointer off the button. I’ve found this post: http://chandoo.org/forums/topic/strange-behaviour-with-scrollbar-control#post-107568

    Manual Calculation is not an option, because of the offset formulas in the “scrolling area’.

    I’ve tried using an ActiveX Scrollbar instead, but this doens’t works completely as expected and is VERY slow.

    Is there any solution?

  170. Ayanna says:

    Hello,
    Is there a way to add the name to pop up when you click on one of the metrics on the comparison scatter chart?
    Great dashboard, btw.

    Thanks!

  171. Luke says:

    Robert,

    I need help with a dashboard simulating a data base of an airport, using the information of passenger and flies. We have a database on Access and need to export it to Excel (i dont know how) and we’re desperate!!!!!!! xoxo Plz help us u.u

  172. Matt says:

    Could you possibly explain the calculation part of the slide bar? It’s obvioulsy important for the “offset” formula, so I would just wanting to know what considerations needed to be taken into account in the “=Data!$C$105-COUNTA(Dashboard!$D$6:$D$15)+1″ formula on the Calculation slide. Thanks!

  173. Swati Singh says:

    This helped me to create my first KPI Dashboard….Just a small query how can we avoid columns as showing zero value as it takes a set of 10 at a time.

    I donot know VBA or macros.

  174. Rakesh says:

    I am struggling on your instructions!1!!!!!!!!!!!!!!!!!!!!!!! It’s Not bloody working…….Please advise.

    Assign the scroll bar control to a cell right click on it and select format control option. In the dialog box, go to “control” tab and adjust the values as shown below:
    scroll-bar-contrl-excel-properties

    WHERE DO YOU WRITE THE OFF SET FORMULA??????????

    “Finally write OFFSET() formula to display any consecutive 10 values in our scrollable table: OFFSET is used on the dashboard to bring back those 10 lines from the sheet with the raw data that are selected by using the scroll bar. A sample formula is shown here: =OFFSET(Data!E5,Calculation!$D$5,0) where Data!E5 refers to the column containing the required data, Calculation!$d$5 has the current scroll bar value. That is all, you will have a small table that you can use to see all data using scroll”

    • Chandoo says:

      I think you must be really lazy. Why not download the file and see where everything is. And if you still need help, learn to ask gently. I am sure someone will be able to share their wisdom with you.

  175. Arup Chakraborty says:

    Dear Chandoo,

    Thanks for such a wonderful insight on Dashboard. It was really great but i would like to know why you havn’t put Offset formula same in all the cells of the dashboard table. That is in the first column you have put =OFFSET(Data!D5,Calculation!$D$5,0,1,1) but in the third column =OFFSET(Data!E5,Calculation!$D$5,0). Can you please tell me if i put =OFFSET(Data!E5,Calculation!$D$5,0,1,1) instead will this effect negetively?

  176. Felipe says:

    hi great post. i have been able to recreate the scroll with my own data. One question why do you calculate the max position with this formula =Data!$C$105-COUNTA(Dashboard!$D$6:$D$15)+1.
    I understand the formula but not sure why you do it this way. Actually i cannot replicate this part and when i debug my formulsa (F9 – another good Chandoo trick) instead of giving me a count as in yours it lists all the numbers like this:{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}-COUNTA(Dashboard!$C$7:$C$13)+1.
    Not sure why it does this . I realize this is an older post but if you have suggestions i appreciate it.

  177. […] is a screen shot of KPI dashboard, in this post you will understand the trick of creating this one in easy […]

  178. Rajiv says:

    Hi Chandoo,

    I stumbled upon your blob while doing google search on dashboards. Excellent piece of information I must say. However, I think there is a more efficient way of creating the scrolling dashboard, i.e. by using the array formula. Using your example sheet, I entered the array formula “{=OFFSET(Data!$C$5,Calculation!D5,0,10,7)} and a single formula works just fine instead of entering the formula in all the display cells. I guess that’s how offset function was designed to be used.

    Regards,

    Rajiv

  179. Rajiv says:

    Hi Chandoo,

    I stumbled upon your blog while doing a Google search on dashboards. Excellent piece of information I must say. However, I think there is a more efficient way of creating the scrolling dashboard, i.e. by using the array formula. Using your example sheet, I entered the array formula “{=OFFSET(Data!$C$5,Calculation!D5,0,10,7)}” and a single formula worked just fine instead of entering the formula in all the display cells. I guess that’s how offset function was designed to be used.

    Regards,

    Rajiv

  180. G Singh says:

    I can’t find the Menu > view > tool bars and select “forms” to see the forms tool bar in Excel 2010. Please help

    • Rajiv says:

      This does not work in Excel 2010. In Excel 2010, you need to first enable the Developer tool bar. This is done by opening the Excel options (to go to the Options dialogue, click the Office icon on the top left corner of the Excel window and then click the Excel Options button at the bottom). In the Options dialogue, select “Popular” on top left and select the check box for “Show Developer Tab in the Ribbon”.

Leave a Reply