If you’re anything like me, you often use Excel to experiment with different parameters or inputs to see how they might affect a design. It can quickly become a challenge to keep track of all those generated design iterations so that they can be recalled quickly.

One way is to create separate workbooks or worksheets for each design iteration, but that gets really messy and unmanageable in a hurry.

A better way to keep track of design iterations in your spreadsheets is to use Scenario Manager. This built-in Excel tool can be used to create and save groups of input values to be recalled and displayed on the worksheet later.

Let’s look at how it works with a very basic engineering calculation:

Stress = Force / Area

Preparing the Spreadsheet

Before we do anything, we’ll want to create named cells (I use “Create from Selection“). This step is optional, but it will make it much easier to understand the scenario values later. Otherwise, we’ll have to decipher cell references (Yuck!).

Creating a Scenario in Excel

Now that we’re ready to create our scenario, go to Data>Forecast>What-If Analysis and open the Scenario Manager:

When the scenario manager window opens, Select “Add”:

The next window that opens is the Add Scenario dialog. In this window you can save your design iteration as a scenario.

Adding a scenario requires four steps:

1. Name the scenario. I’ll call this “Design 1”.
2. Select the changing cells. These are the cells containing the input parameters, or C3:C4 in this case.
3. Add a comment describing the scenario (optional, but a good idea).
4. Click OK.

In the next window that appears, we’re given the opportunity to confirm or adjust the values of the changing cells. Because these cells are named, the input boxes are labeled with the names we created above. If the names had not been assigned, cell references C3 and C4 would be displayed here instead.

Since the whole point of Scenario Manager is to track multiple scenarios, let’s look at adding a second one.

Change the value of the force to 20,000 N, then open scenario manager and add a new scenario.

The changing cells are already set, but we’ll need to give it a name (“Design 2”) and possibly add a comment.

Once the values are confirmed on the next screen, we’ll have 2 saved scenarios.

Showing a Saved Scenario on the Worksheet

Now that we have two different designs stored, it’s easy to switch between them and see the result.

With scenario manager open, select the design you want to view, and click “show”.

Doing so populates the values from that design into the “changing cells” that you selected previously.

Creating Scenario Summaries to Quickly Compare Designs

If result of design calculations is a single value, an even handier way to quickly compare designs is by creating a Summary Report.

In the scenario manager, click “Summary”. Then, in the window that opens next, choose whether you want to create a “Scenario summary” or “Scenario PivotTable report”.

Then choose the cell containing the result and Click OK.

The scenario summary is created on a new worksheet:

Each scenario or design iteration is displayed in a column. The inputs values are in the upper rows of the table, and the result is in the bottom row.

Making Changes to a Scenario

So what happens if you want to make changes to the values in a scenario?

With the scenario manager open, select the scenario you want to modify, and click “Edit”

In the first window, you can edit the name, “changing cells”, and add or update the comments just as before.

When you click OK, you’ll be taken to the second window, where you can make changes to the values for each of the changing cells.

Edit the values, click “OK”, then “Show” to display the modified scenario on the spreadsheet.

Scenario Manager Limitations

If you decide to use the Scenario Manager in your spreadsheets to keep track of design iterations, there are a couple of limitations you should know about:

1. Scenario manager is limited to 32 changing cells, so it won’t work if you have more input cells than this that you want to track. Depending on the complexity of your calculations, this may or may not be an issue for you.
2. Scenario summaries are a static snapshot of the current scenarios at the time that the summary was generated. If you modify a scenario with a new value, you will need to regenerate the summary.

Conclusion

So there is Scenario Manager! Play around with it and I hope you can find it useful to manage all the design iterations you generate in your engineering spreadsheets.