Imagine you want to check how much you’d have to pay back at different interest rates and loan tenures. Chances are you’d manually swap the input numbers, duplicate tabs, or write really long formulas to check.

Well, you don’t have to do that. Tucked away on the Data tab under the Forecast (or Data Tools) group is the menu you need: What-If Analysis. Instead of creating bloated workbooks with dozens of competing tabs, you can use Data Tables, Scenario Manager, and Goal Seek in Excel to change multiple values and see how those changes pan out.

Data Tables

Build a grid of any size in seconds

When you need to evaluate a formula across a wide spectrum of values, you can use a Data Table to display every possible outcome side-by-side in a single grid on one worksheet. You can create either a one-variable table or a two-variable table, depending on how many inputs you want to test.

A one-variable Data Table tests a list of values for a single input variable arranged down a column or across a row, allowing you to evaluate one or more formulas simultaneously. A two-variable Data Table, on the other hand, tests combinations of two input variables simultaneously, with one mapped along the row input and the other along the column input, to evaluate a single formula.

Imagine you’re checking which home mortgage plan you can afford by evaluating different interest rates across different payment tenures. You can evaluate this using either a one-variable Data Table or a two-variable Data Table. Let’s imagine you have the following values in your worksheet across cells A1 to B4:

Say cell B2, which contains the interest rate, is input cell A, while B3, which contains the loan term in months, is input cell B. For a one-variable Data Table, you’ll use only one input cell because you’re testing a single variable, while you’ll use both input cells when creating a two-variable table.

For a two-variable table, you’d populate the top row and top column of the grid you want to populate with values that differ from your input cells. For instance, you’d have a table like this:

If you’re building a one-variable table, however, you’d populate either the row or the column. For instance, you could test your repayment at different interest rates or different repayment tenures. The benefit of using a one-variable table is that you can also test different formulas simultaneously:

Once you’ve populated your grid with your variable or variables, select the range (C1:F4 or C1:E4) and go to Data > Forecast > What-If Analysis > Data Table. Remember the input cells from above, B2 and B3? If you’re using only one variable, such as B2 for interest rates, you’d set the Column Input Cell or Row Input Cell to B2, depending on whether you’ve populated a column or a row, and leave the other input cell blank. For a two-variable Data Table, you’d set both input cells based on how you’ve arranged your grid.

Once you click OK, Excel will populate the blank cells in your grid with the possible outcomes. The number of variables you’re testing determines how the table is structured, while the number of values you provide determines how many outcomes Excel can generate. You can include as many variable values as your worksheet grid permits, so you can generate a 60-cell, 100-cell, or even larger grid with a single command.

Just note that large tables can slow down processing because Data Tables recalculate automatically whenever any worksheet cell changes. To fix this, you can change your calculation settings to Partial under Workbook Calculation by visiting File > Options > Formulas. This lets you refresh your table calculations only when you need them.

Scenario Manager

For juggling complex multi-variable models

When a decision depends on more than two changing factors, you can’t use Data Tables anymore. That’s where Excel’s Scenario Manager comes in. It saves distinct sets of changing cell values that you can toggle between on demand.

Each scenario can accommodate up to 32 changing cells or variables, so you can create as many named scenarios as you need, such as “Optimal,” “Negative,” or “Normal.” For instance, you’d be able to store different baseline revenue and cost inputs without altering the underlying formula structure. Say you set up your worksheet like this:

To create different scenarios, go to Data > Forecast > What-If Analysis > Scenario Manager and click Add. In the name box, type “Optimal” or whatever you want to name your scenario. In the Changing cells box, type B1:B2 and click OK. In the Values dialog box, enter 150000 for B1 and 30000 for B2, then click OK. You can follow the same process to create more scenarios with different values, as many as you’d like, with up to 32 changing variables in each one.

After creating your scenarios, select “Optimal” and click Show. Excel will update cell B1 to $150,000, cell B2 to $30,000, and your Gross Profit formula in B3 will automatically recalculate to $120,000.

If you want a side-by-side comparison report, click Summary inside Scenario Manager and select your result cell, B3. Excel will generate a standalone Scenario Summary Report on a new worksheet, displaying all your input values and resulting profits side-by-side.

Goal Seek

Reverse engineer your targets

While Data Tables and Scenario Manager project forward from known inputs to calculate possible results, Goal Seek projects backward. If you know the specific output you need a formula to produce, Goal Seek can calculate the input value required to hit that target in your financial analysis.

Goal Seek requires three inputs: the Set cell, which contains your formula; the To value, which is your target numerical result; and the By changing cell, which is the single input cell Excel adjusts. So, for example, if a loan yields a $1,250 monthly payment but your budget cap is $500, Goal Seek can adjust the loan amount, interest rate, or loan term cell to show exactly what you can afford.

Just as with Data Tables and Scenario Manager, you first need to set up a base range, like this:

In this case, the Set cell would be B4, the To value would be 500, and By changing cell could be B1, B2, or B3, depending on your specific numerical situation or what you’d prefer to adjust.

Stop checking what if manually

I’ve learned my lesson, so I know that ignoring Excel’s What-If Analysis menu only means more hours wasted manually testing which value is right for me. Depending on what you want to achieve, any of these three tools can transform Excel from a basic calculator into a dynamic decision-making platform.