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.

## Download Practice Workbook

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

## 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.

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

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.

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

`=F4*F5`

- Thirdly, for finding 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`

- And lastly, I will determine the profit for this whole procedure by entering the following formula in cell
**F10**, and by pressing**Enter**, I will 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**like the following image.

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

- Consequently, you will see the
**Data Table**dialogue box after the previous step. - Then, in the
**Row input cell**type 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.

**Read More:** **How to Create a Table with Existing Data in Excel**

**Similar Readings**

**How to Create Table from Data Model in Excel (With Easy Steps)****Make a Table in Excel with Lines (with Easy Steps)****How to Create Excel Table with Row and Column Headers****Excel VBA to Create Table from Range (6 Examples)**

### 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 in like the first procedure of this article.

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

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

- Fourthly, after selecting the previous command, you have to give some input in the
**Data Table**dialogue box. - First of all, in the
**Row input cell**box select cell**F5**, as the different values of price per item are in the same row. - Then, 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.
- Consequently, 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:** **Create a Table in Excel Based on Cell Value (4 Easy Methods)**

## Things to Remember

- Remember that the data table cell must contain the exact 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.

## 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.

The** ExcelDemy** 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.