Excel has a great set of little-known tools hidden within the Data tab of the ribbon. They are considered “Forecasting” tools, but they are incredibly useful for engineers performing design calculations in Excel too.

The tools are contained within the “What-If Analysis” menu and are:

• Scenario Manager
• Goal Seek
• Data Table

Scenario Manager

The scenario manager tool allows you to save input values as a “scenario”.

If you’ve been working on some calculations in a spreadsheet and want to examine alternatives without losing your current solution, you can save it using the scenario manager.

The tool then allows you to recall those inputs at any time by selecting the scenario and clicking “Show”. You can even create a summary of all the different scenarios for easy comparison.

The thing I really like about scenario manager is that there is no more need to save multiple design iterations as separate spreadsheets. Instead, I can have all the design options in one spreadsheet with multiple scenarios.

Goal Seek

The goal seek tool allows us to specify an outcome value (“Set cell”) and let Excel iterate to find the input value (“By changing cell”) that provides the desired result (“To value”). Excel can do this much more quickly than you and I can guess at input values and check the result. 🙂

I’ve demonstrated before how you can use this tool to solve implicit and/or nonlinear equations in Excel. You can even use a macro to automatically run Goal Seek whenever a change occurs on a worksheet.

Goal Seek can only find a single input to achieve the desired result. If we want to look at how multiple input values can affect the outcome, we need to use the Solver add-in.

Data Table

Data tables are a fantastic way to visualize how an input variable (or two) is related to an output variable.

Data tables work by taking a formula in a single cell and creating a table of inputs and an output based on that formula. Since they are “tables”, they are limited to only two variables – one in a row and the other in a column.

They are especially useful for understanding the relationship between an input and output variable – i.e. is it linear/nonlinear, decreasing/increasing, etc.