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