The article will illustrate two examples to perform one and two-variable sensitivity analysis in Excel. Sensitivity analysis studies how different sources of uncertainty can affect the final output in a mathematical model. We can perform one and two-variable sensitivity analysis in Excel by using some formulas.

The sample dataset below will be used for illustration.

**Example 1 – Example of One-Way Variable Sensitivity Analysis in Excel**

We need to first rearrange the data to perform the one-way variable sensitivity analysis.

**Steps:**

- We have selected the table ranges (cells
**B9:C16**in this case) which begin one row above the first input value (row**9**) and the last row is the row containing the last input value (row**16**). - The first column in the range is the column containing inputs (column
**B**) and the last column is the column including outputs (column**C**). - Click the
**Data**tab on the ribbon, choose**What-If Analysis**and click on**Data Table**.

- A
**Data Table**dialog box is prompted after clicking on**Data Table**. Since our table is column-oriented, enter the column input cell reference**$C$4**and leave the row input cell blank.

- On clicking
**OK**, we will get the following results. - When the mortgage amount decreases from $200,000 to $140,000, the monthly payment will decrease from $886.96 to $620.87. The amount will decrease in the other variable mortgages as well as the monthly payment.

- We have made a one-way data table to see the impact of changes in terms of the months.

**Example 2 – Example of Two Way Variable Sensitivity Analysis in Excel**

Let’s find out how monthly payment varies as total mortgage varies from $140,000 to $260,000 (in $20,000 increments) and term length varies from 5 years to 35 years (in 5-year increments).

**Steps:**

- We have changed two inputs and therefore we created the two-way data table.
- We have entered the mortgage amount values down the first column (column
**B**) of the table range and term length values in the**7**^{th}

**Note: **A two-way data table can have only one output cell and you must place the formula for the output in the upper-left corner (**B9** in this case) of the table range.

As with a one-way table, we selected the table range (range **B9:I16**) and clicked the **Data **tab. In the **Data Tools** group, choose **What-If Analysis** and select **Data Table**. Since there are two sources of uncertainty, we need to fill both rows input cell and column input cell. In our case, cell **C4** (*mortgage amount*) is the column input cell and cell **C6** *(mortgage term length*) is the row input cell.

- In the
**Data Table**dialog box, we entered the cell reference**$C$6**in the**Row input cell**section and**$C$4**in the**Column input cell**

- After clicking
**OK**, the two-way data table will be shown as follows. For example, in cell**C8**, when the mortgage amount is $140,000 and the term length is 5 years or 60 months, the monthly payment is $2,540.58.

## Things to Remember

- If you change input values in a worksheet, the values calculated by a data table changes too. For example, if you increase the interest rate from 3.40% to 4.00%, the monthly payment will be $2578 (number in a blue square) given a 5-year $120,000 mortgage. When comparing with the above figure, you can see in the following image that all values in this two-variable data table have been changed.

- You cannot delete or edit a portion of a data table. If you select a cell in the data table range and edit accidentally, the Excel file will prompt a warning message and you can’t save, change or even close the file. The only way that you can close it is by ending the task from task management.
- Though the column-oriented one-way table (which is also applied in this post) is used popularly, you can also make your own row-oriented one-way table by yourself. Simply enter inputs in one row and only fill the row input cell reference in the
*Data Table*dialog box. - When making a two-way data table, do not mix up your row input cell and column input cell.
- Excel enables the automatic calculation by default and that’s the reason why any change in the inputs can cause all data in the data table to recalculate. However, sometimes, we’d like to disable this feature especially when the data tables are large and automatic recalculation is extremely slow. In this situation, how can you disable automatic calculation? Just click the File tab on the ribbon, choose Options, click the Formulas tab and select Automatic Except For Data Tables. You will be able to recalculate all your data in the data table only when you press the
**F9**(recalculation) key.

**Download Practice Workbook**

## Related Articles

- How to Create a Sensitivity Table in Excel
- Data Table Not Working in Excel
- [Fixed] Excel Data Table Input Cell Reference Is Not Valid

**<< Go Back to Data Table in Excel | What-If Analysis in Excel | Learn Excel**