Tanjima Hossain
If you are looking for some easiest ways to make a data table in Excel then you are in the right place. Creating a data table in Excel is an important feature of Excel because it will make any type of calculation easier. A data table in Excel allows you to try out different input values for formulas and see how changes in those values affect the formula’s output.
So, let’s get into the main article.
Download Excel Workbook
5 Ways to Make a Data Table in Excel
To explain the examples of the methods of making a data table in Excel, I have taken the following dataset where a person has invested 2,000 USD in a bank and after 5 years with an interest rate of 5% he got a total balance of 2,500 USD.
The formula of the total balance is,
Total Balance= Investment +Investment*Year*Rate of interest
Total Balance = C3+(C3*C4*C5)
Here, we will see the changes of total balance with the change of variables like Investment, Rate of interest by using data table in Excel easily.
Method-1: Creating One variable Data Table
Case-01: Column-oriented data table
Step-01: At first you have to make a column for various Investment values and a column for Total Balance where the output will be shown up.
Here, we are taking one variable named Investment. Now you will have to link up the first row of Total Balance with the result in cell C7.
Step-02: Then you have to select the whole range E4:F9 and follow Data Tab >> Forecast Group >> What-If Analysis >> Data Table
Step-03: After that, a Data Table Dialog Box will appear where you have to enter the input C3 with absolute reference in the Column input cell as it is a column-oriented data table.
Then press OK.
Step-04: After that, the various results will be shown up in the Total Balance column.
Case-02: Row-oriented Data Table
Step-01: Here, you have to make a row for various Investment values and a row for Total Balance where the output will be shown up. Here, we are taking one variable named Investment. Now you will have to link up the first column of Total Balance with the result in cell C7.
Step-02:Then you have to select the whole range C9:H10 and follow Data Tab >> Forecast Group >> What-If Analysis >> Data Table
Step-03: After that, a Data Table Dialog Box will appear where you have to enter the input C3 with absolute reference in the Row input cell as it is a row-oriented data table. Then press OK.
Step-04: After that, the various results will be shown up in the Total Balance row.
Read more: How to create a one variable data table in Excel
Method-2: Creating Two-Variable Data Table
Step-01: In the case of two-variable data tables both row-oriented and column-oriented data tables are combined. At first, you have to link up a cell such as B9 with the Total Balance in C6. Then immediately below and right side of B9 two types of variables; Investment and Rate of interest will be written as shown below.
Step-02: Then you have to select data range B9:F13 and follow Step-02 in Method-1. After that Data Table Dialog Box will appear, where you have to enter the references in both the Row input cell and the Column input cell and then press OK.
Step-03: After that, the outputs will be shown up as below.
Read more: How to Create a Two Variable Data Table in Excel
Method-3: Compare Multiple results with a Data Table
Suppose, you have to compare two outputs now rather than using two variables as in the previous method.
At first, you have to calculate Interest as another output in C8 by subtracting the Total Balance from Investment.
Then create two columns; Total Balance and Interest beside the Investment column.
Step-01: At first, the first row of the Total Balance and Interest columns have to be linked up with C7 and C8 respectively.
Step-02: Then select the range E4:G9 and follow Step-02 in Method-1. After that Data Table Dialog Box will appear where you will have to enter the reference in the Column input cell as it is a column-oriented data table and press OK.
Step-03: After that, the outputs will be shown up as below.
Similar Readings:
- Example of Excel Data Table (6 Criteria)
- How to Perform the What If Analysis with Data Table in Excel
- Data Table Not Working in Excel (7 Issues & Solutions)
Method-4: Creating Data Table Using Power Query
Step-01: Select the whole data table and press Data Tab>>From Table/Range
Step-02: Then Create Table Dialog Box will appear and here click on My table has headers.
Step-03: After that, a table will be created where you have to just write a formula in the first row of Total Balance.
Step-04: After pressing ENTER the remaining rows of Total Balance will be filled with the formula automatically and the following result will be shown up.
Method-5: Creating Data Table Using Format as Table
Step-01: Select the whole data table and then follow Home Tab>>Styles Group>>Format as Table and then choose any of the styles you prefer.
Step-02:Then Create Table Dialog Box will appear and here click on My table has headers.
Step-03:After that, a table will be created where you have to just write a formula in the first row of Total Balance.
Step-04: After pressing ENTER the remaining rows of Total Balance will be filled with the formula automatically and the following result will be shown up.
Conclusion
In this article I tried to cover the easiest ways to make a data table in Excel. Feel free to comment if anything seems difficult to understand. If You know any other ways feel free to share with us. Thank you.
