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

2026 calendar and planner Excel template - how to use

Learn Excel

70 Responses to “10 Tips to Make Better and Boss-proof Excel Spreadsheets”

Yogesh Gupta says:

November 3, 2009 at 9:54 am

Proper print settings on each sheet helps your boss to print the reports quickly without hastling you after printing irrelevant stuff.

It is highly relevant that you print your reports once before circulating it to your boss or other people.

Knowing that what your boss actully look at in the entire report can be very usefull. You can build a good summary of what your boss wants and put that as separate tab in the form of dashbord report, so that your boss does not peep into rest of your work and start pocking you with irrelevant stuff.

You can also put that Dashboard into the email summary and not trouble your boss to open your workbook. This is ultimate boss proof tip and I have been using this for long time now.

Reply
Shuchi says:

November 3, 2009 at 10:19 am

Thank you Chandoo. Great checklist to follow before delivering an excel spreadsheet to someone else. Some points you mention are seemingly so simple that we might overlook them - like selecting cell#A1, but they make a difference to the impression the spreadsheet creates at the recipient's end.

Reply
Tom says:

November 3, 2009 at 10:22 am

Dear Chandoo,
Great tricks.

One trick I use (more and more) is to hide the sheet tabs and to hide the formulabar via the 'tools' 'options' and the 'view'-tab.

Another trick is to limiting the scrolling area to hide all columms (or rows) until the end of the sheet. Select the column, press CTRL+SHIFT+RIGHT, right-click on the column and hide (also possible via VBA).

I was wondering though if 'boss-proof' is related to 'excel-stupid-proof'?
Cheerio
Tom

Reply
- Arindam says:
  
  April 19, 2014 at 1:31 pm
  
  Just wondering if the hiding formula bar really works when a recipient opens it whose "view-show-Formula Bar" is still checked...
  
  Reply
  - T says:
    
    April 10, 2020 at 12:21 am
    
    It's saved to the sheet I believe.
    
    Reply
Martin says:

November 3, 2009 at 2:58 pm

Absolutely agree with this post !!!

on the past months, after reading this blog, PTS's and Debra's Contextures, one of the things I've beggining to do as a best practice is to create all my spreadsheets with 3 tabs: data, summary and control, and this last one generally xlveryhidden, and sometimes the data one hidden as well.

And this restrictions are also being applied as best practice, and with a lot of benefits as you well mentioned. Furthermore, if combined with dynamic named ranges, formulae is more readable to users, and the WOW effect is often achieved when the question "How did you do that?" arises.....

Keep on the good posts !!!

Rgds,

Martin

Reply
- Ruthie says:
  
  April 3, 2014 at 5:52 pm
  
  Would you mind sharing an example of this technique?
  
  Reply
Nilesh says:

November 3, 2009 at 3:11 pm

Is there a way to keep the data in a seperate file rather than the same excel. This way you could keep presentation and data separate. But not sure how you would link up the two excel files

Reply
- Pieter says:
  
  June 4, 2013 at 10:22 am
  
  Yes, there is a way but it is not prefered.
  I used this a coulple of times, (You need to code).
  
  mail me if you need assistance with some sort
  
  Reply
- T says:
  
  April 10, 2020 at 12:23 am
  
  It entirely is possible. The problem comes though, when you share the spreadsheet.
  
  If the recipient doesn't have both files, or access to both, things break when the values try to refresh.
  
  Reply
bazlina says:

November 3, 2009 at 5:09 pm

ey, why is the boss a she??

Reply
- applehead says:
  
  March 26, 2021 at 6:22 pm
  
  haha - welcome to the future. About time.
  
  Reply
Karthik says:

November 3, 2009 at 5:28 pm

Chandoo, one more trick that we could use with the help of VBA, RT click on the View code of the particular sheet, in the properties table set the Visible status to 2-xlveryhidden, this ensures the sheet name does not show up even when the BOSS tries to unhide the sheet from the sheet >> unhide option. Dont forget to password protect the VBA (available under tools >> VBAProject properties.

Reply
Eric Lind says:

November 3, 2009 at 5:36 pm

Very good tips, although I have to say Chandoo, that your cats probably need to be spayed or neutered if they behave like that. =)

Reply
Nimesh Thakkar says:

November 3, 2009 at 6:27 pm

Good to see all these tips on a single "sheet", and giving the name *boss proof*, and Dilbert was a great welcome 😀

Reply
Peter H says:

November 3, 2009 at 8:51 pm

The best way to "Boss Proof" (and "Self Proof"!!) a spreadsheet is to keep back ups. I use a macro that saves the last 3 significant versions of the spreadsheet all with a date stamp included in the file name.

Reply
Sébastien Labonne says:

November 3, 2009 at 9:07 pm

To quickly select cell A1 on all sheet, use CTRL-Page UP or CTRL-Page down to navigate between sheets and CTRL-Home to select cell A1 (if you have frozen pane, it will select the top left cell of the section below).

Reply
Jorge Camoes says:

November 3, 2009 at 10:37 pm

Great list. And I follow every single item... I also use a consistent background color for input cells in every report/dashboard. And I use a little VBA to identify the user and change the report accordingly (selecting the right market, for example).

Reply
Tim Buckingham says:

November 3, 2009 at 10:49 pm

Chandoo, Nice post. I like to use the hidden Paste Picture Link option. Keep the original report you want displayed on a hidden sheet and only show the boss the report picture. Also great to watch the confusion when boss trying to select cells is worth the effort!

Reply
m-b says:

November 4, 2009 at 10:17 am

I usually save as PDF if there's no interactivity in the report. That way nothing can go wrong 🙂

Reply
- Janet says:
  
  April 3, 2014 at 9:09 pm
  
  PDFs work a dream for me too and saves the boss's EA from telling me all the time that she can't print my work!!
  
  Reply
Chandoo says:

November 4, 2009 at 12:26 pm

@All.. thanks a ton for sharing your ideas. I am thinking of writing a part 2 of this post explaining some of your ideas in detail.

@Bazlina ... I will make sure the boss is a HE in the next post 🙂

Reply
Hui... says:

November 5, 2009 at 12:33 am

"10 Tips to Make Better and Boss-proof Excel Spreadsheets"...
Unless of course your Boss reads PHD !

Reply
Debra McLaren says:

November 5, 2009 at 7:21 am

Great article with one glaring error.

If (like me) the majority of your spreadsheet errors are *caused* by cats, adding more cats is just going to increase the problem.

Reply
Chandoo says:

November 5, 2009 at 8:52 am

@Hui you always have a boss, even if you are boss. If you dont have a boss, then may be a cat or even a dog.

@Debra: hmm... Are you sure the cats are not after the mouse? Go learn some keyboard shortcuts.. now 😛

Reply
Paul Grenier says:

November 6, 2009 at 5:05 am

Great Web Site. I've done almost all the above in trying to build my application and it's taken me hours and hours reading my "dummies " book. Thank you for all this information.
Is there a formula I can use that will automatically return to "A1" cell should an associate use the 10 page spreadsheet I have?
Is there a way to set an expiration date on my workbook so that beynd that date no one will get beyond the cover page?

Reply
- Russell Cooney says:
  
  August 10, 2016 at 5:24 pm
  
  Paul, in all my "user facing" workbooks (those that I distribute) I create a named range called "Home" on the worksheet(s) that are most likely to be used. Then I write a little VBA that selects the Home range whenever that worksheet is activated or on other triggers depending on the context of the sheet. This is more appropriate for the dashboard tabs or summary tabs my job requires.
  
  But I usually set this functionality up early on in the design process so I can take advantage of it as well. I will sometimes assign a keystroke to the GoHome macro.
  
  Reply
JimmyG says:

November 8, 2009 at 5:03 pm

I'm in the marketing department (aka the picture department) and have to say that the macros/Excel sheets from our controlling department are the worst! They come to me to sort out the mess!!

Reply
Chandoo says:

November 9, 2009 at 9:02 am

@Peter: You can try creating a table of contents and then place it on each and every sheet so that user can jump to anywhere from anywhere. Here is a tutorial to help you get started.

Also, You can prevent users from accessing the workbook after a certain date using macros. But users can certainly by pass it by disallowing macros on that workbook.

@Jimmy: Wow... (just kidding) Welcome 🙂

Reply
Ryan says:

November 17, 2009 at 4:21 pm

I was recently given a spreadsheet to improve upon.
One of the "boss-proof" actions that the previous author had used was to use data validation instead of protecting the sheet to ward off people changing formulas.
After entering a formula or value into a cell, use data validation to only allow, in this spreadsheet, whole numbers between 9999999 to 99999999.
It's a bit of a pain to actually correct stuff instead of just unprotecting a sheet, but for those that know how to unprotect a sheet, it's a definite way to keep them from fooling with formulas.

Reply
Raja Srinivas says:

December 6, 2009 at 6:10 pm

Puchu,
We would love to see "Print" in your links section.
It helps us taking prints as neat as your posts 🙂

Reply
Paul Grenier says:

December 31, 2009 at 5:12 am

Chandoo,
I've emailed you a couple of times looking for avenues I need to try to put my workbook on the Internet.
I notice you use PremiumThemes for your Web Site...You must feel good about their service. Do you think PremiumThemes might be an option for me?
Paul

Reply
Anurag G says:

January 21, 2010 at 3:55 pm

Instead of :
Now Right click and select “Hide” option.

Shortcut can be used : Ctrl+0 (to hide)..

Reply
danial says:

March 8, 2010 at 4:41 pm

sir i wanted to know,how to hide cells or tab without hiding rows and columns? PLZ TELL ME

Reply
JunDR says:

March 15, 2010 at 10:28 am

Hi Chandoo!

Great tips! Im researching on an excel project now that you can create to "lighten" the size without sacrificing the data inside..
We usually encounter problems with the data, excel file is shared, in a network folder.. and there are 11 people that enters their own productivity in each tab.. however, there comes a time (uncertain) where some of the data they enter either gets deleted or changes value.. could this be a file size problem? are there other ways to create this file that will decrease data inconsistencies?

thanks!

Reply
Exclude Hidden Rows from Totals [How to?] | Chandoo.org - Learn Microsoft Excel Online says:

May 11, 2010 at 8:31 am

[...] Hide un-necessary rows to create clean looking workbooks (and 9 more tips) [...]

Reply
12 Rules for Making Better Spreadsheets | Chandoo.org - Learn Microsoft Excel Online says:

August 5, 2010 at 9:19 am

[...] Presentation format: all spreadsheets, should be designed so that it is easy to follow the process flow and result. Almost every spreadsheet should be presentable and understandable to senior management without additional formatting or explanation. (tips: how to design boss-proof excel sheets) [...]

Reply
Excel Links – Live from Bangkok Edition | Chandoo.org - Learn Microsoft Excel Online says:

January 30, 2012 at 8:57 am

[...] on Excel formatting here: How to make better excel sheets, Formatting [...]

Reply
Excel Links – Live from Bangkok Edition | Microsoft Excel Training Courses says:

January 30, 2012 at 10:15 am

[...] on Excel formatting here: How to make better excel sheets, Formatting [...]

Reply
How would you customize Excel after installing? [poll] | Chandoo.org - Learn Microsoft Excel Online says:

February 10, 2012 at 8:35 am

[...] 10 Tips to make better & boss-proof spreadsheets in Excel [...]

Reply
Nicholas says:

April 4, 2012 at 7:35 am

You will find another quick and easy technique here:

http://www.onsitetrainingcourses.com.au/main/page_blog_hiding_most_excel_rows_and_columns.html

Reply
Show only few rows & columns in Excel [Quick tip] | Chandoo.org - Learn Microsoft Excel Online says:

July 24, 2012 at 9:48 am

[...] tips: Learn how to make better Excel sheets Spread some love,It makes you awesome! [...]

Reply
Janet says:

July 25, 2012 at 5:50 am

Save what you want the boss to see as a PDF. Absolutely foolproof and no cats hurt in the process.

Reply
malen says:

October 7, 2012 at 2:49 am

I really enjoyed allot of the tips on here, especially the one on comments on cells. That will come in handy on allot of our projects. I would also like to share on on my little tricks. I am constantly working on several different reports with several different systems and in doing so I am constantly running in problems and my way out of them is simply calling <a href"http://www.reportingguru.com/"> Reporting Guru </a> and telling exactly what I'm going through and they can tell me exactly how to get out.

Reply
The_Doctor says:

December 11, 2012 at 6:28 pm

One of the things I've found to boss proof my worksheets are a few simple VBA scripts to automatically protect the workbook/worksheets, and direct them to the "Quick Look" dashboard page, I hide all of the raw data sheets before saving. The script looks like this:
Private Sub Workbook_Open()

    Sheets("Summary").Protect Password:="password"
    Sheets("Labor Cost by Site").Protect Password:="password", AllowUsingPivotTables: =true
    Sheets("Labor Cost by month").Protect Password:="password"
    Sheets("Quick Look").Protect Password:="password"
    Sheets("Quick look").Activate
    ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False
End Sub

I also have a pivot that contains labor cost data which cannot be refreshed while the worksheet is locked.

Private Sub Worksheet_Activate()
    Sheets("labor cost by site").Unprotect Password = "password"
        Set pvttable = Worksheets("labor cost by site").Range("a1").PivotTable
            pvttable.RefreshTable
    Sheets("labor cost by site").Protect Password = "password", AllowUsingPivotTables:=True
End Sub

Reply
lol says:

January 10, 2013 at 8:29 am

OPPAN GANGAM STYLE!

Reply
ben says:

January 28, 2013 at 10:57 am

good

Reply
Rahul thial says:

April 26, 2013 at 1:18 pm

Your post are always with something creative , thanks for sharing this information , your post are worth reading and implementing 🙂 great job

Reply
apt says:

August 13, 2013 at 9:49 pm

Hi,

I will try to learn every point slowly !

Shokran Chandoo.

Reply
SpreadSheetNinja says:

October 30, 2013 at 9:51 am

Best boss Proofing of sheets is useing indirect(address 😛 this prevents most smartass bossess from doing any actual changes cus the formula will be long and hard to understand for any bystanders..

Also putting the actual calculations on a different sheet can make a sheet bulletproof from bosses.. especialy if you put them in the Very hidden so when the boss learns how to unhide sheets he wont simply find them.

One thing iv also learned is that most bosses is scared of macros that gives "virus" warnings before beeing run 😛 That include the default warning from Excel...

Long formulas or work arounds is best way to go.

Reply
Novice says:

January 29, 2014 at 9:04 pm

What's the best way to amalgamate two existing excel spreadsheets into one?

Two teams use the same format spreadsheets with individual data split into calendar months and I want to make them one without manually entering the data.

Reply
- Girish R Kini says:
  
  March 24, 2015 at 5:25 am
  
  Alt + D + D + N
  
  Write a query and viola, Two sheets into one.
  
  Reply
Isaac says:

March 6, 2014 at 4:21 pm

Changing the properties of the file to read-only . (While the file is closed, right click on the file and check the read-only box.)

This allows my boss(es) to access the file -- even change it -- without being able to save their changes. If a boss likes his 'new' version, he can save it with a different file name.

But now -- how to prevent the boss from deleting the file altogether? Or deleting the whole network?

Reply
- pieter says:
  
  March 6, 2014 at 10:22 pm
  
  Hey man.
  Think you can go as easy as to make a shortcut that links to your read only document. Then the boss wont know of the root document. He can figure it out but lets face it. He is a boss and 70% if them wont know squat
  
  Reply
Matt says:

September 5, 2014 at 7:25 pm

Instead of "Hiding" rows & columns, I find "Grouping" works best as its very easy to quickly see if a worksheet has hidden rows/columns. Sometimes hiding a random row/column is not easily noticed and can create issues.

Reply
samantha says:

October 25, 2014 at 4:45 pm

I have one xl sheet with different dates in many columns and one raw's. I want to send this data to another xl sheets for each date. if somebody can help me will be great.

Reply
- Tom says:
  
  October 27, 2014 at 8:37 am
  
  Dear Samantha,
  
  Check out the website of Ron de Bruin. He has a great set of macro's and free add-in that can help you with this issue.
  http://rondebruin.nl/win/s3/win006.htm
  Tom
  
  Reply
Mariateresa says:

January 16, 2015 at 8:43 pm

Hello, I have just found out that I made a mistake in my spreadsheet: I had a column of negative numbers, but one of them was positive (while it should have been negative). Is there a formula/system to avoid this?

Thanks.

Mariateresa

Reply
- rajan pradeep says:
  
  May 31, 2016 at 5:55 pm
  
  Yes, data validation. Values you denote would be between -1 and -999,999,999.
  
  Reply
Girish R Kini says:

March 24, 2015 at 5:23 am

Hi,

Hiding any worksheet can be unhidden and messed around easily. I change the visibility in visual basic from -xlSheetVisible to -xlSheetVeryHidden. By this, even if you right click on sheets, you will be unable to find the hidden sheets.

Cool? I think so...

Reply
sandeep says:

September 9, 2015 at 3:44 am

Very informative, Thanks

Reply
Cedric says:

March 2, 2016 at 9:32 pm

Is there a way to lock cells in an already protected worksheet.
(Thus the entire worksheet is protected, then the entire office can open it as read only but only a few users have the password to edit the file)
I would like an additional password or prompt box so these few users don't accidentally change formulas.

Reply
find a new career quickly says:

July 5, 2016 at 7:28 pm

Itss such as you learn my thoughts! You appear too understand
a lot abnout this, like you wrote thee e-book in it
or something. I fel that you just could do with some percent to presseure the message house a little bit,
but insatead off that, this iis wondeerful blog.
An excellent read. I'll definitely be back.

Reply
free movie says:

September 6, 2016 at 2:08 pm

It is in reality a nice and helpful piece of info.
I am happy that you just shared this useful info with
us. Please keep us up to date like this. Thank
you for sharing.

Reply
GraH says:

October 4, 2016 at 10:24 am

I laughed out loud reading the 2nd solution about moving to marketing department and making ppts.
I've been using "technical" sheets for a long time already and depending on the audience it is hidden or not. I'm currently in my NO VBA mindset, so the very hidden option is no longer. Using sheets names like: TechnicalCodes; ExplicitVariables;SetUp; HeavyCalc seem to work to my experience as they send along a message "Don' t you mess-up here, you fool!". A "Read This" section or sheet however does not work!
Reading stuff on this site has helped me develop a good habit of using colors and themes to assist the end user in being well-behaved. In my book the best advise here, because it is about the user experience and not only about protection your own work.
For dashboards I get rid of tabs and scroll bars. Besides 2 exceptions, I need to come across a manager who can turn them on again without my help.
Seems that I forgot about protecting cells, sheets and workbooks altogether. Damn!

Reply
Mark H says:

December 9, 2016 at 5:08 am

Thanks for the informative article Chandoo, I've been struggling with Excel lately. It's a powerful tool, but hard to learn for me.

Reply
Neeraj Singh says:

June 15, 2017 at 6:09 am

Thanks Chandoo for sharing these excel sheet tips it helps me a lot to understand excel more.

Reply
Bryan says:

June 26, 2018 at 3:59 am

Nice roundup, Chandoo! Here's one more I thought would be relevant:

For Excel 2013+, you can hide the ribbon, as shown in this animated gif: https://gridmaster.io/tips/hide-ribbon-excel-space

This will simplify the interface, making it less likely for people to accidentally make changes. 🙂

Reply
KUMAR says:

May 7, 2020 at 11:49 am

THANK YOU SIR

Reply
constantine la says:

December 2, 2022 at 4:27 am

I'm better at Power BI thanks to you!

Reply