How to Create a Data Table in Excel (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to create a data table in excel


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.

how to create a data table in excel

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

one variable column-oriented

Step-02:
➤ Select the range B5:E9.
➤ Go to Data tab >> Forecast group >> What-If Analysis drop-down >> Data Table option.

one variable column-oriented

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.

one variable column-oriented

Result:
In this way, you will get the Total Balance values for different Investments.

how to create a data table in excel

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.

how to create a data table in excel

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

one variable row-oriented

Step-02:
➤ Select the range C4:G7.
➤ Go to Data tab >> Forecast group >> What-If Analysis drop-down >> Data Table option.

one variable row-oriented

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.

one variable row-oriented

Result:
After that, you will get the Total Balance values for different Investments.

how to create a data table in excel

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.

how to create a data table in excel

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

two variable

Step-02:
➤ Select the range B10:F14.
➤ Go to Data tab >> Forecast group >> What-If Analysis drop-down >> Data Table option.

two variable

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.

two variable

Result:
Then, you will get the Total Balance values for different Investments and Rates of interest.

how to create a data table in excel

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.

how to create a data table in excel

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

multiple results

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.

multiple results

Step-02:
➤ Select the range B5:F9.
➤ Go to Data tab >> Forecast group >> What-If Analysis drop-down >> Data Table option.

multiple results

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.

multiple results

Result:
In this way, you will get the Total Balance and Interest values for different Investments and Rates of interest.

how to create a data table in excel


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.

how to create a data table in excel

Step-01:
➤ Go to Home tab >> Format as Table option >> Choose your preferred style.

Format as Table

Then Create Table dialog box will appear.

➤ Select the data range.
➤ Click on the My table has headers option.
➤ Press OK.

Format as Table

After that, you will get the following table.

Format as 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.

Format as Table

➤ Press ENTER.

Result:
Afterward, you will get the Total Balance for different Investments.

how to create a data table in excel


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.

how to create a data table in excel

Step-01:
➤ Go to Insert tab >> Table option.

Table

Then, the Create Table dialog box will open up.

➤ Select the data range.
➤ Click the My table has headers option.
➤ Press OK.

Table

After that, you will get the following table.

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.

Table

➤ Press ENTER.

Result:
Then, you will get the Total Balance for different Investments.

how to create a data table in excel


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.

how to create a data table in excel

Step-01:
➤ Go to Data tab >> FromTable/Range option.

Power Query

Then, the Create Table dialog box will open up.

➤ Select the data range.
➤ Click the My table has headers option.
➤ Press OK.

Power Query

After that, you will get the following table.

Power Query

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

Power Query

➤ Press ENTER.

Result:
After that, you will be able to get the Total Balance for different Investments.

how to create a data table in excel

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.

practice


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo