In this article, you will have an idea about how to create a data table in Excel easily. Creating a data table in Excel is an important feature of Excel because it will make the calculation easier and faster, furthermore, you can change any type of calculation easily by changing input data.

So, letâ€™s get into the main article.

**Watch Video â€“ Create Data Table in Excel**

## How to Create a Data Table in Excel: 7 Ways

Here, we have used the following table to demonstrate the ways of creating a data table in Excel.

For creating the article, we have used the *Microsoft 365* version.

__Method-1__: Creating One Variable Column-Oriented Data Table

Here, we will use one variable, *Investment*, which is oriented column-wise for creating a data table and having the output *Total Balance *easily with the help of this table.

** Step-01**:

âž¤ At first, you have to get the

*Total Balance*in cell

**E5**by using the following formula.

`= B5+B5*C5*D5`

Here, **B5 **is the *Investment*, **C5 **is the *Year *and **D5 **is the *Rate of Interest.*

**B5*C5*D5**becomes 5000*5*0.04

**Output**â†’ $1,000.00

**B5+B5*C5*D5**becomes 5000+1000

**Output**â†’ $6,000.00

** Step-02**:

âž¤ Select the range

**B5:E9**.

**âž¤ Go to**

**Data**tab

**>>**

**Forecast**group

**>>**

**What-If Analysis**drop-down

**>>**

**Data Table**option.

After that, a **Data Table** dialog box will appear.

âž¤ Select the first cell of the **Investment **column (as you have used this value as a variable for getting *Total Balance*) as the **Column input cell **option.

âž¤ Press **OK**.

** Result**:

In this way, you will get the

*Total Balance*values for different

*Investments*.

**Read more:**Â How to Create One Variable Data Table in Excel

__Method-2__: Creating One Variable Row-Oriented Data Table

You can use a row-oriented variable, *Investment*, for creating a data table and having the output *Total Balance *easily with the help of this table. Here, we have changed the pattern of the table from column-wise to row-wise.

** Step-01**:

âž¤ Use the following formula to get the total balance in cell

**C7**.

`=C4+C4*C5*C6`

Here, **C4 **is the *Investment*, **C5 **is the *Year *and **C6 **is the *Rate of Interest.*

**C4*C5*C6**becomes 5000*5*0.04

**Output**â†’ $1,000.00

**C4+C4*C5*C6**becomes 5000+1000

**Output**â†’ $6,000.00

** Step-02**:

âž¤ Select the range

**C4:G7**.

**âž¤ Go to**

**Data**tab

**>>**

**Forecast**group

**>>**

**What-If Analysis**drop-down

**>>**

**Data Table**option.

Then, a **Data Table** dialog box will appear.

âž¤ Select the first cell of the *Investment* row (as you have used this value as a variable for getting *Total Balance*) as the *Row input cell* option.

âž¤ Press **OK**.

** Result**:

After that, you will get the

*Total Balance*values for different

*Investments*.

**Read More: **How to Create One Variable Data Table Using What-If Analysis

__Method-3__: Creating Two-Variable Row-Oriented and Column-Oriented Data Table

Now, we will create a data table for two variables like *Investment *and *Rate of Interest *(we have changed the values in the *Rate of Interest* column for this method), and for the changes of these two variables, we will see the changes in the total value.

For creating the table, we made an outline where the *Rate of interest *values are arranged row-wise and *Investments* are arranged column-wise. Then, we will get the value of the *Total Balances *in the blank cells using these two variables.

** Step-01**:

âž¤ Use the following formula to get the total balance in cell

**B10**.

`=B4+B4*C4*D4`

Here, **B4 **is the *Investment*, **C4 **is the *Year *and **D4 **is the *Rate of Interest.*

**B4*C4*D4**becomes 5000*5*0.04

**Output**â†’ $1,000.00

**B4+B4*C4*D4**becomes 5000+1000

**Output**â†’ $6,000.00

** Step-02**:

âž¤ Select the range

**B10:F14**.

**âž¤ Go to**

**Data**tab

**>>**

**Forecast**group

**>>**

**What-If Analysis**drop-down

**>>**

**Data Table**option.

Then, a **Data Table** dialog box will appear.

âž¤ Select the first cell of the *Rate of Interest *column (as you have used this value as a variable for getting *Total Balance*) as the **Row input cell **option (the values of *Rate of Interest *are in the row-wise direction) and similarly choose the first cell of the *Investment* column as the **Column input cell **option (the values of *Investment* are in the column-wise direction).

âž¤ Press **OK**.

** Result**:

Then, you will get the

*Total Balance*values for different

*Investments*and

*Rates of interest*.

**Read More:** How to Create a 4-Variable Data Table in Excel

__Method-4__: Having Multiple Results in a Data Table

You can get multiple results like the values of the *Total Balance *and *Interest* for different *Investments *at once by using a data table.

** Step-01**:

âž¤ Use the following formula to get the total balance in cell

**E5**.

`=B5+B5*C5*D5`

Here, **B5 **is the *Investment*, **C5 **is the *Year *and **D5 **is the *Rate of Interest*

**B5*C5*D5**becomes 5000*5*0.04

**Output**â†’ $1,000.00

**B5+B5*C5*D5**becomes 5000+1000

**Output**â†’ $6,000.00

For having the value of *Interest *in cell **F5 **use the following formula.

`=E5-B5`

Here, **E5 **is the *Total Balance*, **B5 **is the *Investment.*

** Step-02**:

âž¤ Select the range

**B5:F9**.

**âž¤ Go to**

**Data**tab

**>>**

**Forecast**group

**>>**

**What-If Analysis**drop-down

**>>**

**Data Table**option.

Then, a **Data Table** dialog box will appear.

âž¤ Select the first cell of the **Investment **column (as we have used this value as a variable for getting *Total Balance*) as the **Column input cell **option.

âž¤ Press **OK**.

** Result**:

In this way, you will get the

*Total Balance*and

*Interest*values for different

*Investments*and

*Rates of interest*.

__Method-5__: Using Format as Table Option to Create a Data Table in Excel

In this section, we will create a table using the **Format as Table **option.

** Step-01**:

âž¤ Go to

**Home**tab

**>>**

**Format as Table**option

**>>**Choose your preferred style.

Then **Create Table** dialog box will appear.

âž¤ Select the data range.

âž¤ Click on the **My table has headers** option.

âž¤ Press **OK**.

After that, you will get the following table.

âž¤ Select the output cell **E5**.**
**âž¤ 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.

âž¤ Press **ENTER**.

** Result**:

Afterward, you will get the

*Total Balance*for different

*Investments*.

__Method-6__: Using Table Option to Create a Data Table in Excel

You can get the values of the *Total Balance *for different *Investments *by using the **Table **option.

** Step-01**:

âž¤ Go to

**Insert**tab

**>> Table**option.

Then, the **Create Table **dialog box will open up.

âž¤ Select the data range.

âž¤ Click the **My table has headers **option.

âž¤ Press **OK**.

After that, you will get the following table.

âž¤ Select the output cell **E5**.**
**âž¤ 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.

âž¤ Press **ENTER**.

** Result**:

Then, you will get the

*Total Balance*for different

*Investments*.

__Method-7__: Using Power Query to Create a Data Table in Excel

Here, we will try to have the values of the *Total Balance *for different *Investments *by using the *Power Query* Option.

** Step-01**:

âž¤ Go to

**Data**tab

**>>**

**FromTable/Range**option.

Then, the **Create Table **dialog box will open up.

âž¤ Select the data range.

âž¤ Click the **My table has headers **option.

âž¤ Press **OK**.

After that, you will get the following table.

âž¤ Select the output cell **E5**.**
**âž¤ 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.

âž¤ Press **ENTER**.

** Result**:

After that, you will be able to get the

*Total Balance*for different

*Investments*.

**Read More:** How to Create Data Table with 3 Variables

## Things to Notice

🔺 Any single cell of the array can not be deleted or edited as the table is created with an array formula.

🔺 Be aware of referencing the **Row input cell** or **Column input cell **according to the pattern of your table.

🔺 The input cells have to be on the same sheet of the data table.

## Practice Section

For doing practice by yourself we have provided a practice section like below in a sheet named **Practice**. Please do it by yourself.

**Download Workbook**

## Conclusion

In this article, I tried to cover the easiest ways to create a data table in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

**<< Go Back to Data Table in Excel | What-If Analysis in ExcelÂ |Â Learn Excel**