In real-world planning—whether you’re forecasting revenue, evaluating loan terms, or comparing investment scenarios—you rarely work with a single fixed assumption.
Sensitivity analyses explore how a situation responds to changes in influencing factors. Excel provides two versions of what-if analyses that differ mainly in the number of variables they allow: The first version varies one value, and the second varies two. The number of variables also affects how the data is arranged for calculation, which varies between the two versions. Plus, the number of formulas that a version can evaluate depends on the number of variables, and only the single-variable version can evaluate multiple formulas at once.
Here’s a simple example. In the figure below, you’ll find a small application using a present-value function. The question might be this: If the interest rate is 1.3%, how much must be deposited on 1-Jan to receive an amount after three years that’s equal to three annual deposits of $40,000?
The formula with the PV() (present value) function in cell C6 doesn’t use a fixed interest rate but refers to cell B5, where 1.3% is initially entered. Follow these steps.
To observe the effect of different interest rates, enter a series of rate variations in a free column (which is column B in our figure). The Other interest rates label shows these values, and you can enter the alternative values one below the other in a single column. The order isn’t important, but sorting them in ascending or descending order makes the results clearer.
Prepare the formula that will use these alternative values, which in this example is the present value function. Enter this formula in the row directly above the first row of alternative value, which must be shifted at least one column to the right because the program will place the newly calculated values directly below this formula. In our example, cell C6 is the correct place for the formula.
Select the range to use for the data table (earlier versions called this a multioperation). In our example, this range is shaded for clarity. The range is defined by the column with alternative values and the row containing the formula or formulas the command will recalculate. Unlike a normal formula, this operation’s result doesn’t appear in the cell with the formulas but in a continuous series of cells.
From the Forecast group on the Data tab, select the Data Table command from the What-If Analysis button menu. This opens the dialog box, where you can enter the cell address for the interest rate—which in this case is B5. This is the input cell where you can enter different interest rates one after another to recalculate the present value formula each time. Since the alternative interest rates are arranged in a column, enter the address “$B$5” in the Column input cell field. If the alternative interest rates were arranged in a row—say, from D5 to L5—you would enter the cell address in the Row input cell field.
When you confirm the entries in the dialog box, Excel calculates the present value formula for all alternative interest rates and shows the results in the column below the formula.
You can read the table like this: An alternative interest rate of 1.4% yields a present value of $116,716, while 1.7% yields $116,032, and so on. The original formula in cell C6 and the initially assumed interest rate of 1.3% in cell B5 remain unchanged.
If you look at cell C7, you’ll see an array formula with the TABLE() function in the formula bar. The cells that show the results of the multioperation form a result matrix. You cannot change or delete individual cells within this range, but you can freely modify the other interest rates you’ve entered. If you’ve set the other arguments of the present value function with cell references, you can now test different values for the number of periods or regular payments, and the entire multiple operation table will recalculate each time.
When working with a single variable, you can observe how multiple formulas behave simultaneously. For example, you can calculate the present value assuming payments are due at the beginning of the period. You just need to add the argument for the due date to the formula, and in this case, you select the range from B7 to D16 for the multiple operation.
The second version of the multioperation accepts two variables, or two input cells, but evaluates only one formula at a time. The table layout differs slightly from the first version. Enter the formula in the previously empty top-left cell of the selected table range.
Then, you enter the alternative values for the first variable in the left column of the table range, as usual, and you enter the values for the second variable in the first row, starting with the cell to the right of the formula cell. So, this version requires the values for the second variable to be where the formulas were in the first version.
The figure below shows the layout of this specific table range, and once again, we’ll use a financial function as the example. A rate calculation uses three arguments: a principal amount (such as a loan), an interest rate, and the number of periods. The loan and interest rate will be varied. The values for the first variable (the loan) are in column B, while the values for the second variable (the interest rate) are in row 6. The reference to the rate formula is in the top-left cell of the table range, which is cell B6.
First, select the table range from B6 to G10, and then, use the Data Table command. This time, fill in both input fields, and for the Row input cell, enter the address of the cell with the interest rate, which is B3. For the Column input cell, enter B4, which contains the loan amount.
This command creates a table of values you can read like an xy chart. To find the payment for a $13,000 loan at a 1.55% interest rate, locate the row starting with $13,000 and then move right to the column headed by 1.55%, which contains the corresponding payment.
After the array operation returns its results, it’s often helpful to convert the array formulas in the result range into constant values. To do this, select the entire result array—using (Ctrl)+(/) or Home > Editing > Find & Select > Go To Special > Current Array—copy the data, and then use Paste Special with the Values option to paste over the selection. Excel will automatically select the entire array range, but if you want to keep the original formula(s), select only the cells with the array formulas!
What-If Analysis in Excel turns static formulas into dynamic decision-making tools. Instead of manually changing inputs and recalculating one scenario at a time, you can evaluate dozens of possibilities in a structured, readable format. One-variable data tables help you understand sensitivity to a single assumption, while two-variable tables allow you to compare combinations of inputs side by side.
Used thoughtfully, these tools make financial modeling, forecasting, and operational planning more transparent and far more efficient. Whether you’re analyzing loan payments, investment returns, or budget projections, data tables allow you to explore alternatives confidently—without compromising the integrity of your original formulas.
Editor’s note: This post has been adapted from a section of the book Excel: The Comprehensive Guide by Helmut Vonhoegen. Helmut is a freelance author and IT consultant. He has published more than 80 books since 1992 and written numerous articles in specialist journals. His focus is Microsoft Office, Windows, web programming, and XML.
This post was originally published 3/2026.