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.

**Table of Contents**hide

## 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__

**: At first you have to make a column for various**

__Step-01__**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__

**: Here, you have to make a row for various**

__Step-01__**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 One Variable Data Table in Excel (2 Examples)**

__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 Change Chart Data Range in Excel (5 Quick Methods)**

__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.

**Read More:** **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.

**Read More:** **How to Perform the What If Analysis with 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.

** 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.

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

## 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.