Data Table
Hi Guys,
We hope that you are enjoying our blog posts. Please share it with colleagues & friends in case you find them useful.
A data table is a range that calculates changing variables based on a single formula. The data table is one of “what if analysis” features (Do not compare this Data table option with simple TABLE format in insert tab). Instead of entering formula and variables individually in cells, we can set a data table with two types of variables-
1- One-way variable
2- Two-way Variable
One-way Variable-
This is the example of the One-way variable in the data table, in the below mention screenshot, we came here to calculate many variables based on a single formula. –
Now, we would like to calculate EMI, Principal and Interest amount for 10 years (120 months). We have two choices either we can do it manually or put it down the formula in one cell then drag it towards down in your cells, However, For each heading, we have to repeat the procedure.
Instead of testing the results with a single formula, you can set up a data table, and compare the results, side-by-side. Let’s do it-
- Now, we are here to calculate EMI, Principal and interest amount based on 10 years. Basically, we mentioned the number of payments in monthly.
- Note: To calculate EMI, apply this formula for PMT (=PMT(B3/12, B4,-B2),For Principal amount use this formula (=PPMT(B3/12,B10,B4,-B2) and for Interest calculation use this formula (=IPMT(B3/12,B10,B4,-B2)
- Select the database (B9: E20)
- Go to Data Tab
- What if Analysis
- Select Data Table
- Select the cell address B4 into the column input cell because the number of payments is in column values.
- Click Ok
We will get all the result on a single click. Refer the screenshot below.
The resulting data table shows how the amount getting changed as the number of payments is changing.
Once the data table is in place, you can’t change any of the referencing cells (columns C, D, and E in this data table). Excel protects those cells. In addition, the data table formulas are a series of =TABLE () functions – this function is just for show. You can’t use it in any other context.
How to clear the data
Because the data table values are in an array, you cannot edit or clear individual cells. If you try to change one cell, you will see an error message – “Cannot change part of a data table.” If you want to remove the entire table, or the resulting values, follow the steps below.
To remove the data table from the worksheet:
Select all the cells in the data table, including the heading
On the keyboard, press the Delete key
To clear out the resulting values only:
Select all resulting values in the data table.
On the keyboard, press the Delete key
Two-way Variables-
In this example, we will create a table based on two variables. For example, we will check the different EMI amount from the different rate of interest and different number of Payments.
Let’s see the example below-
The same database with two variables, we would like to calculate the EMI on the different rate of interest and number of payments.
- First, make a relation between cell address B9 to B5 with an equal (=) sign
- Select the table (B9: H20)
- Data Tab
- What if analysis
- Data table
Considering the database, the rate of interest will be row input cell and the number of payments will be column input cell. For row input select cell B3 and for column input select cell B4.
- Click Ok
- We will get all the EMI’s based on a different rate of interest and number of payments.
Note: When we want to calculate the number of variables based on a single column is called “One-way Variable”. When we want to calculate the number of columns and rows both at the same time then we called it two-way variable.
Hope, you would have liked the blog post. if you have any query, Suggestion or ideas, You can write it down in the comment box below.
Stay tuned with Nurture Tech Academy.
Happy Learning 😊