**Microsoft Excel **is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default **Excel Functions** that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we estimate possible outcomes from real-life projects by changing input variables. Accordingly, an analysis of those probable scenarios is necessary. This is called **Sensitivity Analysis**. In that case, we can stay prepared for whatever happens. Hence, this article will show you the **2 **different criteria to build a **Sensitivity Analysis Table** in **Excel**.

## Download Practice Workbook

Download the following workbook to practice by yourself.

## 2 Different Criteria to Build a Sensitivity Analysis Table in Excel

In this article, we’ll build the sensitivity analysis table for both one-variable and two-variable cases. To illustrate, we’ll use the following dataset as an example. For instance, we have a **Mortgage Amount**,** Interest Rate**, and **Months**. In the **One-Variable** case, we’ll consider **5 **different mortgage amounts. And in the **Two-Variable** case, we’ll take**3 ** different values for months along with the **5 **mortgage amounts. In cell **C9**, we input the formula:

`=PMT(C5/12,C6,C4)`

**The PMT function** determines the loan amount for a fixed interest rate, time period, and present value of the mortgage.

### 1. Build One Variable Sensitivity Analysis Data Table in Excel

Follow the below steps carefully to create a sensitivity analysis table based on one variable.

**STEPS:**

- First, select the cell range
**B9:C14**. - Then, go to
**Data ➤ Forecast ➤ What-If Analysis ➤ Data Table**.

- Consequently, the
**Data Table**will pop out. - Select cell
**C4**as the**Column input cell**. - After that, press
**OK**.

- As a result, it’ll spill the precise monthly payment amounts.
- See the picture below.

- Now, click any cell in the spilled result range (
**C10:C14**). - You’ll see the same formula applied to all the output values.

### 2. Create Excel Two-Variable Sensitivity Analysis Table

In this example, we’ll arrange our dataset to consider the two variables. We’ll take **3 **different time periods (**60** months, **120 **months, and **180 **months) to pay back the mortgage amount. Here, **5 **different values for the mortgage will be considered as well. Moreover, we’ll move the formula in cell **C9 **for calculating the loan to cell **B9**. Now, learn the following process to build a **Two-Variable Sensitivity Analysis Table** in **Excel**.

**STEPS:**

- Firstly, choose the range
**B9:E14**. - Now, select
**Data ➤ Forecast ➤ What-If Analysis ➤ Data Table**.

- Subsequently, the
**Data Table**will pop out. - Next, choose
**C6**in the**Row input cell**. - Again, select cell
**C4**as the**Column input cell**. - After that, press
**OK**.

- Thus, you’ll get the sensitivity analysis data table as shown below.

- Select any cell in the output range.
- You’ll see that the same formula is behind all the outputs.

## Things to Remember

- Any changes in the inputs will result in changes in the final calculation of the outputs. For example, if you change the interest rate, the monthly payment amounts will get modified too. Therefore, special care is a must so that we don’t change any input data by mistake.
- Moreover, you can’t edit or delete a certain portion of the spilled output range. You’ll get a warning message if you try to do so. This could also result in unnecessary complications.
- During the two-variable data analysis, make sure you don’t mix up while choosing the row and column input cells. Or else, it’ll result in a big error.

## Conclusion

Henceforth, you will be able to build a **Sensitivity Analysis Table **in **Excel **following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.