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

206 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

How to make a pivot from another pivot in Excel?

Excel Howtos

65 Responses to “Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download]”

claudia says:

December 8, 2010 at 3:22 pm

WOW, is all I can say.

I could not have imagined a dynamic dashboard without getting approved software budget and a team of people involved to create it. Given that I am a relative newbie to excel and actually got here by looking for pivit table help, I imagine that i would not be able to make anything myself. But armed with the demo excel sheet I will press buttons (and I will report back how that went;-)

Claudia

Reply
winston says:

December 8, 2010 at 6:14 pm

Good stuff Chandoo, thanks
The slicer buttons take up quite a bit of room on the dashboard
Is there a way to make the buttons smaller so we can have more room for charts, tables, and commentary?

Kind regards,
Winston

Reply
- Jova says:
  
  July 2, 2012 at 11:02 am
  
  You can resize the slicers! When you click the slicers you can change the height and width of columns and slicers. You can also, under slicer style click "New slicer style" where you can define your own style, which enables you to change most things, including font size.
  
  Reply
Gregory says:

December 8, 2010 at 7:13 pm

I hadn't seen the Group Option used as you did for the Duration PivotTable. And thanks for showing how to remove the Field Buttons on a PivotChart, I loathe them with all my heart.

Fantastic design and a great dashboard.

Reply
Chandoo says:

December 9, 2010 at 1:37 am

@Claudia.. I am glad you like it. Do let us know how your adventures go.

@Winston: You can resize slicers or increase the number of columns inside. Unfortunately, we can not readjust the font sizes in slicers. So when you resize, you will see partial text.

@Gregory: Thank you. I am happy you like it 🙂

Reply
- kris says:
  
  April 2, 2014 at 8:49 am
  
  Hi Chandoo, your dashboards are really professional and simple. I do have some question, if I have the following scenario, could you help to advise : -different data sources eg monthly
  -calculations percentile
  -%difference between financial year
  
  Thank you so much!
  
  Reply
Divya says:

December 9, 2010 at 6:20 am

Hi,

Thanks for your great information.It has helped me a lot.
Now,I can build my excel addin for Excel 2010 better with your tips.

Reply
Istiyak says:

January 1, 2011 at 6:06 pm

Hi chandoo i am new reader for ur site.and really found good stuff and temp. But i suggest u 2 put a guidance step sheet in temp so anyone can understand easily.and also help me to become awesome as ur noume.

Reply
Excel Links – My First International Excel Workshop Edition | Chandoo.org - Learn Microsoft Excel Online says:

January 17, 2011 at 8:47 am

[...] [Related: Dynamic Dashboard using Pivot Tables & Slicers] [...]

Reply
Stevros says:

February 24, 2011 at 1:48 pm

Chandoo, Wow these are very powerful reports. I will be implementing them straight away. It will save me hours of work. Thankyou so much.

Reply
Paul Avenell says:

July 29, 2011 at 3:42 am

Hi Chandoo,

I love the Slicer, but how do I link a slicer for different data sheets e.g.: Client data on one tab and products on another tab, as I find that as long as you use pivot tables off the same data you can link the Pivot tables using Slicer connections.

Regards
Paul

Reply
Vivek says:

August 18, 2011 at 4:49 pm

I appreciate the work you have posted on your website - very informative and easy to understand. I just wanted to inform you that you can make selections within the slicer too by using Ctrl and selecting the fields you want to group and use as filter.

I had a question regarding the data used in pivot tables. Is there a way to update the data (eg. a new customer entry) and have the pivot tables and the linked charts in dashboard automatically update? I will search for the answer in other posts so ignore if you have covered it elsewhere.

Thanks again and keep up the good work.

-Vivek

Reply
Brij Arora says:

August 24, 2011 at 11:24 am

Dear All,

Me too is a die hard fan of Slicer. it's requirement was arise when management is feeling it difficult to juggle with filters for sales of a particular location, Product Category in Pivot Table.

Got very positive response when introduced to tackle the above situation. furthermore in slicer setting there would be option to enable or disable deleted data is handy for particular scenario.

These are eye catching color themes would be like icing on the cake.

There is one more feature of excel 2010 which proves to be tool for great time saving is "Repeat Labels" in Pivot Tables.

Reply
Katherine says:

December 15, 2011 at 4:35 pm

This is fantastic!! Your steps were super to easy follow. I can't wait to show my new dashboard off to the boss. Thank you so much!

Reply
Van says:

April 20, 2012 at 12:36 pm

This might be a little unrelated but I'd like to know which software was used to record your on screen actions? I'd like to use it for tutorials on models that I build for my customers. Thanks!

Reply
- Hui... says:
  
  April 22, 2012 at 3:37 am
  
  @Van
  Have a look here: http://chandoo.org/wp/about/what-we-use/
  
  Reply
DV says:

July 6, 2012 at 9:47 am

The slicers are coming in a sorted order... How can i get it in the way it appears in my original data.... The settings show to sort them A to Z or the other way round but they are option boxes and can not be unchecked... What are my options????

Reply
Making your dashboards interactive [Dashboard Essentials] | Chandoo.org - Learn Microsoft Excel Online says:

August 3, 2012 at 12:37 am

[...] Using slicers to make a dynamic dashboard in Excel [...]

Reply
Duncan Williamson says:

August 7, 2012 at 11:52 am

I watched the video and then worked through an example of my own, also telephone costs by coincidence. It took me about 30 minutes to do everything. Once you've understood the basics of pivot tables and slicers, all that limits you is your imagination!

The only thing missing from the video is now to change the number of columns in a slicer: Right click a slicer then Size and Properties, Position and Layout, Layout, Number of Columns ...

Good page and video.

Duncan

Reply
soycharnichart says:

September 6, 2012 at 5:44 pm

How do you insert 'Year' in the Pivot Table Field List if it doesnt exist in the Master table???

Thanks

Reply
Manu says:

September 25, 2012 at 7:53 pm

Hi,

Can I disable the multi-selection of the slicer to only allow one selection at a time?

Thanks in Advance

Reply
- Chandoo says:
  
  September 26, 2012 at 1:50 am
  
  @Manu.. as of Excel 2013, this is not supported yet. But you can remove slicer heading, clear filter button and style it so that it looks like a single selection. You can also use Macros to ignore previous selection upon multiple selection, but I would not recommend it.
  
  For an example on styling see - Interactive Pivot Calendar
  
  Reply
Devin says:

November 2, 2012 at 2:17 pm

Awesome guide! The dashboard I made blew people away. I do have one question. I want the chart title to match what I have selected. How can I do this without writing macros?

Reply
- Hui... says:
  
  November 3, 2012 at 5:33 am
  
  @Devin
  Lets say what you have selected in in A1
  Select the Chart then Select the Title
  Click in the Formula Bar and type =A1
  enter or click the small arrow to the left of the Formula Bar
  Enjoy
  
  Reply
Johnny says:

December 3, 2012 at 2:22 am

Love the slicers and use them often in my dashboards. Question about the data (specifically the date) I see the "date of call" column but was wondering how were you able to filter on slicers by year and month when there is only a date of call entered into the data?

Thanks for your help!

Reply
Jet Copeland says:

January 21, 2013 at 2:25 pm

Thanks for taking the time to create this interesting and very useful tutorial!
I was able to create a similar dashboard in a short time after watching your tutorial. The problem I am having now is how to update the pivot tables and dashboard graphs when a change is made in the raw data. I tried two methods; Change data Source and Refresh. When I used Change Data Source (Options-> Change data source) the values in the pivot tables didn't update. When I tried refresh the values in the pivot tables disappeared as well as the information in the graphs, since the data in the pivot tables no longer existed.
I have been searching for a solution for a while now but I have unfortunately not been able to solve this problem yet. Any help someone can provide is GREATLY appreciated.
All the best

Reply
Nigel says:

April 19, 2013 at 9:27 am

Hi, looks great, but how valuable is power view when it comes to financial data? I've been having trouble trying to visualize how I would use power view to report of financial data.

Reply
Snooky says:

April 21, 2013 at 1:29 pm

Hi Chandoo, you are awesome! Thanks for the good work!

Reply
beth says:

May 21, 2013 at 3:58 am

there is duplication for my slicer, probably cause i choose date, time as my options. i changed it to date but still theres a duplication of the same date

Reply
Alex Cardoso says:

June 7, 2013 at 7:45 pm

Just Great! Thank you for the time to put this together and teach us.

Alex Cardoso from Indaiatuba, Brazil.

Reply
jose says:

July 1, 2013 at 9:03 pm

First of all I would like to thank you guys for this post I used this amazing tool with the help of your tutorial to create a dashboard for one single account and my regional manager said "good job, it looks very profesional" she was so impresed that now she wants one daschboard with all the acounts and services she is going to replace her KPI reports with my report !! I smell a promotion!! My demand was a new laptop with MS 2010 and it was granted. now I have allot of work and many many questions to post .. kudos

Reply
krishna prasad says:

July 4, 2013 at 10:13 am

Hi Chandoo

I want to say thanks first because i loved ur tutorials

i have a small doubt how to insert slicer from external connections

i searched every where could you please explain how to insert a slicer from external source

Reply
- Brij Arora says:
  
  September 3, 2013 at 5:41 am
  
  @Krishna Prasad
  use external source data as pivot table then you will be able to use slicer.
  
  Reply
Jyothi says:

August 16, 2013 at 2:11 am

Hello Chandoo,

How to get rid of the > items in Months slicer?

They are appearing when there is a grouping on the date field in pivot

Thanks

Reply
Emma says:

August 29, 2013 at 3:05 am

Hi Chandoo,

One problem always bothers me when i use slicer. I have no idea aobut how to change the number format in slicer. Want to display number in slicer as general format, but it always displays other number format such as date.
I check my source data and it doesn't effect the number format.

Look forward you or any EXPERTS to solve it. Thanks very much!

In the end, This website is awesome!!!

Reply
- Sunil says:
  
  April 16, 2015 at 10:31 am
  
  Hi Emma,
  
  Were you able to resolve your query? I have a similar problem. I use Excel 2013 and the field I'm dropping into the slicer is a currency field ($1.00, $1.05, $1.10 etc.) representing the exchange rates that the user can choose from. The items in the slicer revert back to general format (1, 1.05, 1.1, etc.) although the source field is formatted as currency field. Is there a way to fix this?
  
  Reply
  - Chandoo says:
    
    April 16, 2015 at 12:23 pm
    
    @Sunil & Emma: You can create a new column in your raw data which has currency as text, using the TEXT formula like this =TEXT(currency_val, "$#,##.00"). Use this column to create the slicer.
    
    Reply
    - Sunil says:
      
      April 17, 2015 at 6:38 am
      
      Thanks for the response Chandoo. It works as you suggested. However, if the users were to pick more than one item in the window I'd like to know what is the max value and utilise that value in a DAX formula.
      Also... there is no issue if I were to throw a slicer over a normal pivot. The trouble comes when I choose the 'Add this data to the Data Model' option which I need for the PowerPivot.
      
      Reply
Jonas says:

November 22, 2013 at 8:55 am

Hi Chandoo (Or others)

Is there a way to make the color change, when the value changing after the use of a slicer?

Lets say the value is 4,5, when i press the slicer, and the value change to 3,5 i would like the color to change. Can anyone help?

Thank you.

Reply
Burendei says:

January 21, 2014 at 3:16 am

Hi Chandoo,

It was very useful video for me. Thanks.
But I have one question to ask.
How can I connect data which is growing in size (rows, records) by time (daily, monthly etc.) to this kind of dashboard?
Or it is only on select number of data?

Thank you.

Reply
Angela says:

February 6, 2014 at 10:52 am

Chandoo zindabad!

Reply
Angela says:

April 8, 2014 at 7:00 am

Hi Chandoo,
I have been able to create something similar quite easily. The problem that I am facing is that I want to keep the Top 10 filters permanently. If I select one option and then clear the filter, the chart removes the Top 10 filter; I want it to go back to Top 10 filter.

Is there a solution to this problem?
Regards

Reply
- Federico says:
  
  June 27, 2014 at 10:43 am
  
  Thanks a lot for the tutorial and for the demo file!
  I have the same problem of Angela: after clearing the filter applyed on P1, the filter on P1 shows all the customers without filtering top 10 (as it was before).
  Thanks!
  
  Federico
  
  Reply
  - Justin says:
    
    July 22, 2014 at 11:29 pm
    
    Go to your pivot table, right-click and choose "pivot table options." On the "Totals & Filters" tab check "Allow multiple filters per field."
    
    Reply
    - Federico says:
      
      July 23, 2014 at 7:51 am
      
      Justin, thank you so much!
      now after clearing the filter applyed on P1, the filter on P1 shows again top 10 customers.
      
      Reply
CP005: Introduction to Form Controls – an interview with Debra Dalgleish | Chandoo.org - Learn Microsoft Excel Online says:

April 19, 2014 at 12:29 pm

[…] Slicers – how to use them – case study […]

Reply
Roger says:

June 24, 2014 at 2:33 pm

Chandoo!
Just find out your website, I´ll follow your tutorials from now, very useful!
Great thanks from Brazil!!!

Reply
Priya Ranjan says:

July 9, 2014 at 12:04 pm

Very useful. Learned a new skill today. Thanks a ton!

Reply
Manav says:

August 4, 2014 at 4:57 pm

Hi Chandoo,

This is fantastic! It's going to really help me with some operational reports I develop regularly. Two questions I'm hoping you can answer for me:

1. How can I use one slicer to manipulate two different pivot charts that came from two different pivot tables?
2. If I have a slicer in an excel and share that with someone who is on older versions of Excel - what will it look like to them?

thanks!

Reply
Elisa says:

August 28, 2014 at 1:29 pm

Hello Chandoo!
I love the dashboards and have been able to make quite a few, my puzzle is when I am connecting the pivot charts to the slicers, I have to do each individual one and check every single slicer (usually I have about 12, so I end up having to check the 12 check boxes 12 times to connect everything) am I missing something? Is there an easier way to do this?

Thanks!
elisa

Reply
Hama says:

August 30, 2014 at 8:14 am

Hello Chandoo,

You make my life easier, am in love withe the slicers!

I greatly appreciate

Thanx

Hama

Reply
How I learned PowerPivot Blog Series – Creating Measures | The Insight Analyst says:

December 8, 2014 at 9:22 pm

[…] Slicers. Easy for me to do, but not as easy to explain how I did it. Fortunately, Chandoo has a Make Dynamic Dashboards using Pivot Tables & Slicers video and download that will do the job nicely. Suffice it to say it took me <3 minutes to put […]

Reply
@jitkumar56 says:

January 14, 2015 at 10:28 am

thank you very much..... 🙂

Reply
Jimbo says:

May 27, 2015 at 5:25 pm

You are a legend!! Thank you so much - very clear, very helpful indeed.

Reply
Shahid says:

July 3, 2015 at 6:27 am

nice player...

i like to play like chandoo sir.

i learn somthing about slicer by watching posts.

it was too difficult to watch and easy to prepare..

thank you boss.

God Bless You

Reply
MFAC93 says:

January 13, 2016 at 7:19 am

Hi,

I've built a dashboard on Excel 2010 using Pivot tables and slicers.

What I would like to do now is duplicate the dashboard on another tab, having it extract from another data source (format is identical to the 1st data source).

I'm extracting the same metrics, but each data sources measure different product lines.

Could anyone help me out?

Thanks in advance,
M

Reply
- Hui... says:
  
  January 14, 2016 at 2:51 am
  
  @M
  
  Can you please post the question at the Chandoo.org Forums
  http://forum.chandoo.org/
  
  Please attach a sample file for a quicker more targeted response
  
  Reply
Vicky says:

March 2, 2016 at 12:55 pm

Thank you so much. I learned so much about the slicer because of the video. Just got a quick question. Say I got 100+ Customer name bottons in one of the slicer, and it is time consuming to scroll up and down to find the one to select. Is there anyway I can set in the slicer setting that when I type "E", it automatically take the selectionto to where all the "E" starts? Thanks

Reply
TL says:

June 1, 2016 at 4:24 pm

Hi there,

This looks great - is there a way I can use it to compare vs budget, forecast? Is it just a case of renaming one of the field Comparison with the data being "Actual, Budget, Forecast"?

Thanks!

Reply
an irany says:

June 4, 2016 at 8:49 am

hello master!
please help me.
i am looking for many file example for Dashboard, but because my English is weak i couldnt fint it in hear.
please help me.
thankyou so much.

Reply
- Hui... says:
  
  June 5, 2016 at 3:00 pm
  
  @An
  
  Goto: http://chandoo.org/wp/welcome/
  Have a look under dashboards http://chandoo.org/wp/excel-dashboards/
  Also use the Search Box at the Top right of every page at Chandoo.org and search for Dashboard
  
  Reply
  - an irany says:
    
    June 6, 2016 at 7:52 pm
    
    thank you brother.
    i love all of you!
    
    Reply
Ikram Siddiqui says:

January 18, 2017 at 8:57 am

Dear Excel Guru,

Hope everything is fine with you?

Can you please help in this Logic, it is a thought only to increase my knowledge SIR?

Please note that I have been working in Excel file contains two times of our teammates who claims overtime an each calendar month

My excel file as like this :-

ROW 1 Days of Month
ROW 2 Date of Month

Cell -1 [Time IN(06:00Hrs)], cell -2 [Time OUT(15:30Hrs)] no break in our factory and anything after Eight hours assume as overtime as standard in all across.

Appreciate if you could help me in providing the best an Exclusive Excel formula to calculate each day overtime excluding staff eight hours regular duty and Friday consider as full day overtime.

Kindly help me at the earliest convenience.

awaiting for your expertise.............

Best Regards / Ikram Siddiqui

Reply
Lav Mishra says:

October 28, 2019 at 8:01 pm

Thank you for video , will you please provide pivot table with header and sub header like year main header and under that three sub header. How to make dashboard for that.

Reply
Praful Patil says:

December 23, 2021 at 7:55 am

Dear Sir,

How to seperate amount, mention in remarks.

Reply