All Time Hits, Analytics, Charts and Graphs, Excel Howtos, Featured, Huis, Learn Excel, Posts by Hui, simulation

Data Tables & Monte Carlo Simulations in Excel – A Comprehensive Guide

Last updated on June 27, 2013

Chandoo

This is a Guest Post by Hui, an Excel Ninja and One of the Moderators of our Forums. Please note that this post is unusually large by Chandoo.org standards.

============================================================

If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape!

How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost vs …”

This post is going to detail the use of the Data Table function within Excel, which can help you answer those questions and then so so much more.

Introduction
1 Way Tables
2 Way Tables
Monitor Multiple Variables
Multiway Tables
Monte Carlo Analysis
Iterative Functions and Fractals
Download Example Workbooks

INTRODUCTION

How often have you thought “I’d like to know what our profit would be for a +/- 10, 20 and 30 % variance in the costs” ?

This post is going to detail the use of the Data Table function within Excel, which can help you answer that question.

The Data Table function is a function that allows a table of what if questions to be posed and answered simply, and is useful in simple what if questions, sensitivity analysis, variance analysis and even Monte Carlo (Stochastic) analysis of real life model within Excel.

The Data Table function should not be confused with the Insert Table function.

DATA TABLE BASICS

The Data Table function is hidden away in different locations within different versions of Excel but apart from the menu location the functionality is the same throughout.

Where is the Data Table Function

Excel 2007/10

In Excel 2007 & 2010 go to the Data Tab, What If Analysis panel and select Data Table

Excel 97-03

In Excel up to 2003 go to the Data Menu and select Table…

Both Excel 97-03 and 2007/10 then bring up the same Data Table dialog box.

… and this simple dialog box is all empowering ?

Yes !

Blue Sky Mine Co

For demonstration of the Data Table function I am going to use a simple profit model of a Gold Mine, “The Blue Sky Mine Co”. This is a fictitious mine but provides a simple model which we can use the data Table function to analyse.

It consists of 6 input variables and a simple cost and revenue model to produce a profit.

In our Blue Sky Gold Mine Co model, we can see that if we mine and treat 1,000,000 t of gold ore containing 1.68 g/t gold, we will make A$ 5.452M profit. But what if the inputs change ?

1 WAY DATA TABLES

Lets make a 1 Way Table with our Blue Sky Gold Mine Co example.

This is shown in the attached Excel Workbook on the “1 Way” Tab or 1 Way Example

In our Blue Sky Gold Mine profit calculation example, we can see that if we mine and treat 1,000,000 t of gold ore containing 1.68 g/t gold, we will make A$ 5.452M profit. But what if the grade is more or less than that value of 1.68 g/t ? After all it is only a geological estimate.

This is what the Data Table function is made for.

Next to the model add a couple of columns as shown in blue

Note: Throughout this post you will see the use of 1E6 in formulas which is simpler to write than 1,000,000.

The first column is a list of values that will be applied to each iteration of the Column Input Cell

The Top Cell of the second Column contains a formula which will retrieve the answer you want to watch, in this case Profit. It will be displayed as M$.

Now select the entire Blue Area and Select Data Table

This is the Data Table input screen.

The tricky/confusing part here is that in our example we are changing the input value to our Gold Mine Profit model using a Column of Numbers, so enter $C$6 in the Column Input Cell, Leave the Row Input Cell blank.

Click Ok

You can now see a Table of Profit Values for each Grade Value.

The variance in the Profit can easily be graphed against the Gold Grade and we can now see that if the Gold Grade is below about 1.55 g/t Au we will not make a profit and conversely if it is above 2.0 g/t Au we will make a large profit.

Before we move onto 2 Way Data Tables it is worth exploring small variations on One Way Tables.

What if my Data is in Rows?

Had our input data been arranged horizontally in Rows, we could have used a Row Input Cell to process the data.

What if I want to vary the inputs by a certain Percentage ?

Another Scenario is often where you want to vary an input by a Fixed Percentage.

This is easily done using Data Tables

Setup the input cells with the percentage variations you want to examine, noting that the values don’t have to be evenly spread.

Setup a Temporary Input Cell, This will hold the Percentage Variance briefly whilst calculations are happening. Set a default value of 0 (zero)

Change your Main Input Cell, Gold Grade in our case, to Multiply the fixed answer by 1+ the temp Input Cell.

Run the Data Table with a Column Input Cell, which will refer to the Temp Input Cell.

2 WAY DATA TABLES

So the Boss comes in and asks, what Happens if the Gold Grade changes as well as the A$/U$ Exchange Rate?

You guessed it, Two Way tables to the rescue.

This is shown in the attached Excel Workbook on the “2 Way” Tab or 2 Way Example

Two way data Tables work the same as One Way Data Tables except that you can vary 2 parameters at once.

With Two Way Data Tables you need to setup a Column of data for one Input and a Row of data for the second Input. The answer is returned at the intersection of the Row and Column.

Here we have setup a Column of Gold Grades ranging from 1.5 to 2.1 g/t Au and a Row of Exchange rates =varying from 0.70 to 1.00 A$/U$

Note at the intersection of the Row and Column there is a Reference to the variable you want to monitor in this case profit.

You can now see the variance in Profit for variations in Gold Grade and Exchange Rate.

What about varying by Percentages?

Once again we can re-arrange the input variables to examine percentage changes in the inputs via a Temporary Input Cell.

MONITORING MULTIPLE VARIABLES

So you have a complex model and want to monitor a number of input and output variables at once. No problems, Data Tables to the rescue.

In this example we are varying one input variable but monitoring 3 Output variables, 2 input variables and then doing a calculation all as part of the Data Table.

This is shown in the attached Excel Workbook on the “Monitor Multi variables” Tab or Monitor Multi Variables Example

The first 3 columns, Total Cost, Revenue and Profit are output variables even though Total Cost doesn’t change, we can still monitor it to make sure our model is working correctly

The next 2 columns, Gold Grade and Gold Price are input variables even though only Gold Grade is being varied.

The last column Cost per Oz is not calculated as part of the model (ok sometimes we forget don’t we), but it can be calculated on the fly as part of the Data Table.

The result is:

MULTIWAY DATA TABLES

But I hear you thinking, “If Data Tables are so good why can I only Change 2 variables at Once? I want to change more! “.

No Problems

Data Tables in fact allow you to Change any Number of input variables at once and monitor any number of input and output variables. It does however require a slight of hand.

This is shown in the attached Excel Workbook on the “Multi Way Tables” Tab or Multiway Table Example

First things first,

Setup a table of what scenarios you want to examine:

Setup the Data Table area to monitor Inputs, Outputs and Calculated Fields

Note that the Input Data Column will be used to select the Scenario No.

Also note that we have setup F2 to retrieve the Scenarios Name.

And in H6 we will put the Scenario name into the Data Table, who said Data Tables were only for Numbers!

Next Link the Model to the scenario

And run the Data Table

Note how the Description Column is populated with the Scenario’s Name (Text values)

So now when your boss asks you what effect the price of … has on the budget, you know where to turn.

MONTE CARLO SIMULATIONS IN EXCEL

Monte Carlo simulation (or analysis) as its name suggests puts an element of gambling into the scenarios, or more correctly allows you to measure the effect of variability on input parameters.

This is done by running scenarios against your model hundreds or thousands of times and changing the inputs each time and then measuring the effects at the end of the runs.

And Data Tables can do that? Absolutely!

First some statistics.

Everything in life has variability, from the size of Zebra’s Strips, The height of people and the Arrival times of trains, the time that people read this post, the time that it takes people to read this post.

Most things are variable around a central or mean (average) value. The spread of variability is commonly known as the distribution.

Distributions can have many names and shapes, but common ones are

Normal: Bell shaped around a mean
Uniform: All values have an even chance of selection
Exponential: Low or High values have a much higher probability that the other values

In life most distributions are Normal in nature indicating that the distribution is Bell shaped around a mean with a known method of describing the variability around this.

Excel has 2 functions that produce Random numbers, Rand() and Randbetween(). These 2 functions both have a Uniform Distribution, that is any value between the minimum and maximum values will have the same probability of being chosen.

We can convert a uniform distribution to a Normal distribution by some simple maths (simple to do, not simple to explain).

=norminv(rand(),mean,standard_dev)

Example =NORMINV(rand(),100,10)

Will generate a distribution of random numbers centred on 100 with a spread having a bell shaped curve with a standard deviation of 10. This means that the function will produce a number with a 99.7% probability of being between 70 and 130 and on average will have a mean of 100.

Monte Carlo simulations

So how can I use this and Data Tables to do Monte Carlo simulations.

Before we go any further the author wants to explicitly state that he is not suggesting that the use of Normal Distributions for the variables modeled below is appropriate, except for the purpose of demonstration of the principles behind Monte Carlo Modelling.

As with all models you need to have a good understanding of the distribution of inputs before you start playing with simulations or of which Monte Carlo is but one type. ie: Rubbish In Rubbish Out.

We can model an input vaiable, in this case Exchange rate with a distribution instead of a fixed value and then run the model a number of times and see what impact the variation has on the output.

This is shown in the attached Excel Workbook on the “Monte Carlo (Simple)” Tab or Monte Carlo (Simple) Example

The formula =NORMINV(RAND(),0.92,0.02), will generate a Random Exchange Rate with a distribution based on a mean on 0.92 A$/U$ and a spread of approximately 6 cents each way ie: there will be a 99.7% probability of the exchange rate being between 0.86 and 0.98 A$/U$.

Copying the formula down from H6 to H1005 will allow our data table to generate 1000 iterations of the model each with a randomly generated Exchange Rate.

In the model above, you can see that for a Base case exchange rate of 0.92 the profit is $M 5.452, however after running 1000 simulations the profit is actually $M 5.7134. More important is that you can now run statistics on the model to tell what is the probability of the profit being greater than 0.00 based on variance in the exchange rate etc.

Note 1: You will note that in the above data table that the Input Column (darker blue) has the formula for calculating a random input grade from a distribution. =NORMINV(RAND(),0.92,0.02)

This is a Volatile Formula , ie: It recalculates every time the worksheet changes.

What this means for the worksheet is that when the Data Table goes to Calculate Row 2 of the Data Table it will recalculate the Input value for Row 1.

On Calculation of Row 2, It doesn’t change the Table Values for Row 1, just the Input Column value.

So after 1,000 calculations of the Data Table, the Input Column values will have no relationship to the data from the original Calculations stored in the Data Table body area.

To make up for this we also add an Input variable to the Data Table.

Doing this allows the Data Table to capture and store both the Input variable and corresponding Output variable in the Data Table’s Body.

Note 2: Always run at least 1000 iterations of Monte Carlo models. This is to ensure that you have a statistical chance of getting sufficient outliers (extreme values) to make the variance analysis meaningful. This is important because as the number of iterations increases the variance of the average output decreases.

Press F9 a few times and watch the average H6:H1005 change.

Try changing the Data table from 1,000 rows to 10, 20 or 100,0000 rows. As the number of iterations increases the variance in the average of the output decreases.

Advanced Monte Carlo Simulations

We can now put our knowledge of Data Tables and Monte Carlo Simulation to the test by varying 4 input variables at the same time.

This is shown in the attached Excel Workbook on the “Monte Carlo (Advanced)” Tab or Monte Carlo (Adv) Example

In the example below we have inserted distributions for 4 input variables.

Ore Tonnes	Mean 1,000,000 tonnes	Standard Deviation of 100,000 tonnes
Gold Grade	Mean 1.68 g/t Au	Standard Deviation of 0.1 g/t Au
Gold Price	Mean 1,200 U$/Oz	Standard Deviation of 100 U$/Oz
Exchange rate	Mean 0.92 A$/U$	Standard Deviation of 0.02 A$/U$

And setup a data Table for the 4 Input Variables and main output variable, Profit.

Note: When this model is run through the Data Table, note that the Row or Column input cells can be set to anywhere. The Model is not using the value of the Input Cell (Row or Column) and isn’t even using the Run No (Column F) for the model, the data table is simply being used to run lots of iterations of the model, with the variability coming from the Random Numbers in the 4 input cells.

ITERATED FUNCTIONS INCLUDING FRACTALS

At a meeting in early 2005, the company I was working for was looking at an integrated Scheduling & Budgeting system.

The salesman gave a great demo except that the system would take approx. 30 mins to calculate our budget as opposed to a half a second in Excel.

Complaining I mentioned that our current, Excel based, system could do the job in seconds.

And he returned stating that “the system was doing a lot of things Excel couldn’t do”.

I responded “but Excel can do anything”

and he immediately shot back that “Excel can’t do a Mandelbrot”

To which I responded “Yes it can”

And he responded “Not without VB Code”

Without too much thinking I responded that I would accept the Challenge.

The attached file, which is described below is my response.

Excel Mandelbrot

The attached file is an implementation of the classic Mandelbrot implemented in Excel without the use of VBA code.

A Mandelbrot is a graphical display of the simple equation Z_n+1 = Z_n² + c, where Z is a complex number (x +iy).

Which is described at http://en.wikipedia.org/wiki/Mandelbrot_set

This can be solved in the real X-Y domain using:

Xnew = Xold² – Yold² + X_Orig and

Ynew = 2 * Yold * Xold+ Y_Orig

Study of iterated functions reveals that these functions will either converge on an answer or diverge once a boundary has been breached

In the case of the Mandelbrot, this function diverges after the function Z² > 4

So to construct a Mandelbrot a program needs simply to

Loop from Xmin to Xmax in small steps and
Loop from Ymin to Ymax in small steps and
For every X, Y Point in the above 2 loops, solve the above equations until the answer is > 4
Color the screen according to how many iterations it took to diverge or not

Simple…

Except that Excel doesn’t have any looping functions unless you use VBA Code

The calculation of the solution for any X, Y starting point is simple enough using a series of Rows and Columns where each Cells is the starting iteration of the solution for each various X, Y co-ordinate.

This is shown in the Calculations page in the Xnew, Ynew, Xold, Yold, Rsq and Count columns.

The iterations are simply done in the Xnew and Ynew columns

For each iteration we check that the Z² value hasn’t diverged (not > 4) (Xnew² + Ynew²)

And keep track of how many iterations it took to diverge, the Count Column

The above 5 lines I refer to below as the Calculator.

The trick to working out how to do this for a X-Y Grid was the use of the Table Function to send the starting positions to the Calculator and return the Count for that location.

This is the large Yellow Area.

The Large yellow area (Data Table Area) is flanked on the Top and Left by the X and Y co-ordinates for a grid encompassing the area which we want to plot.

The Table Function extracts the Top and Left values and puts them in the X Orig and Y Orig positions of the calculator.

The Calculator returns the Count of the Divergence of the Calculator to the H2 position (Top Left corner of the Grid) and that value is stored at the Grid Location.

The Data Table repeats this for each position in the X-Y Grid.

An Excel Surface Chart can then Chart the Large Yellow area in effect creating a Traditional Mandelbrot plot by joining up adjacent areas of equal value (Contouring).

The Chart can also be displayed as a 3D-Surface rather than a Contour Chart for a dramatic effect.

Zooming In can be added by adding code that allows the user to say Right click in the Large Yellow area and the code will then take the Co-ordinates and Zoom in by a fixed factor

Zooming Out can be added by adding code that allows the user to say Double click in the Large Yellow area and the code will then take the Co-ordinates and Zoom out by a fixed factor

DOWNLOAD EXAMPLE WORKBOOKS

Download the complete example workbooks described above and practice data tables on your own.

Click here to download Gold Mine Monte Carlo Simulations & Data Tables workbook. [XL 2003 version here]
Click here to download Excel Mandelbrot workbook.

Note: A few people have said the above files either Hang or Freeze there PC’s. This is probably because they have a number of large Data Tables within them.

I have uploaded each Tab as a separate Excel 2007 file, see below:

1. 1 Way.xlsx
2. 2 Way.xlsx
3. Monitor Multi Variables.xlsx
4. Multiway Table.xlsx
5. Monte Carlo Simple (updated)
6. Monte Carlo (Adv).xlsx

In the Example Files some of the Data Tables have been removed and there are instructions on how to re-instate them included in the file.

FINAL THOUGHTS

Speed

If you start adding a number of Data Tables to Complex Models you will rapidly cause even the fastest machines to grind to a halt.

VBA

The best way around the above speed issue is to setup a number of Data Tables for whatever analysis you wish to undertake. Then as you run each analysis copy the Data Table Data Area, The area between the Rows and Columns and paste it as values over itself. Then move onto the next data table and run it.

This allows the Data Tables to be quickly recalculated if required.

This process can be automated via 3 lines of VBA code for each Data Table.

‘Calculate Data Table in F5:H18, using Column Input cell C9
Range(“F5:H18”).Table ColumnInput:=Range(“C9”)

‘Copy Data Area as Values
Range(“G6:H18”).Copy
Range(“G6:H18”).PasteSpecial Paste:=xlPasteValues

‘Repeat Above for each Data Table

‘Deselect Current Range
Application.CutCopyMode = False

Cell Contents

If you look at a cell in a Data Table you will see something like:

{=TABLE(,E5)}: for a Column Input Cell
{=TABLE(E4,)}: for a Row Input Cell
{=TABLE(E4,E5)}: for a Row and Column Input Cell

Although these appear like Array Formula, they cannot be manually set.

So setting up a data table and typing =TABLE(,E5) Ctrl-Shift-Enter, only produces an error message.

Added by Chandoo

This post is by far one of the most comprehensive posts on Chandoo.org. And each of the 3100+ words in it show the passion and knowledge that Hui has. Thank you so much Hui for sharing this wealth of knowledge with our members.I have learned a lot of interesting and useful things from this article.

If you have enjoyed this article, please say thanks to Hui.

Share this tip with your colleagues

205 Comments
Ask a question or say something...
Tagged under 1way tables, 2way tables, Analytics, data tables, downloads, fractals, guest posts, hui, mandelbrot, Microsoft Excel Formulas, modeling, monte carlo simulations, multiway data tables, scenarios, simulation, spreadsheets, what-if analysis
Category: All Time Hits, Analytics, Charts and Graphs, Excel Howtos, Featured, Huis, Learn Excel, Posts by Hui, simulation

Want an AWESOME
Excel Class?

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.

Rebekah S

Reporting Analyst

FREE Goodies for you...

Related Tips

Excel Challenges

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

DPizz says:

August 21, 2008 at 5:13 am

Pretty cool!

Reply
robert says:

August 21, 2008 at 5:33 am

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

Reply
Robert says:

August 21, 2008 at 6:53 am

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

Reply
AdaptiveDervish says:

August 21, 2008 at 10:45 am

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.

Reply
Chandoo says:

August 21, 2008 at 10:57 am

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

Reply
Marino says:

August 21, 2008 at 12:22 pm

Very nice. I Hope more tips. Thanks!

Reply
Robert says:

August 21, 2008 at 12:23 pm

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

Reply
Chandoo says:

August 21, 2008 at 1:06 pm

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

Reply
Robert says:

August 21, 2008 at 1:42 pm

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

Reply
Justin says:

August 22, 2008 at 8:03 am

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)

Reply
Robert says:

August 22, 2008 at 8:21 am

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

Reply
aj says:

August 22, 2008 at 7:37 pm

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

Reply
Chandoo says:

August 26, 2008 at 10:54 am

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

Reply
- Dahmane says:
  
  January 22, 2015 at 8:30 pm
  
  started following you from the beginning about excel, i have no other word better than thank you...
  
  Reply
AdaptiveDervish says:

August 27, 2008 at 1:57 pm

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!

Reply
Robert says:

August 27, 2008 at 2:06 pm

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

Reply
Microsoft Excel KPI Dashboards - Improving with Quartile Information, Sort and Scroll - [Part 3 or 4] | Pointy Haired Dilbert - Chandoo.org says:

September 3, 2008 at 7:37 pm

[...] 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 [...]

Reply
Creating Key Performance Indicator (KPI) Dashboards in Microsoft Excel [Part 4 or 4] - Adding Microcharts | Pointy Haired Dilbert - Chandoo.org says:

September 10, 2008 at 10:31 am

[...] Creating Key Performance Indicator Dashboards in Excel with Scroll Controls [...]

Reply
Akash says:

September 22, 2008 at 1:15 pm

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

Reply
Akash says:

September 22, 2008 at 1:16 pm

Hey Chando,
Got it.....many thanks...

Regards,
Akash

Reply
Robert says:

September 22, 2008 at 1:19 pm

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

Reply
Excel Dashboard Visualization Techniques - Adding a Scatter Plot to Compare 2 Decision Parameters | Pointy Haired Dilbert - Chandoo.org says:

October 9, 2008 at 1:44 pm

[...] 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 [...]

Reply
Excel Dashboard Tutorials | Dashboards By Example says:

October 16, 2008 at 1:49 am

[...] Post 1 - Implementing a Scrolling Excel Dashboard Table [...]

Reply
Excel Dashboards Tutorial : Creating Test Cricket Statistics Dashboard | Pointy Haired Dilbert - Chandoo.org says:

October 20, 2008 at 11:48 pm

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

Reply
azmat says:

November 25, 2008 at 10:09 am

guyz this is just awesome .......

Reply
Excel Links of the week - the Christmas edition | Pointy Haired Dilbert - Chandoo.org says:

December 23, 2008 at 3:52 pm

[...] 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. [...]

Reply
Sanjay says:

December 26, 2008 at 5:58 am

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

Reply
- Steve says:
  
  April 5, 2013 at 8:48 pm
  
  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!
  
  Reply
  - Robert says:
    
    April 7, 2013 at 6:22 am
    
    Steve,
    the workbooks of this series have been implemented using Excel 2003, but the same technique is possible with 2007/2010/2013, too.
    
    Reply
Chandoo says:

December 27, 2008 at 3:06 pm

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

Reply
- nikhil says:
  
  June 18, 2013 at 1:32 pm
  
  chandoo u r gr8
  
  Reply
Happy New Year to all PHD Readers | Pointy Haired Dilbert - Chandoo.org says:

December 31, 2008 at 3:45 pm

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

Reply
Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save time, impress everyone) | Pointy Haired Dilbert - Chandoo.org says:

February 19, 2009 at 12:11 am

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

Reply
Welcome Robert & ClearlyAndSimply.com to Blogging | Pointy Haired Dilbert - Chandoo.org says:

February 21, 2009 at 12:50 pm

[...] 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 [...]

Reply
Creating Key Performance Indicator (KPI) Dashboards in Microsoft Excel [Part 2 or 4] - Adding Sort Feature | Analytics | Pointy Haired Dilbert - Chandoo.org says:

February 28, 2009 at 5:51 am

[...] Part 1: Creating a Scrollable List View in The Dashboard [...]

Reply
Adding Box Plots to Show Data Distribution in Dashboards [Part 6 of 6] | Analytics | Pointy Haired Dilbert - Chandoo.org says:

March 2, 2009 at 4:15 pm

[...] Part 1: Creating a Scrollable List View in The Dashboard [...]

Reply
lieben says:

March 3, 2009 at 10:03 am

Interessante Informationen.

Reply
P.J. says:

March 14, 2009 at 10:32 am

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.

Reply
Robert says:

March 14, 2009 at 3:11 pm

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.

Reply
- Prateek Kapoor says:
  
  September 21, 2013 at 12:15 pm
  
  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)
  
  Reply
  - Hui... says:
    
    September 22, 2013 at 2:46 am
    
    @Prateek
    
    Have a read of the description of Offset here: http://chandoo.org/wp/2012/09/17/offset-formula-explained/
    
    Reply
P.J. says:

March 15, 2009 at 3:19 am

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.

Reply
Angel Ramos says:

March 18, 2009 at 3:05 pm

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?

Reply
Robert says:

March 19, 2009 at 8:18 am

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.

Reply
Alex says:

April 7, 2009 at 3:00 pm

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

Reply
Robert says:

April 7, 2009 at 7:17 pm

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.

Reply
Web Analytics Dashboard by Percent Mobile is Fun [Dashboard Reviews] | Charts & Graphs | Pointy Haired Dilbert - Chandoo.org says:

May 1, 2009 at 3:07 pm

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

Reply
w I Got a Free iPhone With Free iPhone Apps says:

May 23, 2009 at 4:08 am

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.

Reply
Robert says:

May 23, 2009 at 6:57 am

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.

Reply
Chandoo says:

May 23, 2009 at 6:25 pm

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

Reply
Marion says:

May 29, 2009 at 9:46 pm

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

Reply
- Shaquana Bruegman says:
  
  March 8, 2016 at 2:53 pm
  
  I seriously enjoy the content you include on your web site it really has helped me out a whole lot thanks
  
  Reply
Chandoo says:

June 1, 2009 at 12:38 pm

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

Reply
Pam says:

July 1, 2009 at 8:57 pm

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

Reply
Chandoo says:

July 2, 2009 at 1:54 am

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/

Reply
HAN says:

August 22, 2009 at 6:30 am

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

Reply
HAN says:

August 22, 2009 at 6:33 am

Is this Excel 2007??

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

Reply
Robert says:

August 22, 2009 at 5:11 pm

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.

Reply
John says:

October 11, 2009 at 3:30 pm

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

Reply
Robert says:

October 11, 2009 at 6:01 pm

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.

Reply
John says:

October 11, 2009 at 6:22 pm

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

Reply
Robert says:

October 11, 2009 at 7:27 pm

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.

Reply
John says:

October 12, 2009 at 3:05 am

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.

Reply
Sales Dashboards - Examples, Templates and Best Practices - Making Sales Dashboards using Excel | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org says:

January 4, 2010 at 9:37 am

[...] KPI Dashboards using Excel – 6 part tutorial [...]

Reply
manish says:

March 9, 2010 at 10:15 am

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

Reply
Making a Dynamic Dashboard in Excel [Part 1 of 4] | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org says:

March 16, 2010 at 9:51 am

[...] Scrolling report – Chandoo [Link] [...]

Reply
Making a Dynamic Dashboard in Excel [Part 2 of 4] | Chandoo.org - Learn Microsoft Excel Online says:

May 26, 2010 at 1:42 am

[...] Scrolling report [Link] [...]

Reply
Dynamic Dashboard in Excel – Pulling it all together [Part 4 of 4] | Chandoo.org - Learn Microsoft Excel Online says:

May 26, 2010 at 8:10 am

[...] 6 Part Tutorial on Making KPI Dashboards in Excel [...]

Reply
Excel hemel » Onderzoeksblog.nl says:

July 14, 2010 at 12:14 pm

[...] 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 [...]

Reply
Veronica says:

August 3, 2010 at 1:39 pm

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.

Reply
Chandoo says:

August 4, 2010 at 5:09 am

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

Reply
Alexander says:

August 20, 2010 at 2:47 pm

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

Reply
Chandoo says:

August 24, 2010 at 2:37 am

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

Reply
Andre says:

August 30, 2010 at 5:11 pm

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

Reply
Robert says:

September 13, 2010 at 8:10 pm

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.

Reply
Andre says:

September 14, 2010 at 10:14 am

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

Reply
Robert says:

September 14, 2010 at 2:25 pm

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.

Reply
Andre says:

September 15, 2010 at 3:16 pm

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é

Reply
Robert says:

September 15, 2010 at 3:40 pm

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

Reply
Milind says:

September 16, 2010 at 2:58 am

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 !

Reply
Robert says:

September 16, 2010 at 7:46 am

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

Reply
Andre says:

September 17, 2010 at 1:10 pm

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é

Reply
Robert says:

September 17, 2010 at 1:40 pm

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.

Reply
Milind says:

September 22, 2010 at 12:02 pm

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!

Reply
Robert says:

September 22, 2010 at 1:42 pm

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.

Reply
Milind says:

September 22, 2010 at 9:22 pm

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!

Reply
Hui... says:

September 23, 2010 at 12:05 am

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

Reply
Milind says:

September 23, 2010 at 12:03 pm

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 !

Reply
Hui... says:

September 23, 2010 at 12:47 pm

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

Reply
Milind says:

September 23, 2010 at 3:55 pm

That worked just great ! Thanks again!

Reply
Robert says:

September 23, 2010 at 6:26 pm

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.

Reply
Milind says:

September 24, 2010 at 1:36 am

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

Reply
Steve says:

November 8, 2010 at 1:57 am

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.

Reply
Robert says:

November 8, 2010 at 9:56 am

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.

Reply
Do you use (or make) Excel Dashboards? Tell us about yourself to win a free training kit | Chandoo.org - Learn Microsoft Excel Online says:

December 16, 2010 at 9:10 am

[...] Creating KPI Dashboards in Excel – 6 part tutorial [...]

Reply
jawad hassan says:

January 31, 2011 at 7:13 am

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

Reply
Hui... says:

January 31, 2011 at 1:06 pm

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

Reply
Ryan says:

February 10, 2011 at 1:29 pm

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

Reply
Satbir Singh says:

February 11, 2011 at 6:23 am

Hey!!...gr8 job man...

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

Cont no 9999066800

Reply
Robert says:

February 12, 2011 at 12:52 pm

Ryan,

have a look here:

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

Something like this?

Reply
Neil says:

February 15, 2011 at 2:23 pm

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

Reply
Robert says:

February 16, 2011 at 7:43 pm

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.

Reply
Neil says:

February 17, 2011 at 9:30 am

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

Reply
Robert says:

February 17, 2011 at 4:17 pm

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.

Reply
Neil says:

February 17, 2011 at 4:22 pm

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

Reply
Robert says:

February 17, 2011 at 9:44 pm

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.

Reply
Joe says:

February 23, 2011 at 9:44 pm

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

Reply
Joe says:

February 23, 2011 at 9:45 pm

Thanks for any help you can give me.

Reply
Robert says:

February 25, 2011 at 9:52 am

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?

Reply
How to Learn Advanced Excel - 4 Proven Ways to become proficient in Microsoft Excel | Chandoo.org - Learn Microsoft Excel Online says:

March 2, 2011 at 8:57 am

[...] 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 [...]

Reply
Milind says:

March 23, 2011 at 1:24 am

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

Reply
Robert says:

March 23, 2011 at 8:01 am

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.

Reply
Executive Review Dashboard in Excel [Dashboard Week] | Chandoo.org - Learn Microsoft Excel Online says:

March 23, 2011 at 8:28 am

[...] 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?] [...]

Reply
Milind says:

March 23, 2011 at 9:16 pm

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 !

Reply
Robert says:

March 23, 2011 at 9:33 pm

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.

Reply
Robert says:

March 23, 2011 at 9:38 pm

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.

Reply
Milind says:

March 24, 2011 at 1:54 am

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

Reply
Robert says:

March 24, 2011 at 7:40 am

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.

Reply
KPI Dashboard using Microsoft Excel - Downloadable Workbook, Demo & Details | Chandoo.org - Learn Microsoft Excel Online says:

March 24, 2011 at 8:49 am

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

Reply
Milind says:

March 24, 2011 at 1:40 pm

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 !

Reply
Robert says:

March 24, 2011 at 3:10 pm

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.

Reply
Milind says:

March 24, 2011 at 5:33 pm

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

Reply
philippe says:

March 24, 2011 at 8:00 pm

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

Reply
Learning Dashboards? – Go thru these 33 Recommended Resources | Chandoo.org - Learn Microsoft Excel Online says:

March 25, 2011 at 8:46 am

[...] KPI Dashboard using Excel – 6 part tutorial [...]

Reply
Robert says:

March 25, 2011 at 11:40 am

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.

Reply
Robert says:

March 25, 2011 at 11:55 am

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.

Reply
Philippe says:

March 25, 2011 at 1:01 pm

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

Reply
Robert says:

March 25, 2011 at 1:20 pm

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

Reply
G says:

April 14, 2011 at 2:55 pm

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

Reply
Robert says:

April 14, 2011 at 5:53 pm

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.

Reply
Ron says:

April 20, 2011 at 6:27 pm

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

Reply
pibfer says:

June 3, 2011 at 11:54 pm

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

Reply
Johan says:

June 5, 2011 at 2:42 pm

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

Reply
Robert says:

June 5, 2011 at 5:35 pm

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

Reply
Robert says:

June 5, 2011 at 5:37 pm

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

Reply
Smart Dashboard Ranking Tables – blog.xlcubed.com says:

June 30, 2011 at 8:00 pm

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

Reply
Angela says:

July 14, 2011 at 6:35 pm

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

Reply
Robert says:

July 15, 2011 at 7:35 am

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.

Reply
Angela says:

July 15, 2011 at 2:10 pm

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

Reply
Robert says:

July 15, 2011 at 4:04 pm

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.

Reply
Angela says:

July 15, 2011 at 6:23 pm

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 🙂

Reply
Robert says:

July 15, 2011 at 7:31 pm

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.

Reply
Angela says:

July 15, 2011 at 7:48 pm

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

Reply
Robert says:

July 15, 2011 at 8:24 pm

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.

Reply
MANOHAR says:

July 30, 2011 at 6:33 pm

Hai Chandoo,

U R Just Awesome

Reply
thowfeek says:

August 17, 2011 at 11:36 pm

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

Reply
Hui... says:

August 18, 2011 at 3:36 am

@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

Reply
thowfeek says:

August 18, 2011 at 4:49 pm

Hi Hui

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

Reply
Hui... says:

August 18, 2011 at 11:50 pm

@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

Reply
Brian Moore says:

September 7, 2011 at 8:47 pm

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.

Reply
Hui... says:

September 8, 2011 at 12:08 am

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

Reply
Brian Moore says:

September 8, 2011 at 1:02 am

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

Reply
Nabil Khan says:

September 13, 2011 at 7:28 am

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

Reply
Hui... says:

September 13, 2011 at 8:05 am

@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

Reply
Nabil Khan says:

September 13, 2011 at 8:41 am

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?

Reply
Hui... says:

September 13, 2011 at 11:38 am

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

Reply
Nabil Khan says:

September 14, 2011 at 6:40 am

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?

Reply
Nabil Khan says:

September 14, 2011 at 6:43 am

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.

Reply
Hui... says:

September 14, 2011 at 7:05 am

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

Reply
Nabil Khan says:

September 14, 2011 at 12:07 pm

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.

Reply
Hui... says:

September 14, 2011 at 1:18 pm

"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

Reply
- Vina Sautner says:
  
  March 8, 2016 at 3:18 pm
  
  I truly appreciate the material you have on your webpage it truly has helped me out a lot thanks
  
  Reply
Nabil Khan says:

September 15, 2011 at 7:06 am

Thanks again,

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

Reply
Nabil Khan says:

September 15, 2011 at 7:30 am

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"

Reply
Hui... says:

September 15, 2011 at 7:39 am

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!

Reply
Hui... says:

September 19, 2011 at 2:47 pm

@Nabil
Not sure if this helps with your bands problem?
http://peltiertech.com/WordPress/excel-charts-with-horizontal-bands/

Reply
Learn Any Area of Excel using these 80 Links | Chandoo.org - Learn Microsoft Excel Online says:

December 12, 2011 at 9:02 am

[...] KPI Dashboards in Excel [...]

Reply
Best of Chandoo.org – 2011 | Chandoo.org - Learn Microsoft Excel Online says:

December 30, 2011 at 8:53 am

[...] KPI Dashboards in Excel 112,683 [...]

Reply
Ramon says:

December 30, 2011 at 8:20 pm

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.

Reply
- Robert says:
  
  January 21, 2012 at 8:55 pm
  
  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?
  
  Reply
Ksandra2901 says:

January 5, 2012 at 2:56 pm

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

Reply
- Robert says:
  
  January 21, 2012 at 8:59 pm
  
  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?
  
  Reply
ksandra2901 says:

January 22, 2012 at 9:27 pm

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

Reply
Anup says:

February 11, 2012 at 9:20 pm

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/

Reply
Designing a Customer Service Dashboard in Excel | Chandoo.org - Learn Microsoft Excel Online says:

February 23, 2012 at 12:45 am

[...] KPI Dashboards in Excel – 6 part tutorial [...]

Reply
Vineet says:

February 23, 2012 at 1:59 pm

This website is really amazing. 🙂

Reply
nitin says:

March 11, 2012 at 9:35 am

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

Reply
- Hui... says:
  
  March 11, 2012 at 2:48 pm
  
  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)
  
  Reply
tua022012 says:

March 19, 2012 at 1:53 am

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!

Reply
KPI (Key Performance Indicator) Dashboards in Excel – Tutorial [Part … | how videos says:

May 8, 2012 at 9:54 am

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

Reply
Excel Dashboard Examples - 66 Dashboards to Visualize Excel salaries around world | Chandoo.org - Learn Microsoft Excel Online says:

July 30, 2012 at 10:29 am

[...] KPI Dashboards using Excel – 6 part tutorial [...]

Reply
Rudd says:

August 5, 2012 at 3:16 pm

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

Reply
Kanchan says:

August 7, 2012 at 9:28 am

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

Reply
Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4] | Chandoo.org - Learn Microsoft Excel Online says:

August 31, 2012 at 9:40 am

[...] KPI Dashboards in Excel – 6 part tutorial [...]

Reply
Creating Customer Service Dashboard in Excel [Part 3 of 4] | Chandoo.org - Learn Microsoft Excel Online says:

August 31, 2012 at 9:42 am

[...] KPI Dashboards in Excel – 6 part tutorial [...]

Reply
OFFSET formula – Explained | Chandoo.org - Learn Microsoft Excel Online says:

September 17, 2012 at 8:11 am

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

Reply
GiGi says:

September 19, 2012 at 7:28 pm

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.

Reply
- Chandoo says:
  
  September 20, 2012 at 1:31 am
  
  @Gigi... You need to apply this new style to your table.
  
  Reply
  - Robert says:
    
    September 20, 2012 at 11:48 am
    
    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?
    
    Reply
  - Chandoo says:
    
    September 20, 2012 at 12:58 pm
    
    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/
    
    Reply
Karthik says:

October 12, 2012 at 7:03 am

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

Reply
Geekyard says:

November 4, 2012 at 2:11 pm

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

Reply
Linked-In Post – E-Mail Address Capture (NOT FOR PHISHING!) | The Method and The Madness says:

March 5, 2013 at 11:20 pm

[...] KPI Dashboard [...]

Reply
Excel 2007 tableau de bord says:

April 15, 2013 at 11:12 am

[...] 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 [...]

Reply
10 Rookie mistakes to avoid when making dashboards | Chandoo.org - Learn Microsoft Excel Online says:

April 25, 2013 at 9:47 am

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

Reply
antoine says:

April 25, 2013 at 12:18 pm

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

Reply
Michelle says:

May 7, 2013 at 2:56 am

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

Reply
Jason says:

May 20, 2013 at 12:24 am

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!

Reply
- Hui... says:
  
  May 20, 2013 at 1:17 am
  
  @Jason
  Absolutely
  Generally setup a report with columns for fields and source each from wherever you need to
  
  Reply
  - Jason says:
    
    May 20, 2013 at 1:31 am
    
    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.
    
    Reply
    - Hui... says:
      
      May 20, 2013 at 3:55 am
      
      @Jason
      Can you post a sample file
      Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
      
      Reply
Ursula Lampsas says:

May 22, 2013 at 10:30 am

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

Reply
vlatro says:

June 7, 2013 at 6:52 am

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?

Reply
Ayanna says:

July 1, 2013 at 9:05 pm

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!

Reply
Luke says:

July 14, 2013 at 11:55 pm

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

Reply
Matt says:

July 31, 2013 at 5:33 pm

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!

Reply
Swati Singh says:

August 27, 2013 at 8:42 am

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.

Reply
Rakesh says:

September 18, 2013 at 10:16 am

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"

Reply
- Chandoo says:
  
  September 18, 2013 at 1:03 pm
  
  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.
  
  Reply
Arup Chakraborty says:

January 10, 2014 at 11:00 am

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?

Reply
Felipe says:

January 18, 2014 at 4:51 am

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.

Reply
Free Download Excel Dashboard Spreadsheet Templates 2010 | ProjectManagerClub says:

January 29, 2014 at 5:36 pm

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

Reply
Rajiv says:

February 5, 2014 at 8:08 am

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

Reply
Rajiv says:

February 5, 2014 at 8:12 am

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

Reply
- Ashutosh says:
  
  November 23, 2021 at 6:27 am
  
  @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...
  
  Reply
G Singh says:

June 12, 2014 at 3:46 pm

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

Reply
- Rajiv says:
  
  June 13, 2014 at 4:35 am
  
  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".
  
  Reply
KPIs Dashboards Templates Excel | scopeminded says:

July 10, 2014 at 3:31 pm

[…] http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ […]

Reply
Ahmed says:

September 7, 2014 at 4:48 am

My friend, you are really an amazing guy, please accept my warmest thanks

Reply
My name is Amit Sharma says:

September 7, 2014 at 6:07 pm

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.

Reply
Ritesh says:

October 14, 2014 at 12:39 pm

Great chart.. with this data can be viewed very easily!.. thanks you so much

Reply
Razia says:

October 14, 2014 at 9:58 pm

Fantastic-simple & easy to follow steps

Reply
Ashvene Devi says:

November 13, 2014 at 5:25 am

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?

Reply
Becky says:

December 30, 2014 at 5:38 pm

Hi Chandoo,

Great stuff!!!

How can I increase the KPIs to more than 20?

Thanks

Reply
Prajakta says:

January 13, 2015 at 7:12 am

Hi,

I am stuck in initial stage itself.. i don't know how to add up thes scroll bar in data sheet

Reply
Isaac says:

January 30, 2015 at 8:17 pm

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.

Reply
KJ says:

February 24, 2015 at 12:24 am

I just set this up and did indeed impress my boss who called it awesome, thank you. 🙂

Reply
Brandon says:

March 4, 2015 at 8:43 pm

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

Reply
Vincent says:

March 17, 2015 at 2:57 pm

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.

Reply
Mbali says:

April 20, 2015 at 1:39 pm

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?

Reply
Julian says:

April 22, 2015 at 10:40 am

I tried to add one more option button to show the original phase without filtering and it works well. Thanks for your coach.

Reply
priya says:

July 2, 2015 at 5:06 am

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

Reply
Excel’de Dashboard (?? Raporlar?) Olu?turma | says:

July 30, 2015 at 8:30 pm

[…] Creating KPI Dashboards in Excel [6 part tutorial] […]

Reply
50 Free Productivity Dashboards and Templates says:

July 31, 2015 at 2:41 am

[…] 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. […]

Reply
How to Create a KPI Dashboard in Excel [Part 1 of 3] says:

September 4, 2015 at 6:49 am

[…] KPI Dashboard by Chandoo – 6 Part Series. […]

Reply
Free Business Templates Excel Dashboards | Excel Dashboard says:

September 10, 2015 at 5:19 pm

[…] 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 […]

Reply
Jitendra says:

September 12, 2015 at 9:50 am

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.

Reply
Sandra Lee says:

October 28, 2015 at 12:49 pm

Where do you sign up for the 25 pages of tips and tricks ebook?

Reply
5 Reasons Freelancers Need to Learn Excel This Year - Freelancing Philippines says:

March 8, 2016 at 3:12 am

[…] 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 […]

Reply
5 Reasons Freelancers Need to Learn Excel This Year - Bernard Vukas says:

March 9, 2016 at 10:09 am

[…] 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 […]

Reply
Newbie says:

September 5, 2016 at 1:17 pm

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

Reply
Oluchukwu says:

November 3, 2016 at 8:34 am

The tutorial was very educative and revealing.
thank you

Reply
Ruby Doan says:

November 11, 2016 at 2:06 am

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?

Reply
Thakur says:

February 22, 2019 at 4:55 am

Thank you for sharing your knowledge!
its so useful.

Reply
Waleed says:

March 4, 2021 at 9:15 am

How the data can be displayed into table when i enter new data after serial # 100 in data sheet

Reply