How to Make a Data Table in Excel: Step-by-Step Guide (2024)

Data tables in Excel are used to perform What-if Analysis on a given data set.

Using data tables, you can analyze the changes to the output value by changing the input values to a formula.

There is so much that you can do using data tables in Excel. 😀

Continue reading the article below to learn it all.

Also, download our sample workbook here to practice the examples given in this guide.

What is an Excel data table?

An Excel Data table is a What-if Analysis tool. It allows users to use different input values for a variable and assess the changes to the output value.

These are especially of help if you are operating a formula in Excel where the output depends on several variables. And you are keen to compare the results for different inputs to the formula.

Presently, Excel offers a one-variable and two-variable data table only. This means you can choose any two variable values (at max) from any formula to test.

Jump right into the article below to learn all about a data table in Excel. 🔔

How to create a one-variable data table in Excel

A one-variable data table in Excel allows users to test one variable.

For example, see the image below.

The image shows the particulars of a loan. We have three main variables in the data.

1. The amount of loan
2. The rate of interest/profit
3. The tenure of the loan (until it is paid back)

Example 1: Column Input Cells

In this example, let’s see keep the interest rate as the variable.

What is the yearly payment to be made against the loan?

1. Write the PMT function to find the yearly repayment against the loan.

= PMT (B3, B4, B2)

= PMT (Interest Rate, Periods of Repayment, Amount of Loan Today)

2. Multiply this number by the number of payments to be made.

That’s the total amount to be paid against the loan over 5 years.

So how much is the interest on the loan?

3. Subtract the amount of loan from the amount of repayment.

Everything’s good and sorted.

Now, what if you want to see how the repayments change if one variable (the interest rate) changes?

Do not re-perform the entire calculation all over again. The Data Table (What-if analysis) will do it for you.

4. List down the variable (interest rate in this case) that is to be changed.

5. Create a link by referring to the targeted output for each interest rate in the corresponding column.

We want Excel to give us the repayments for different interest rates. So, we have created a link to the repayment in the original calculation.

6. Select the Inputs table (the interest rates and the corresponding column for targeted output).

7. Go to Data Tab > Forecast > What-If Analysis Tools > Data Table.

This will take you to the Data Table dialog box.

8. In the Column Input Cell box, create a reference to the ‘Interest Rate’ from the original table.

Reference is made to the Interest rate because that is the variable in our data. We want to experiment with how the changing interest rates affect repayments.

9. All set. Hit Okay and Ta-da! 😃

Excel creates a one-variable data table to calculate the repayments for different interest rates.

Example 2: Row Input Cells

Let’s bring a slight variation to the above data. This time the one variable of the data is the amount of the loan.

Also, let’s change the shape of Input Data from a Column to a Row.

1. Select the Inputs Data.

2. Go to Data Tab > Forecast > What-If Analysis Tools > Data Table.

3. In the ‘Data Table’ dialog box, create a reference to the Loan amount in the Row Input Cell box.

This time the variable is the amount of the loan. We want to experiment with how the changing loan amount affects the repayments.

4. Click ‘Okay’ to see the repayment amount for differing amounts of loans.

What if we want to see how the total interest changes by the change in the loan amount?

Simple, refer to the amount of interest in the Inputs Data.

And there it is! Excel shows the changes to total interest instead of repayments.

How to create multiple one-variable data tables?

In the above example, what if you want to see the change in interest rates on both the repayments and total interest?

Create multiple Excel data tables. Simple.

1. In the Input Data, make two columns next to the variable interest rates.

2. In the first column, create a reference to the repayment calculation in the original data.

3. In the second column, create a reference to the total interest in the original data.

4. Create a one-variable data table by referring to the interest rate in the Column Input Cell box.

5. Click Okay, and there you go! 🙂

Excel shows the result of changes in interest rates on repayments and loan amounts.

How to make a two-variable data table in Excel?

The two-variable data table is more of a two-dimensional table. It allows you to analyze how your final output changes from the changes in any two variables of your data.

Let’s continue the example above to create a two-variable data table in Excel.

This time, let’s select two variables from the data, Interest Rate, and Loan Amount. We want to see how the repayments change when both these variables change.

1. Create a two-dimensional data table with each variable on one side of the table.

In the above image, we have set the interest rates in a columnar format. Whereas the loan amount takes the shape of a row.

2. Select the intersecting cell of both the data sides.

3. In this cell, create a reference to the calculation of the repayment in the original table.

This is because we want to see how the repayments change with changes in the interest rate and the loan amount.

Our Input Data is now ready. Let’s now create a data table and perform the What-if Analysis.

4. Select the entire Data Table.

5. Go to Data Tab > Forecast > Click What-if Analysis Tools > Data table.

6. This opens up the data table dialog box.

7. Against the Column Input Cell box, create a reference to the interest rate from the source data.

Pay attention to how a reference is created to the interest rate against the Column Input Cell. This is because the possible input values for interest rate (the first variable) are in the shape of a column.

8. Against the ‘Row Input Cell’, create a reference to the amount of loan from the source data.

The Row Input Cell refers to the amount of the loan. This is because possible input values for the loan (second variable) are in the shape of a row.

9. Click ‘Okay’, and you’re good to go.

Woah! This seems like a very densely packed data table.

What is this? See below.

Each cell of this data table is mutual to two cells. For example, in the image above, the highlighted cell shows the amount of repayment, if the interest rate changes to 12% and the loan amount changes to 2000.

Must Note: A two-variable data table is a two-dimensional table. It captures the result of the change in any two variables at the same time.

Trouble Shooting the Two-Variable Data Table

The Two-Variable data table in Excel seems no less than magic. A heap of calculations is only a click away.

A two-variable data table is an array, and there is something you must know about a table array.

1.    Editing a two-variable data table

Once you have created a two-variable data table, try clicking on any individual cell from the data table and making some changes to it.

You cannot make changes to a part of this data! This is all that Excel has to say in return.

A data table is an array, and you cannot make changes to individual cells of an array.

To make any changes to the data table, click the data table and select the whole of it.

1. From the formula bar, delete the Table formula.

2. Type in the desired value (let’s say 10) and hit Ctrl + Enter.

3. The entire table will be replaced by 10.

You can now make changes to any individual cells as it is no more an array.

2.    Deleting a two-variable data table

Deleting two-variable data is a little science.

You cannot delete an individual value from the data table. However, you can only delete the whole data table.

1. Select the entire array (whole data table).
2. Press the delete key.
3. And your data table is gone.

That’s it – Now what?

Data Tables can save you big on time.

In the article above, we have learned almost all about data tables in Excel – starting from creating a single-variable data table, and multiple single-variable data tables (in one go) to creating multiple-variable data tables.

And of course, many tips.

While data tables help data analysis in Excel, you’d need many other functions of Excel to handle big data sets in Excel. The most important of these include the VLOOKUP, SUMIF, and IF functions.

Learn each of these three functions by signing up for my free 30-minute email course that teaches you these functions (and more!).

Other relevant resources:

If you liked reading about Data Tables, you’d also be interested to know about similar functions.

If yes, we suggest you read about Scenario Tables, Data Validation & Goal Seek in Excel.