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,

`Total Balance= Investment +Investment*Year*Rate of interest`

`Total Balance = C3+(C3*C4*C5)`

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.

`=[@Investment]+[@Investment]*[@Year]*[@[Rate of Interest]]`

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.

`=[@Investment]+[@Investment]*[@Year]*[@[Rate of Interest]]`

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.

`=[@Investment]+[@Investment]*[@Year]*[@[Rate of Interest]]`

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.