This is a guest post by Vijay, our in-house VBA Expert.
Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.
Consolidate Data Demo
First, lets take a look at the consolidate data VBA code.
Consolidating Data from different Excel files – the setup
There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure).
Lets try to make a generic consolidation macro so that we can use this almost anywhere.
We start of by creating a simple table on our sheet, we will call this List.
- On this table essentially we are defining everything that our VBA code needs to know to copy and paste data.
- We start by telling the name of the Excel workbook and then the complete path (location) of the file.
- In the next 2 cells we define what are the starting cell and the ending cell that contains our data.
- Next we are put the name of the worksheet where the data will be pasted. In our example the sheet remains the same however as per your requirements you may put a different sheet name.
- The last option is to specify where to paste the copied data and we only need to tell the start cell address, the code will automatically select the next empty cell in that column and then paste the data from that point onwards.
Let’s understand the code.
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As StringstrListSheet = “List”
On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2”).Select
‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> “”
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
ErrH:
MsgBox “It seems some file was missing. The data copy operation is not complete.”
Exit Sub
End Sub
We have used the Workbook object to accomplish this task and also the Error handler to trap any errors that may come in case any file is missing.
The current code will display a message box when it is not able to open any file and will stop.
We start by assigning the workbook where we want to consolidate the date to the variable currentWB by using the statement:
Set currentWB = ActiveWorkbook
After this a looping construct has been used to go through all the inputs provided one by one and open the workbooks, it has been assumed these workbooks to contain on the data that we need to copy hence I did not specify the source sheet name, however this can be easily added to this code to add more functionality.
Inside our loop are the 4 variables which are assigned the
1) File name,
2) Copy Range,
3) Where To Copy and
4) Which Column contains the starting cell to paste data.
We open the data workbook by using the Application.Workbooks.Open method.
Once we have our first data workbook open, we assign this to the dataWB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.
Next we select the data that has been assigned to the copy range and copy to the clipboard.
We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “strWhereToCopy”. This allows us to paste data onto separate sheets within the same workbook.
I have also made use of UDF (user defined function) to find the last cell in the column that we specify.
Once we have found the last row we then select the next empty cell below that and paste our data then.
Additional things that may be used to enhance this code
1. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted.
2. Allow the user with the option to clear data before new is pasted.
Download Consolidate Data from different files Demo file
Click here to download the workbook.
Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate.
More on VBA & Macros
If you are new to VBA, Excel macros, go thru these links to learn more.
- More Examples on Consolidation
- What is VBA & Macros? Introduction
- Excel VBA Example Macros
- VBA tutorial videos
Join our VBA Classes
If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
245 Responses to “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.
@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 🙂
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.
@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 😀
@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!!
@aj ... thanks 🙂 welcome to commenting. Without wonderful readers like you all, this blog is just another site.
started following you from the beginning about excel, i have no other word better than thank you...
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.
[...] 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 [...]
[...] Creating Key Performance Indicator Dashboards in Excel with Scroll Controls [...]
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.
[...] 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 [...]
[...] Post 1 - Implementing a Scrolling Excel Dashboard Table [...]
[...] excel dashboard tutorials: Creating KPI (Key Performance Indicator) Dashboards in Excel - 4 part tutorial Making Visualizations for Dashboards Too much data? Use tables [...]
guyz this is just awesome .......
[...] 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. [...]
I was not aware abt how to draw dashboard tables but the published notes/exmple helped me to undersstand /draw table now.
Thanks
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!
Steve,
the workbooks of this series have been implemented using Excel 2003, but the same technique is possible with 2007/2010/2013, too.
@Azmat and Sanjay: Welcome. I am happy you liked it.
chandoo u r gr8
[...] Excel KPI Dashboards - 4 Post Tutorial and Free Downloads [...]
[...] DIY Excel Training: Learn Excel Formulas in Plain English | Executive Dashboards in Excel - 4 Part Tutorial | 15 Excel Fun [...]
[...] 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 [...]
[...] Part 1: Creating a Scrollable List View in The Dashboard [...]
[...] Part 1: Creating a Scrollable List View in The Dashboard [...]
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.
hi,
i am the beginar for dashboard. why have u used minimum value in offset formula. =OFFSET(Data!D5,Calculation!$D$5,0,1,1)
@Prateek
Have a read of the description of Offset here: http://chandoo.org/wp/2012/09/17/offset-formula-explained/
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.
[...] on dashboards: KPI Dashboards using Excel (6 part tutorial and downloads), Excel Dashboards theory, principles and [...]
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.
@"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...
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).
I seriously enjoy the content you include on your web site it really has helped me out a whole lot thanks
@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.
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
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/
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.
[...] KPI Dashboards using Excel – 6 part tutorial [...]
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
[...] Scrolling report – Chandoo [Link] [...]
[...] Scrolling report [Link] [...]
[...] 6 Part Tutorial on Making KPI Dashboards in Excel [...]
[...] 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 [...]
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.
@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?
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
@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. 🙂
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
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.
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
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.
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é
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
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 !
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
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é
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.
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!
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.
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!
@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.
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 !
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
That worked just great ! Thanks again!
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.
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
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.
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.
[...] Creating KPI Dashboards in Excel – 6 part tutorial [...]
your dash board formula is not working and giving error of reference is not valid
@Jawan
Can you be more specific about what your problem is, as hundreds of people have downloaded this without problem
Can a similar dashboard be constructed, but with filtering capabilities? I want the charts to depend on the multiple filters selected by the user.
Hey!!...gr8 job man...
this help me a lot...
Looking forwrd for coaching. kindly guide me.
Cont no 9999066800
Ryan,
have a look here:
http://www.box.net/shared/5d9t5hfb1x
Something like this?
Great website, here comes the silly question so apologies now:
How can you change the scroll window to show more than 10 rows?
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.
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
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.
Thanks Robert worked a treat.
Sorry for my noddy questions new to dashboard creation.
Keep up the good work!!
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.
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
Thanks for any help you can give me.
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?
[...] 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 [...]
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
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.
[...] 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?] [...]
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 !
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.
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.
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
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.
[...] original KPI dashboards using Excel article was so popular. They still help around 12,000 people around the globe every month. Many of [...]
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 !
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.
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
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
[...] KPI Dashboard using Excel – 6 part tutorial [...]
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.
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.
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
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).
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
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.
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} )
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
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
@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.
@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.
[...] Chandoo and Robert over at the PHD blog have a nice a 4 post series of posts about Creating KPI Dashboards in Microsoft Excel. [...]
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? 😀
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.
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... 😀
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.
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 🙂
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.
Thanks Robert for all your help! And thanks for posting the link to your article on the search functionality - very useful functionality to have.
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.
Hai Chandoo,
U R Just Awesome
Hi all in the scroll KPI , how add more kPI column ,
@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
Hi Hui
thanks
i had tried same but scroll not working properly after KPI 5
pls guide me
@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
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.
@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.
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
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
@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
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?
@Nabil
Did you populate the new rows with extra data?
.
What Chart ?
This post doesn't have a chart ?
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?
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.
@Nabil
If you email me the file I'll have a look at your questions
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.
"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
I truly appreciate the material you have on your webpage it truly has helped me out a lot thanks
Thanks again,
Do you think its possible to do the cloud idea and the gas tank indicator as per my previous post?
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"
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!
@Nabil
Not sure if this helps with your bands problem?
http://peltiertech.com/WordPress/excel-charts-with-horizontal-bands/
[...] KPI Dashboards in Excel [...]
[...] KPI Dashboards in Excel 112,683 [...]
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.
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?
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
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?
Hi thanks for replying. I've sorted it now thanks!
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/
[...] KPI Dashboards in Excel – 6 part tutorial [...]
This website is really amazing. 🙂
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
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)
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!
[...] KPI (Key Performance Indicator) Dashboards in Excel – Tutorial [Part … Link to this post!No related posts. [...]
[...] KPI Dashboards using Excel – 6 part tutorial [...]
I love you chandoo . Because you are professionally exciting and very intelligent .
Thank you for your labouruous site
how to make a dash board in excel ?
Is that dashboard is not the part off excel ?
[...] KPI Dashboards in Excel – 6 part tutorial [...]
[...] KPI Dashboards in Excel – 6 part tutorial [...]
[...] Charting & Dashboards: Dynamic range charts | Top x chart | Analyzing large datasets | KPI dashboards [...]
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.
@Gigi... You need to apply this new style to your table.
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?
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/
Hi Chandoo. Can you please tell me how did you type "?" in the cell(E5, E16) formula. I hope it is not symbol!
This will be highly while getting increments and performance appraisal 😉 DATA talks more
[...] KPI Dashboard [...]
[...] 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 [...]
[...] dashboards – Information, examples & tutorials Creating a KPI dashboard using Excel – 6 part tutorial Sales dashboards – 32 examples & [...]
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
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!!
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!
@Jason
Absolutely
Generally setup a report with columns for fields and source each from wherever you need to
I think I know what you mean. However the second report has two rows for each instance, I want to lookup based on the matter number and return the value say in column g on the row below.
Only option may be to match/index the data from sheet two into sheet one then reference the new column in sheet one in the table.
@Jason
Can you post a sample file
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Great Information Can't Watch for Another Post. Great Stuff
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?
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!
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
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!
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.
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"
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.
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?
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.
[…] is a screen shot of KPI dashboard, in this post you will understand the trick of creating this one in easy […]
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
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
@Chandoo - I guess 13-14 years on & this page still lands up in Google searches for dashboarding... what better example would be for timeless/ageless knowledge on Excel? Just that, it might need an update related to Excel 2007 & onward (ribbon) versions...
@Rajiv - Agreed with you 100%, I used this technique (array formula) after playing around for some time with other methods. Just to add my two cents for other & upcoming readers, the range should be selected as per required 'height' of the scrollable information before pressing CSE (Ctrl+Shift+Enter)... i.e. If the dashboard maker wants to display 5 rows of scrollable information, then 5 continuous cells to be selected while entering the OFFSET formula & then press CSE..
Nevertheless, congratulations to you as well, for providing an important improvement which is valid after 7 years & counting...
I can't find the Menu > view > tool bars and select “forms” to see the forms tool bar in Excel 2010. Please help
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".
[…] http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ […]
My friend, you are really an amazing guy, please accept my warmest thanks
hi sir,
I have started excel after a long time.
I didn't get a step => how to start like
M sorry! m new
I downloaded and opened the excel workbook, three sheets are there
1-DashBoard, 2-Data, 3-Calculations
when I inseted a new sheet, then I selected ten blank rows for table and I am not able to insert a scroll-bar from > view menu> tool bars and select “forms, I am using Excel-2007. I am not getting the View menu.
Kindly guide, what should I do.
Great chart.. with this data can be viewed very easily!.. thanks you so much
Fantastic-simple & easy to follow steps
Hi Chandoo,
I looked trough your dashboard and it is really impressive. However i am still unable to get the dashboard working like how it is shown in your template.... is there any usage of macros here?
Hi Chandoo,
Great stuff!!!
How can I increase the KPIs to more than 20?
Thanks
Hi,
I am stuck in initial stage itself.. i don't know how to add up thes scroll bar in data sheet
I think this entire article is a great example of when trying to use "straight" excel is a bad idea. By the time you have set up all of these fancy components, you might as well have just created a userform and designed the whole thing in VBA. After you were done, you'd have everything this article accomplished but then a whole lot more....And VBA code is easier to document, provide error handlers, troubleshoot, maintain, and scale.
This is great for a person who doesn't know VBA and has decided they never want to. But it's probably not the best choice for everyone else.
I just set this up and did indeed impress my boss who called it awesome, thank you. 🙂
This may be remedial but I was trying to recreate this from your instructions above and I get stumped by the part that says to create a 10 row table? if the data has many more rows than 10, how do you make the table only 10 rows and then how do you enable it to scroll? You lost me. 🙁
Hi all,
I struggle on the first step of this tutorial. This tutorial assumed you have very good foundation of excel which I don't.
I have figured it out and posted a video for those who are unable to do this tutorial.
http://youtu.be/kDl7NSMsN9M
I hope it helps and this tutorial is awesome! I did not know form control exists.
Hi,
My calculation sheet produces "#DIV/0!" errors from cell G23, downwards and across. There must be something wrong with my 'choose' function, but I can't figure out what the problem is?
I tried to add one more option button to show the original phase without filtering and it works well. Thanks for your coach.
HI Chandoo,
I need to paste linked charts in one location only with the help of click of each option button. i have four charts
[…] Creating KPI Dashboards in Excel [6 part tutorial] […]
[…] Chandoo delivers access to a free Key Performance Indicator (KPI) dashboard plus offers tutorials on implementing and maximizing how to use it in your business to get greater performance from your talent. […]
[…] KPI Dashboard by Chandoo – 6 Part Series. […]
[…] Start using a KPI dashboard in order to keep track of your metrics that can assist you establish criteria for career advancements and bonus for your […]
While doing the same in format control, conrol option is not showing I am using excel 13 what could be the possible reason as I am unable to go ahead.
Where do you sign up for the 25 pages of tips and tricks ebook?
[…] is intermediate to advanced level. I would like you to read Chandoo’s in-depth 6-part tutorial: Creating KPI Dashboards in Excel. By doing that, you’ll be able to build some amazing […]
[…] how to design Excel dashboards, I urge you to read Chandoo’s in-depth 6-part tutorial: Creating KPI Dashboards in Excel. You’ll be able to build some amazing […]
Hello.
I am very much a newbie here...I thought I knew excel but OMG there is soo mucch to learn. sooo... in thr tutorial for creating a scrolling table. I can't seem to be able to insert the scroll bar. When I go to "View" I cannot see toolbars neither can I see "forms" . I am using the 2007 version. please help
The tutorial was very educative and revealing.
thank you
Sorry because of my ignorance, but I could not figure out what kind of function or formula the author have used for the "Actual Start Position" cell in sheet "Calculation". Please Anyone can help me to understand this?
Thank you for sharing your knowledge!
its so useful.
How the data can be displayed into table when i enter new data after serial # 100 in data sheet