How to Make a Data Table in Excel (Easiest 5 Methods)

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.

data table

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.

column-oriented data table

Step-02: Then you have to select the whole range E4:F9 and follow Data Tab >> Forecast Group >> What-If Analysis >> Data Table

column-oriented 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.

column oriented data table

Step-04: After that, the various results will be shown up in the Total Balance column.

how to make a data table in Excel

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.

row-oriented data table

Step-02:Then you have to select the whole range C9:H10 and follow Data Tab >> Forecast Group >> What-If Analysis >> Data Table

row-oriented 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.

row-oriented data table

Step-04: After that, the various results will be shown up in the Total Balance row.

how to make a data table in Excel

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.

two variable data table

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.

data table dialog box

Step-03: After that, the outputs will be shown up as below.

how to make a data table in Excel

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.

comparing multiple results

Step-01: At first, the first row of the Total Balance and Interest columns have to be linked up with C7 and C8 respectively.

comparing multiple results

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.

data table dialog box

Step-03: After that, the outputs will be shown up as below.

comparison of multiple results


Similar Readings:


Method-4: Creating Data Table Using Power Query

Step-01: Select the whole data table and press Data Tab>>From Table/Range

power query

Step-02: Then Create Table Dialog Box will appear and here click on My table has headers.

create table dialog box

Step-03: After that, a table will be created where you have to just write a formula in the first row of Total Balance.

power query

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.

how to make a data table in Excel

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.

format as table

Step-02:Then Create Table Dialog Box will appear and here click on My table has headers.

create table dialog box

Step-03:After that, a table will be created where you have to just write a formula in the first row of Total Balance.

formula in table

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.

how to make a data table in Excel

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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo