• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

Using Scenario Manager to Track Design Iterations in Excel

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

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.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

Evaluating Derivatives of Equations in Excel with VBA

Complex Number Functions in Excel

Nonlinear Curve Fitting in Excel

Using Scenario Manager to Track Design Iterations in Excel

Named Arrays in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in