Sensitivity analysis or sensitivity tables in Microsoft Excel are quite easy to create or perform. By giving proper input of data and following some commands, one can easily perform a sensitivity analysis. In this article, I will show you how to create a sensitivity table in Excel.

## Introduction to Sensitivity Analysis in Excel

According to a specific set of assumptions, sensitivity analysis evaluates how various values of an independent variable impact a specific dependent variable. One can apply this method within defined parameters that are dependent on one or more input variables.

## How to Create a Sensitivity Table in Excel: 2 Effective Ways

In this article, you will see two effective ways to create a sensitivity table in Excel. In the first method, I will create the table with only one variable. But, in the second approach, I will use two variables to build the table. In both ways, I will require the Data Table feature of Excel to accomplish my task.

To illustrate my article further, I will use the following data set.

### 1. Create a Sensitivity Table with One Variable

In the first method, I will create a sensitivity table using one variable in Excel. After creating the table, you will be able to analyze the final result based on different inputs. The steps to perform this procedure are as follows.

**Steps:**

- First of all, I need to do some pre-calculations before starting the main procedure.
- Here, make three extra fields in the primary data set to calculate the total sales, total cost, and profit.

- Next, to calculate the total cost insert the following formula in cell
**F6**and press**Enter**to get the value.

`=F4*F5`

- To find out the total cost, I will need to add the utility cost and the transport cost and for that, in cell
**F9**type the following formula and hit**Enter**.

`=F7+F8`

- Lastly, I will determine the profit for this whole procedure by entering the following formula in cell
**F10**. Press**Enter**to get the desired result.

`=F6-F9Â `

- Afterward, I will start the main procedure from this step. To start the procedure, in cell
**C13**of the sensitivity table, insert the same input of cell**F10**in the following image.

- After that, in the product quantity row, input the amount of product as per your choice.
- Then, select range
**C12:H13**and go to the**Data**tab of the ribbon. - Lastly, from the What-If Analysis dropdown, choose
**Data Table**.

- You will see the
**Data Table**dialogue box after the previous step. - In the
**Row input cell**box, select cell**F4**, as I am creating this data table based on product quantity. - Lastly, press
**OK**.

- Finally, you will be able to create the sensitivity table after performing the previous steps.
- From the image below, you can see the estimated profits for this business earned by selling different quantities of the same product.

### 2. Create a Sensitivity Table with Two Variables

In the second method of this article, I will create the sensitivity table with two variables instead of one. The other procedures for this method will be the same. For a better understanding, see the below-given steps.

**Steps:**

- Firstly, create an additional table, under the primary data set like the following pictures.
- Additionally, I did the pre-calculations for this method just like the first procedure of this article.

- Then, in cell
**C13**, insert the same cell value as in cell**F10**just like in the following image.

- Again choose
**Data Table**from**What-If Analysis**after selecting the range**C13:H18**like the picture given below. - Before selecting the range, input values of both variables as per your choice.

- After selecting the previous command, you have to give some input in the
**Data Table**dialogue box. - In the
**Row input cell**box select cell**F5**, as the different values of price per item are in the same row. - In the
**Column input cell**box select cell**F4**as various values of the product quantity are in the same column. - Lastly, press
**OK**.

- Finally, you will see the sensitivity table filled with various values of profit just like the following image.
- This indicates how much profit you will earn in terms of that respective product quantity and price per unit.
- For example, if other costs are fixed and you sell 80 units of products at $65 per product then it will make a profit of $1750.

**Read More: **How to Create a Two-Variable Data Table in Excel

## Things to Remember

- Remember that the data table cell must contain the same formula just like the data set, while you are giving input from the main data set in the data table.
- Otherwise, if you input the value only instead of the formula, it wonâ€™t show any actual result.

You can download the free Excel workbook here and practice on your own.

## Conclusion

Thatâ€™s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to create a sensitivity table in Excel. Please share any further queries or recommendations with us in the comments section below.

Our team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

**Related Articles**

- One and Two Variables Sensitivity Analysis 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**