How to Make a Data Table in Excel (4 Effective Ways)

If you are looking for some easiest ways how 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. Moreover, with the help of a data table, you can try various input values in formulas to see the changes of those values as the effect of formula’s output.
So, today, in this article, I’m going to show you how to make a data table in Excel.
Furthermore, for conducting the session, I will use Microsoft 365 version.
Now, let’s get into the main article.

Download Practice Workbook

You can download the practice workbook from here:

4 Easy Ways to Make a Data Table in Excel

Here, 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.
So, the formula of the total balance is,

Here, we will see the changes in total balance with the change of variables like Investment, Rate of interest by using a data table in Excel easily.

Method-1: Use of Data Table Feature to Make a Data Table in Excel

In this section, I will make two types of data tables using the Data Table feature. In addition, one data table will be with one variable, and the other will be with two variables. Furthermore, for the first one, the variable will be only Investments, and for the second one, the variables will be both Investments and Rate of Interest. Below, I have shown you the detailed steps for both types.

1.1. Making a Data Table with One Variable

Here, you can make a data table with one variable using the Data Table feature in Excel. Moreover, you can create column or row-oriented both types of data tables.

Case-01: Column-Oriented Data Table

Steps:

• 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.
• Then, you will have to link up the first row of Total Balance with the result in cell C8.

• Then you have to select the whole range E5:F10.
• After that, follow the Data tab >> Forecast group >> What-If Analysis >> Data Table.

• Subsequently, a Data Table dialog box will appear where you have to enter the input C4 with absolute reference in the Column input cell as it is a column-oriented data table.
• Then, press OK.

As a result, you will see all the Total Balances for different Investments.

Case-02: Row-Oriented Data Table

Steps:

• Firstly, you have to make a row for various Investment values and a row for the Total Balance where the output will be shown up. Here, we are taking one variable named Investment.
• Secondly, you will have to link up the first column of Total Balance with the result in cell C8.

• Then you have to select the whole range C10:H11 and follow the Data tab >> Forecast group >> What-If Analysis >> Data Table.
• So, the Data Table dialog box will appear where you have to enter the input C4 with absolute reference in the Row input cell as it is a row-oriented data table.
• Lastly, press OK.

Finally, the various results will be in the Total Balance row.

1.2. Creating a Data Table with Two Variables

In the case of two-variable data tables, both row-oriented and column-oriented data tables are combined. Now, follow the steps given below to create a two-variable data table.

Steps:

• At first, you have to link up a cell such as C11 with the Total Balance in D8.
• Then, immediately below and right side of C11 two types of variables; Investment and Rate of Interest will be written as shown below.

• Then, you have to select the data range C11:G15.
• After that, from the Data tab >> go to the Forecast group >> from What-If Analysis >> choose Data Table.

• Consequently, the Data Table dialog box will appear, where you have to enter the references in both the Row input cell (D6) and the Column input cell (D4).
• Lastly, press OK.

After that, the outputs will be as below.

Read More: How to Create One Variable Data Table in Excel (2 Examples)

Method-2: Applying Power Query to Make Data Table in Excel

Here, I will use Power Query to make the data table in Excel. Let’s see the steps below.

Steps:

• First, select the whole data table.
• Then, press Data tab >>and choose From Table/Range.

• Then, Create Table dialog box will appear, and here click on My table has headers.
• Moreover, make sure that the data range is selected.
• Subsequently, press OK.

After that, a new window, named Power Query Editor will pop up.

• Then, from the Close & Load tab >> select Close & Load.

As a result, you will see the following table in a new sheet.

On the other hand, another table will be created in our previous worksheet. In that table, you have to just write a formula in the first row of Total Balance.

• Then, in the E5 cell write down the following formula.

Here, [@Investment] is the invested amount, [@Year] is the time period, and [Rate of Interest] is the interest rate.

• After pressing ENTER the remaining rows of Total Balance will be filled with the formula automatically and you will get the following result.

Read More: How to Change Chart Data Range in Excel (5 Quick Methods)

Method-3: Using Format As Table Feature to Create Data Table

Here, I will create a data table using the Format as Table feature in Excel. So, follow the steps below.

Steps:

• Firstly, select the whole data table and then follow the Home tab >> Styles group >> Format as Table and then choose any of the styles you prefer.

• Then, the Create Table dialog box will appear, and here click on My table has headers.
• Additionally, make sure that the data range is selected.
• Consequently, press OK.

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

• So, in the E5 cell write down the following formula.

Here, [@Investment] is the invested amount, [@Year] is the time period, and [Rate of Interest] is the interest rate.

• After pressing ENTER, you will see the following result.

Read More: Data Table Not Working in Excel (7 Issues & Solutions)

Method-4: Employing Create Table Feature

You can have the values of the Total Balance for different Investments by using the Table feature also.

Steps:

• Firstly, select the data range.
• Secondly, go to the Insert tab >> Table feature.

At this time, the Create Table dialog box will open up.

• First, make sure that you have selected the data range.
• Then, click the My table has headers option.
• Finally, press OK.

After that, you will get the following table.

• Now, select the output cell E5.
• Then, type the following formula.

Here, [@Investment] is the invested amount, [@Year] is the time period, and [Rate of Interest] is the interest rate.

• Lastly, press ENTER.

So, you will get the Total Balance for different Investments.

Read More: How to Perform the What If Analysis with Data Table in Excel

How to Compare Multiple Results Using a Data Table in Excel

Suppose you have to compare two outputs now rather than using two variables as in section 1.2.

Steps:

• At first, you have to calculate Interest as another output in C9 by subtracting the Total Balance from Investment.
• Then, create two columns: Total Balance and Interest beside the Investment column.

• Now, the first row of the Total Balance and Interest columns have to be linked up with C8 and C9 cells respectively.

• Then, select the range E5:G10.
• After that, follow the Data tab >> Forecast group >> What-If Analysis >> Data Table.
• As a result, the 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.

Lastly, you will get the output below.

Read More: How to Add Data Table in an Excel Chart (4 Quick Methods)

Practice Section

Now, you can practice by yourself.

Conclusion

In this article, I tried to cover the easiest ways to make a data table in Excel. You can visit our website Exceldemy to learn more Excel-related content. Feel free to comment if anything seems difficult to understand. If you know any other ways feel free to share them with us.

Further Readings

• Scenario Analysis in Excel: A Guide with 2 Sample Cases + Template