In this post, we are going to take a look at the data table option under What if analysis in Excel. What-if analysis is the process of changing the values in cells which helps us to understand how those changes will affect the outcome.

Types of data table.

- one -variable (either
)**columns or rows oriented** - Two -variable (a combination of
)*columns & rows*

## What are Data Tables?

In MS Excel, Data Table can be spotted under **What if analysis** which helps the user to try different inputs values for a single formula and checks how the results affect the output. This reduces the need to create multiple scenarios.

A person in the field of Analytic, Share market Investing, Banking, etc, data tables can reduce their redundant work and save time by using this tool. It is useful when a formula depends on several values and user likes to experiment with different combinations of inputs and compare the results.

#### Note: *A Data Table is different from Table, which empowers to manage and analyze a group of related data. *

To know more on Tables click on the Text as follows –Excel Tables – 9 Reasons Why You Should Use

## How to create One-variable Data table in Excel

One- variable Data table allows the user to test a series of values for a single input value and help in understanding how those values influence the result.

Say for instance we are borrowing loan of ₹ 3,00,000/- and the interest rate charged is 5% with a Repayment Period of 3 years (180 months). We find out the monthly payment to be made using the PMT function.

Now, let’s do a What-If analysis to see what will be the monthly payment for 5 years depending on loan amount ranging from ₹50,000 to ₹3,00,000 with a fixed interest rate.

Below are the steps to create a One-variable Data table:

** Step 1**– Enter the variable values. In this example, you will see a column-oriented approach. Variable values are entered in F4 to F9.

** Step 2**– Manually enter the formula in the cell or else give a link to the cell which consists of the formula.

*In this case, I have linked the cell. i.e. the cell G3 is linked to C9, which consists of the formula.*

* Step 3*– Select the data table range, including your formula or linked cell. (

*)*

**F4: G9**** Step 4**– Now Go To

**Data**>

**What-If Analysis**> Select

**Data Table**.

** Step 5–** A Dialog box pops-up on the screen, fill in the

**Column Input cell**box (because our

*Loan Amount*values are in a column), with the variable cell referenced in our formula. In this case, we select C5 that contains the Loan Amount value.

** Step 6**– Click OK, and Excel will automatically populate the data in empty cells with results corresponding to the variable value in the same row.

## How to create Two- variable Data table in Excel

Two- variable Data table in Excel allows the user to test a series of values with dual input values and help to understand how those values influence the result.

The steps are similar to One-Variable, the only difference is two range of input values are entered.

Take the above example, what will be the monthly payment amount for 5 years depending on the borrowing amount ranging from ₹50,000 to ₹3,00,000 but at different interest rates.

** Step 1**– Enter the variable values. I have entered the variable values in a column F4: F9. Link the cell F3 to the original PMT formula that calculates the monthly payment i.e., C9.

** Step 2**– Enter the other set of variable values to the right of the formula, in the same row (Interest rate in G3 to J3).

** Step 3**– Select the entire data table range including the formula, variable values and the empty cells in which the calculated values will populate (F3 to J9).

** Step 4**– Now Go To

**Data**>

**What-If Analysis**> Select

**Data Table**.

** Step 5**– A Dialog box will pop-up on the screen, enter values in the

**Row Input cell**box as C6 which is the interest rate and for

**Column Input cell**box C5 cell which contains Monthly loan payment to be made.

** Step 6**– Click

*OK*, and Excel will automatically populate the empty cells with results.

## Tip: Deleting value in data table

Excel does not allow the user to delete individual values within the output. While doing so, it pops an error stating “Cannot change part of a data table”, as it is an array Function.

1. You can delete the entire row or column, but not a single cell. Thus Select the Column or row.

2. Then press Delete.

To enhance your excel skills –Click here

## 1 Comment

Thanks for sharing this. We expect more topic from you in coming days.