Data Table in Excel (Table of Contents)
Data Table in Excel
Data tables are used to analyze the changes seen in your final result when certain variables are changed from your function or formula. Data tables are one of the existing parts of What-If analysis tools, which allow you to observe your result by experimenting it with different values of variables and to compare the outcomes stored by the data table.
There are two types of a data table, which are as follows:
- One-Variable Data Table.
- Two-Variable Data Table.
How to Create Data Table in Excel?
Data Table in Excel is very simple and easy to create. Let’s understand the working of the Data Table in Excel by Some Examples.
Data Table in Excel Example #1 – One-Variable Data Table
One-variable data tables are efficient in the case of analyzing the changes in the result of your formula when you change the values for a single input variable.
Use case of One-Variable Data Table in Excel:
The one-variable data table is useful in scenarios where a person can observe how different interest rates change the amount of their mortgage amount to be paid. Consider the below figure, which shows the mortgage amount calculated based on the interest rate using the PMT function.
The table above shows the data where the mortgage amount is calculated based on the interest rate, mortgage period and loan amount. It uses the PMT formula to calculate the monthly mortgage amount, which can be written as =PMT (C4/12, C5,-C3).
In the case of observing the monthly mortgage amount for different interest rates, where the interest rate is considered as a variable. In order to do this, there is a need for creating a one-variable data table. The steps to create the one-variable data table are as follows:
Step 1: Prepare a column which consists of different values for the interest rates. We have entered different values for interest rates in the column which is highlighted in the figure.
Step 2: In the cell (F2), which is one row above and diagonal to the column which you prepared in the previous step, type this = C6.
Step 3: Select the entire prepared column by values of different interest rates along with the cell where you had inserted the value, i.e. F2 cell.
Step 4: Click on the ‘Data’ tab and select ‘What-If Analysis’, and from the options popped down, select ‘Data Table’.
Step 5: Data table dialog box will appear.
Step 6: In the Column input cell, refer to cell C4 and click OK.
In the dialog box, we refer to the cell C4 in the Column input cell and keep the row input cell empty as we are preparing a data table with one variable.
Step 7: After following all the steps, we get all the different mortgage amounts for all entered interests rates in column E (unmarked), and the different mortgage amounts are observed in column F (marked).
Data Table in Excel Example #2 – Two-Variable Data Table
Two-variable data tables are useful in scenarios where a user needs to observe the changes in the result of their formula when they change two input variables simultaneously.
Use-case of Two-Variable Data Table in Excel:
The two-variable data table is useful in scenarios where a person can observe how different interest rates and loan amounts change the amount of their mortgage amount to be paid. Instead of calculating for individual values separately, we can observe them with instantaneous results. Consider the below figure, which shows the mortgage amount calculated based on the interest rate using the PMT function.
The above example is similar to our example shown in the previous case for a one-variable data table. Here the mortgage amount in cell C6 is calculated based on the interest rate, mortgage period and loan amount. It uses the PMT formula to calculate the monthly mortgage amount, which can be written as =PMT (C4/12, C5,-C3).
In order to explain the two-variable data table with reference to the above example, we will show the different mortgage amounts and choose the best which suits you by observing the different values of interest rates and loan amount. In order to do this, there is a need for creating a two-variable data table. The steps to create the one-variable data table are as follows:
Step 1: Prepare a column which consists of different values for the interest rates and loan amount.
We have prepared a column consisting of the different interest rates, and in the cell diagonal to starting cell of the column, we have entered the different values of the loan amount.
Step 2: In the cell (E2), which is one row above to the column which you prepared in the previous step, type this = C6.
Step 3: Select the entire prepared column by values of different interest rates along with the cell where you had inserted the value, i.e. E2 cell.
Step 4: Click on the ‘Data’ tab and select ‘What-If Analysis’, and from the options popped down, select ‘Data Table’.
Step 5: A Data table dialog box will appear. The ‘Column input cell’ refers to cell C4 and in the ‘Row input cell’ C3. Both the values are selected as we are changing both the variables and Click OK.
Step 6: After following all the steps, we get different values of mortgage amounts for different values of interest rates and loan amount.
Things to Remember About Data Table in Excel
- For one variable data table, the ‘Row input cell’ is left empty, and in a two-variable data table, both ‘Row input cell’ and ‘Column input cell’ are filled.
- Once the What-If analysis is performed, and the values are calculated, you cannot change or modify any cell from the set of values.
Recommended Articles
This has been a guide to a Data Table in Excel. Here we discuss its types and how to create data table examples and downloadable excel templates. You may also look at these useful functions in excel –
