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

Excel Challenges

59 Responses to “Robust Dynamic (Cascading) Dropdowns Without VBA”

Gary says:

February 25, 2014 at 2:05 pm

downloaded workbook doesn't work for me. it's always missing the first selection.
so, If I choose fruit, apples is not in the dropdown. if I choose vegetables, beets is not in the dropdown

Reply
roberto mensa says:

February 25, 2014 at 2:55 pm

@Gary, you are right, I try to anticipate the reply of Jeff.
Here’s the right formula, enter into the Name Manager while cell B8 was selected:

=IF(ISBLANK(C8),IF(DataEntry[#Headers] B:B = DataEntry[[#Headers],[Main Category]],OFFSET(ValidationLists[[#All],[Main Categories]],1,,COUNTA(ValidationLists[Meat])),OFFSET(ValidationLists,,MATCH(A8, ValidationLists[#Headers],0)-1,COUNTA(OFFSET(ValidationLists,,MATCH(A8, ValidationLists[#Headers],0)-1,,1))-1,1)))

Reply
roberto mensa says:

February 25, 2014 at 3:06 pm

UFFF ... i'm sorry ... forget my previous comment 🙁
Here’s the right formula, enter into the Name Manager while cell B8 was selected:
=IF(ISBLANK(C8),IF(DataEntry[#Headers] B:B = DataEntry[[#Headers],[Main Category]],OFFSET(ValidationLists[[#All],[Main Categories]],1,,COUNTA(ValidationLists[Meat])),OFFSET(ValidationLists,,MATCH(A8, ValidationLists[#Headers],0)-1,COUNTA(OFFSET(ValidationLists,,MATCH(A8, ValidationLists[#Headers],0)-1,,1)),1)))

the errors were due to the fact that ValidationLists does not include the header (as I had initially interpreted)

Reply
Elias says:

February 25, 2014 at 7:32 pm

Great formula as an always Roberto. However, I think it still need some VBA to clean the subcategory selection if the user delete the previous category.

Regards

Reply
- roberto mensa says:
  
  February 25, 2014 at 10:44 pm
  
  Ciao Elias! the formula was born from a discussion with Jeff, everyone added something then he wrote this post and he explained the formula ... he gave all the credit to me ... but the merit is more his ... I've only made a mess with these Excel Tables (I'm sorry) 🙂
  anyway thanks
  
  Reply
  - Jeff Weir says:
    
    February 25, 2014 at 10:54 pm
    
    This post was borne out of a whole bunch of misunderstandings! When we'd all got to the bottom of them, something beautiful emerged from the dust. 😉
    
    Reply
Jeff Weir says:

February 25, 2014 at 9:03 pm

Gary: Thanks for the heads up. Have fixed post and sample file.

Elias: No VBA necessary as users CANNOT delete upstream categories without FIRST manually cleaning out downstream categories. Give the sample file a spin.

Reply
- Elias says:
  
  February 25, 2014 at 11:39 pm
  
  Hi Jeff,
  
  Open the file->go to B8 and press the delete key. Do I missing something?
  Also, the new file’s link is giving and error.
  
  Regards
  
  Reply
  - Jeff Weir says:
    
    February 26, 2014 at 5:10 am
    
    No, it's me that's missing something...I misunderstood what you were saying. So yes, you'd need VBA to do that, along the lines per my previous post. But this is still a great non-VBA solution, apart from that.
    
    Link fixed.
    
    Cheer Elias.
    
    Reply
Kuldeep J says:

February 26, 2014 at 4:00 am

Great....i was looking something like this in past but end up with only VBA solutions which i really did not liked them because if anyone unknowingly chnage the upper LOV, All the sub selection get vanished and if the person do not know what those LOV, He/She can only hit his head.

Reply
LeonK says:

February 26, 2014 at 11:31 am

What a fantastic solution. I'm currently replacing VBA routines with non-VBA ones and this formula has become a major part of my re-modelling. Thank you so much for debating, creating and sharing this formula.

LeonK

Reply
Jeff Weir says:

February 26, 2014 at 9:45 pm

Kuldeep and LeonK: Thanks for you kind comments. Without comments like yours, I tend to think that noone finds this stuff useful.

Reply
Robust Dynamic (Cascading) Dropdowns Without VBA | Chandoo.org – Learn Microsoft Excel Online | CareWare says:

February 27, 2014 at 3:39 am

[…] http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/?utm_source=feedburn… […]

Reply
Oxidised says:

February 27, 2014 at 4:35 am

Great solution! shame I needed it 6 months ago, but I'm sure i'll find another use for it soon!

Certainly do find it useful, keep the awesome posts coming!

Reply
Doug Glancy says:

February 27, 2014 at 4:46 am

Nice work, Jeff and Roberto. The VBA-free cascading data validation is a worthy goal.

At my previous job I regularly sent out Excel surveys to dozens of recipients, and of course couldn't hazard the maintenance nightmare of VBA. I came up with a version of dependent dropdowns that wasn't as self-correcting as this, but uses Conditional Formatting to alert the user, and ultimately, the analyst, that something is amiss. If anybody's interested it's at http://yoursumbuddy.com/user-friendly-survey-without-vba/.

Reply
- Jeff Weir says:
  
  February 27, 2014 at 5:03 am
  
  Hey, that's cool, Doug. Will add link to that in the main article.
  
  Reply
  - Doug Glancy says:
    
    February 27, 2014 at 5:23 am
    
    Thanks Jeff!
    
    Reply
Excel Roundup 20140303 | Contextures Blog says:

March 3, 2014 at 5:02 am

[…] Weir explains Robert Mensa’s technique for creating robust dynamic drop downs, without VBA. Just remember, the best we can do is build things that are idiot resistant, not idiot […]

Reply
Pablo says:

March 10, 2014 at 7:56 pm

Thanks Jeff and Roberto, this is exactly what I was looking for. The timing it's like a miracle! 🙂

I noticed that the validation is not consistent, in some cells I can type anything I want, but in others the validation rule works. I just copied the cell from the one that was working to the rest and now all are fine. I hope I didn't mess up anything by doing that.
Thanks again,
Pablo

Reply
Why you should close down Excel completely | Chandoo.org - Learn Microsoft Excel Online says:

March 24, 2014 at 9:05 am

[…] Howdy folk. Jeff Weir here. You might remember me from shows such as Handle volatile functions like they are dynamite, Did Jeff just Chart, and Robust Dynamic (Cascading) Dropdowns Without VBA. […]

Reply
Leonard says:

April 8, 2014 at 2:29 pm

Guys, does this work with subcategories beyond 3, per the example? I have a flow chart decision tree with 6 subcategories. My customers are basic users who don't want to read my guidelines or decision tree. I thought dynamic dependent drop downs the best option for this situation. However, I can't seem to find anything on the web that shows beyond 3 categories. If anyone can help show how it could work with the learning/example that Jeff published above, I would be grateful...or if there are better options then drop downs...

Reply
- Jeff Weir says:
  
  April 9, 2014 at 8:41 am
  
  Leonard - yes it can. As per the original article: Like Roger’s approach, Roberto’s approach can handle any number of cascading levels, provided all the category names are unique. All you need to do is simply add the new subcategories to the right hand side of the validations table.
  
  Reply
  - Leonard says:
    
    April 11, 2014 at 4:32 am
    
    Thanks Jeff!
    
    Even though I am basic beginner, I managed to emulate and then modify. Maybe it say somewhere, but I discovered that if your last column in the validation table isn't filled in as far as the 1st column, then you won't get the full first drop down list. I basically created an extra end column that I filled in with bogus numbers so it would work.
    
    Thanks again!
    
    Reply
CP005: Introduction to Form Controls – an interview with Debra Dalgleish | Chandoo.org - Learn Microsoft Excel Online says:

April 19, 2014 at 9:07 am

[…] Dependent Data Validation (Cascading Data Validation) […]

Reply
Enzo says:

March 17, 2015 at 6:10 pm

I just implemented this mechanism and it works perfectly. When I close the file and try to open it again, it just crashes Excel, and the file become useless.
Tested in two different machines and got the same result.
Anyone with the same problem?
Tks
Enzo

Reply
- Jeff Weir says:
  
  March 17, 2015 at 7:43 pm
  
  Hi Enzo. Not sure what's going on there. I'll flick you an email, in the case that you want to send me the file to look at.
  
  Reply
Gigi says:

September 30, 2015 at 3:52 pm

Hey,

I did this equation on a template and it worked out well. I set up three different sections with no problem. A few months later when I tried to add two more sections to a different tab, it will not work. Well, at first it worked but then out of the blue the formula is not working. But only for that section. This is what I am typing in with the proper adjustments for the cell I am entering. Also, not sure if this is a part of the problem, I am using macros to dynamically rebuild the ValidationListsD table in the formula.

=IF(ISBLANK('Budget Details'!H11),IF(DataEntryD1[#Headers] 'Budget Details'!G:G = DataEntryD1[[#Headers],[SC Account Name]],OFFSET(ValidationListsD[[#All],[SC Account Name]],1,,COUNTA(ValidationListsD[SC Account Name])),OFFSET(ValidationListsD,,MATCH('Budget Details'!F11,ValidationListsD[#Headers],0)-1,COUNTA(OFFSET(ValidationListsD,,MATCH(‘Budget Details’!F11,ValidationListsD[#Headers],0)-1,,1)),1)))

Reply
- Gigi says:
  
  September 30, 2015 at 3:53 pm
  
  Oh and my starting cell for this one is G11.
  
  Reply
Simon Williams says:

October 8, 2015 at 2:30 pm

Hi -
Thanks for the example - something I could never have done myself 🙂

Reply
Walter Rizzoli says:

January 13, 2016 at 4:42 pm

Fine article and excellent solution for cascading validation lists. Once I found it I couldn't resist trying it. After a while, then, i bumped into an apparently unsolvable problem: when I try copying the sheet which contains DataEntry table, the validation list wouldn't work. I have the ValidationLists in a different sheet.
Of course, copying the worksheet forces excel to copy the ValName range which remains with the same name but scoped to the new sheet instead of the workbook. The DataEntry table becomes DataEntry2 instead. I thought the problem was that ValName scoped to workbook in the first place, so that having 2 different range named the same, one being scoped to the workbook was the issue. So I decided to change the scope of ValName to the worksheet before copying. The result was the same: validation lists do not appear on the new sheet, while still working fine on the original one.
Is there anything I am missing or is just that the operation doesn't have an easy solution (meaning I need to set up the whole thing everytime a copy the sheet)?

Reply
- Hui... says:
  
  January 14, 2016 at 2:53 am
  
  @Walter
  
  Your correct
  
  Named Formula are sheet specific and copying sheets can create these issues
  
  You can setup a new Named Formula and relink it on the new worksheet eg: ValName2
  
  Reply
  - Walter says:
    
    January 14, 2016 at 2:02 pm
    
    Thanks. I thought so.
    
    Reply
Cmt says:

May 13, 2016 at 7:26 am

Hi Guys! try this..
Let us assume you have the main dropdown in cell A1 and its dependent dropdown in cell B1. Also, let us assume that the name of your main validation list is "list1", which means that under validation criteria for cell A1, you Allow List and in source you type"=list1". So, instead of "=list1", try this:
Under validation criteria for cell A1, Allow - List. In Source, type the following formula...
=IF(B1"","",list1)
THAT IS IT! So long as cell B1 is not empty you will not be able to input any value in cell A1.

Reply
- Jeff Weir says:
  
  May 13, 2016 at 10:43 am
  
  Cmt...that works fine for two level dropdowns, where you have set up named ranges ahead of time. But my approach can handle any number of cascading levels (provided all the category names are unique) without having to set up individual named ranges. All you need to do is simply add subcategories to the right hand side of the validations table (Table1). There's more on this in the original article I link to at the top.
  
  Reply
Dzordzan says:

June 20, 2016 at 7:57 am

That is AWESOME!!! I am not VBA yet but needed to impress by boss 😀 AND I DID!!! I did impress myself though as well lol! Biggest thanks to you, Chandoo!!! You rock!

Reply
Discoblade says:

July 14, 2016 at 9:45 am

This is exactly what I was looking for, except for one issue, I would likre my data input to be on a different sheet to the source table, and I can't seem to make it work...help?

Reply
Mangirish Nadkarni says:

December 14, 2016 at 11:31 am

I have tried the formula. My data input is on Opportunity Data sheet from K3:M9999 and my Validation Lists are on DB Sheet from B37:W55. I have "First_Header", "ProductData" and "ValidationLists" in the Name Manager. I tried the formula as: =IF(ISBLANK(Opportunity_Data!L3),IF(ProductData[#Headers] DB!B:B=ProductData[[#Headers],[Customer Billing Plant]],OFFSET(ValidationLists[[#All],[Customer Billing Plant]],1,,COUNTA(ValidationLists[Customer Billing Plant])),OFFSET(ValidationLists,0,MATCH(DB!A36, ValidationLists[#Headers],0)-1,COUNTA(OFFSET(ValidationLists,,MATCH(Sheet1!A36, ValidationLists[#Headers],0)-1,,1)),1))). I am continuously getting the error message regarding incorrect formula with the ValidationLists[[#All] highlighted. Can you please help me?

Reply
Prajay Kumar says:

July 5, 2017 at 5:03 pm

One more simplest way is to use INDIRECT function.

Reply
Jeff Weir says:

July 5, 2017 at 7:38 pm

Yes, you can use INDIRECT. But you have to set up a seperate named range for every possible combination, and you can't use spaces (meaning in the example above you would have to use Ozark_Beauty instead of Ozark Beauty). So if you have more than a couple of levels of cascading dropdowns, the INDIRECT approach soon becomes unwieldy.

Whereas my approach can handle any number of cascading levels (provided all the category names are unique) without having to set up individual named ranges. All you need to do is simply add subcategories to the right hand side of the validations table (Table1). There's more on this in the original article I link to at the top.

Reply
Tracy Ormand says:

September 22, 2017 at 1:59 pm

I'm working on adapting this to my form. I'm just getting into using VBA and advanced formulas, literally like three days ago! I was wondering if this will work with my project. I have a very simple form that I created using one downstream dropdown to capture a group and subgroup. I only have the one table where my source data is pulled from. There are no headers in my data entry (form) area either. Just want to make sure its possible. I'm hoping to understand this better once my Vyvanse kicks in!

Kind regards

Reply
- Jeff Weir says:
  
  September 22, 2017 at 10:11 pm
  
  Yes, this should work. Setting it up can be a bit complicated, so if you get stuck, perhaps the best way to proceed is to download the example, and change it to suit your needs.
  
  Reply
Jim says:

July 6, 2018 at 8:07 am

Hi Chandoo,
Your formulas have worked wonders for me, however after finally being able to apply this "Robust-dynamic-cascading-dropdowns-without-vba", it seems I could not get the dropdowns to work if I duplicate the worksheet and rename. When I pull up the Name Manager, it seems the originally created table and Name Ranges are scoped to Workbook, and new Names exist scoped to specific Worksheet.

Not sure if I make any sense, but in simple terms, I duplicated sheet 1, rename to sheet 2, click on the same cell with data validation, no drop-down appears.

Any idea?

Reply
- Jeff Weir says:
  
  July 6, 2018 at 9:34 am
  
  Hi Jim. I'm the author of this guest post, not Chandoo. You'll simply have to create new names that don't conflict with the old.
  
  Reply
Jim says:

July 14, 2018 at 9:40 am

Hi Jeff, sorry I didn’t notice... anyway, thanks for the reply, just realised the sheet names and formulas will then have to match accordingly, it works fantastic now! A thousand sincere thanks!!! 😉

Reply
Michael says:

July 24, 2018 at 8:29 pm

Hi Jeff,

Thanks for this post, this was very helpful. I am trying to tweak this formula to accommodate a similar dynamic drop-down structure, but in a different format. My DataEntry table must be formatted a certain way to meet upload requirements.

In my case on the DataEntry table, my "Main Category' drop downs are the table headers (So picture Fruits, Vegetables, and Other Stuff, in B7:D7) and my dependent sub-category drop-downs (only using 1) are in the rows directly underneath (ex. Apples, Beets, Bread as selections in B8:D11)

My Validation table has my Main Category selections as column headers with all the sub-category values listed underneath. (I hadnt considered an Initial List since I only have 1 dependent drop-down list)

I can change the validation table in any way I want but the DataEntry table must stay in this format. Is there a way to rearrange the formula to accommodate this while maintaining functionality?

Thanks!
Michael

Reply
- Jeff Weir says:
  
  July 25, 2018 at 11:06 am
  
  I'm afraid not, Michael. You'll have to research for a different approach.
  
  Reply
- Chandoo says:
  
  July 25, 2018 at 10:14 pm
  
  In this case, you can use the example demonstrated here:
  
  https://chandoo.org/wp/cascading-drop-down/
  
  Reply
  - Michael says:
    
    July 26, 2018 at 2:59 pm
    
    Hi Chandoo,
    
    Thanks for this, unfortunately I cannot use this as I will need to copy the validation over to multiple columns to the right, and also have the dependent drop down validation copied down to multiple rows. So I wont be able to have the IFERROR formula that displays the red X next to my drop-downs, nor can I put it underneath. Similarly I will need to format my validation values as a table so I can add new items to each drop-down as I please.
    
    Based on this criteria, is VBA my only option here?
    
    Reply
Leonardo says:

January 13, 2019 at 2:33 pm

Hi Chandoo,

I use Excel for Mac 2016, so when I try to paste my version of the suggested formula the range of cells box truncates it to 255 characters max (my formula has 455 characters).

Is there a way to get around this situation?

I tried splitting it up in two and using CONCATENATE to combine it together but it didn't work. Maybe I'm doing it or there's a more clever way to circumvent this limitation...

Reply
Barry says:

February 10, 2019 at 11:21 pm

Hi! This solution is perfect for me! I am trying to use Cascading Drop-downs on a protected sheet and don't want to get into VBA to get around that. I would only need to adapt the Headings and Category names.

I can do this easily and it works perfectly on the worksheet I downloaded from you.

However - I think I am having the same trouble as Leonardo above - When I try to copy the sheet into my existing workbook, it fails. It says I am missing a parenthesis. I am also using Excel for Mac 2016. But I don't understand why it works on the worksheet I downloaded, but not when trying to integrate it into a new workbook. Thanks for any help!

Reply
Barry says:

February 12, 2019 at 12:56 am

Actually I figured it out - if I "copied" your worksheet into mine, the problems occurred. If I "moved" the worksheet into mine, it worked perfectly, thanks again for providing this innovative solution, now I can use these lists on a protected workbook with no VBA to work around!

Reply
Pierre says:

October 21, 2019 at 1:47 pm

Hi everyone,
First of all, thank you for this article, it is really insightful.
I have a question regarding this topic tho.
My question is "How many levels of dynamic (cascading) dropdowns can you construct ?".
In order terms, would it be possible to add a 4th level with illegal characters in the example above ?
I tried to research that really hard but could not find anything. Any help is welcome.
Thank you,
Pierre

Reply
Thomas says:

November 12, 2019 at 11:54 am

Hi, I am looking for a formula where the cell content changes from selecting the options in a dropdown. For example: when I select week as an option in the dropdown then my dashboard will give me report week wise information. From Cell A5 to A15 the weeks should be reflecting and based on that weeks the other column cells should give the info. Likewise when I select the option as Month then the same cells A5 to A15 should show the months and based on the month the other column cells will give the report. And other this is, only the available week or month should reflect in the A5 to A15 Cells from the dump.

Reply
- Thomas says:
  
  November 12, 2019 at 11:56 am
  
  Kindly request to help me in this as I have stuck with the dashboard which I need to complete and share with clients.
  
  Reply
  - Chandoo says:
    
    November 13, 2019 at 3:33 am
    
    Hi Thomas... Thanks for your comments. Please refer to automatic month rollover page for information on how to calculate month dates based on starting month. You can tweak this logic with an IF formula so that when "Weeks" is selected in the drop down, you will have weeks in A5:A15 and months otherwise.
    
    Automatic Rolling Months in Excel [Formulas]
    
    Reply
    - Thomas says:
      
      November 13, 2019 at 5:39 am
      
      Thank you so much Chandoo. It's helpful.
      
      Reply
    - Thomas says:
      
      November 13, 2019 at 6:08 am
      
      Hi Chandoo, I have attached the dashboard via email. Please check.
      
      Reply
Miguel says:

October 12, 2020 at 4:43 pm

I need help!
I have 2 columns one with date which can repeat and another with date that vary along the day as multiple tests are executed during the day. I need a cascading dropdown, than upon selection of the date, user's can choose the time at which the report was executed to look at the data.
my 2 tables look like this:
Date Hour
20/10/2020 13:45
20/10/2020 15:34
07/10/2020 20:00
09/10/2020 20:45
09/10/2020 21:45

How would the formulas look for a scenario where I don't have and categories listed on the top row?

Reply
- Chandoo says:
  
  October 13, 2020 at 9:01 pm
  
  You may want to refer to my latest post "two level dropdowns" for another way dealing with this problem. link here - Two-level Data Validation [Excel Trick]
  
  Reply