Monte Carlo Simulation in Excel With RAND and a Data Table

A Monte Carlo simulation in Excel runs the same model hundreds or thousands of times with random inputs, then shows you the spread of results. Instead of one forecast ("profit will be 240"), you get a range and its chances ("profit is negative in about 37% of trials"). You build it with RAND, a one-column Data Table and a few summary formulas. This guide is for analysts, students and planners who want to show risk, not just a single number.

What a Monte Carlo simulation does#

A Monte Carlo simulation answers "what could happen, and how likely is it?" It works in three steps:

1. Model: build a normal formula model, such as profit = units × margin − fixed costs.
2. Randomise: replace each uncertain input with a random value drawn from a realistic range.
3. Repeat and summarise: recalculate the model many times, store each result, then look at the average, the extremes and the share of bad outcomes.

Each recalculation is called a trial. One trial is a guess; a thousand trials show a pattern.

Step 1: Build the model with fixed inputs#

Start with a model that works with ordinary numbers. A small business sells about 530 units a month, earns 8 per unit and has fixed costs of 4,000:

With 530 units, profit in B7 is 530 × 8 − 4,000 = 240. The std dev (standard deviation) in B2 says how far monthly sales usually stray from the average. The standard deviation guide shows how to calculate it from past data.

Step 2: Make the inputs random with RAND#

Replace the fixed 530 in B6 with a random value. Excel has three useful random functions:

Sales usually cluster around an average, so a normal distribution fits. In B6, type:

=ROUND(NORM.INV(RAND(),B1,B2),0)

RAND gives a random probability, and NORM.INV turns it into the matching number of units. Most results land between about 440 and 620, and only rarely outside that. ROUND keeps the units whole. Press F9 (Mac: ⌘=) and B6 and B7 change each time. That's one new trial per press. The guide to randomising a list explains why RAND changes on every recalculation.

Step 3: Run 1,000 trials with a Data Table#

A one-column Data Table recalculates the whole model once for every row, and stores each result. The trick is to point it at an empty input cell, so the only thing that changes between rows is the random numbers.

1. In D2:D1001, enter the trial numbers 1 to 1,000. In Excel 2021 or Microsoft 365, type =SEQUENCE(1000) in D2. In older versions, type 1 and use Home > Fill > Series.
2. In E1, the cell above the first result, link to the output: =B7.
3. Select D1:E1001.
4. Go to Data > What-If Analysis > Data Table.
5. Leave Row input cell empty. In Column input cell, pick any empty cell, such as G1. Click OK.

E2:E1001 now holds 1,000 profits, one per trial. Excel writes the range as {=TABLE(,G1)}, and you can't edit single cells inside it.

A Data Table recalculates every time anything in the workbook changes, which can make a big file slow. Go to Formulas > Calculation Options > Automatic Except for Data Tables. The table then updates only when you press F9 (Mac: ⌘=).

Without a Data Table#

You can also put the random formula straight into each row. In E2, type =ROUND(NORM.INV(RAND(),$B$1,$B$2),0)*$B$3-$B$4 and fill it down to E1001. The $ signs lock the inputs, so every row uses the same B1:B4. Without them, the references slide down as you fill and point at empty cells. In Microsoft 365, one formula can do all 1,000 trials: =ROUND(NORM.INV(RANDARRAY(1000),B1,B2),0)*B3-B4.

Step 4: Summarise the results#

Summary formulas turn 1,000 numbers into an answer. Put these next to the table:

With this model, the average lands close to 240, and roughly 37% of trials show a loss, because sales below 500 units don't cover the fixed costs. The 5th percentile sits around −940: in the worst 1 in 20 months, the loss is about that size or bigger. Your exact numbers will differ slightly each run, because the trials are random.

The AVERAGE function guide and the guide to AVERAGE, MIN and MAX cover the basics. For the 5th percentile, see the percentile and quartile guide.

Step 5: Chart the outcomes#

A histogram shows the shape of the results at a glance. Select E2:E1001 and choose Insert > Insert Statistic Chart > Histogram (Excel 2016 or later). You should see a bell shape centred near 240. The histogram guide shows how to set the bin width so the chart reads clearly.

Tips for a reliable simulation#

• Freeze a run you report. Copy the results and paste them as values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V, then Values). Otherwise the numbers change while someone reads them.
• Run enough trials. Run the table twice. If the average and the loss chance barely move, you have enough.
• Use realistic inputs. A simulation is only as good as its ranges. Base the mean and standard deviation on past data, not hope.
• Keep inputs in cells. Then you can test a higher price or lower costs and rerun in seconds.
• Check one trial by hand. Press F9, read B6 and work out B7 yourself.

Practise it#

A simulation stands on two basics: summarising a column of results, and locking input cells with $ so a formula can be filled down a thousand rows. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise relative and absolute references. Each lesson checks your formula instantly. Then try the exercise on this page to turn eight trials into a chance of loss.