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

Pretty cool!

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

@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!

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.

Very nice. I Hope more tips. Thanks!

@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.

@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?

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)

@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…

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

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!

@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.

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

Hey Chando,
Got it…..many thanks…

Regards,
Akash

@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.

guyz this is just awesome …….

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

Interessante Informationen.

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.

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.

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.

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?

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.

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

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.

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.

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.

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).

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

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

Is this Excel 2007??

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

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.

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)?

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.

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”)?

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.

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.

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