How to Create a Data Table in Excel
Using 1 & 2 Variables

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

A data table is an Excel tool you can use to test different scenarios.

It’s one of the available What-If analysis tools in Excel, together with the ‘Scenarios’ and Goal Seek tools.

In this article, you’ll learn what a data table is and why it’s useful. Then, we’ll show you how to create the two types of data tables.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s start with defining what a data table is.

What is an Excel data table?

In general, What-If analysis tools allows you to change the values in some cells and test how the changes affect the computation or formulas on the worksheet.

Similarly, data tables help in testing out different scenarios by showing the results in a table. Because it’s in a table form, you can easily interpret and analyze the results.

Kasper Langmann, Co-founder of Spreadsheeto

As of now, there are only two (2) types of data table:

  • One-variable data table
  • Two-variable data table

A one-variable data table lets you see how one variable affects one or more formulas. Likewise, a two-variable data table lets you see how two variables affect the results.

Although a data table is only limited to a maximum of 2 input variables, you can actually test as many variables as you want.

Excel Tables and Data Tables

Excel tables and data tables are entirely different.

An Excel table will help you organize and manage data. Data tables, as described above, allow you to test out different scenarios.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

One Variable Data Table

Making a one-variable data table is easy.

Here’s a sample problem:

Suppose you’re into an RTW (Ready-to-wear) business. You ordered a bulk of clothes worth $140. You sold the pieces and added a 50% increase in the prices. From that alone, you earned $210.

Now, you would like to see how much you could possibly earn if you increase the price by 55%, 60%, 65%, and 70%.

data set for the data table exercise

When making a data table, the input cells should be in the same worksheet as with the data table.

Kasper Langmann, Co-founder of Spreadsheeto

The ‘Projected Profit’ (C6) is calculated by adding:

  • The product of multiplying the ‘No. of Bulks’ (C3) with the ‘Bulk Price’ (C4)
  • The product of multiplying the ‘Price Increase’ (C5) with the ‘Bulk Price’ (C4)

When translated to formula, that’s…

Projected Profit = (No. of Bulks x Bulk Price) + (Price Increase x Bulk Price)

To create a one-variable data table, enter the variable values in one column or row.

For example, we’ll place the ‘Price Increase’ values, which is our variable values, on column ‘B11:B14’.

variable cells on one column

Now, place the formula that would compute the goal or result on a cell a row above the variable values and one cell to the right. In short, place it on the upper right side of the table.

In this case, we would be linking the cell, ‘C10’, to the original dataset’s ‘Projected Profit’ formula in the cell ‘C6’

When linking, use the equal sign (=) like the one below:

referencing a cell to another cell in Excel

Then, select the data table including the variable values, the formula cell, and the blank cells.

selecting variable values, formula cells, and blank cells for data table

Click ‘Data’ from the tab list.

data from the tab list

On the ‘Forecast’ group, click ‘What-If Analysis’ and choose ‘Data Table’ from the options.

data table option on what-if analysis of forecast group

On the Data Table window, there are two possible inputs:

  • Row input cell
  • Column input cell

For our example, we’ll only be using the ‘Column input cell’.

Click the input form beside the ‘Column input cell’ (since we arranged them in a column) and click the cell which contains the default ‘variable’ value that’s being used in the ‘Projected Profit’ formula.

We used 50% so click cell ‘C5’.

Then, click ‘OK’.

entering reference cell on column input cell

Immediately, you’ll see the results on the blank cells as seen below (cells are formatted to currency):

results of running a one variable data table

That wasn’t so bad, right? 😊

Excel Data Table not working?

One possibility why your data table is not working is because you included unnecessary cells in the table when you selected it.

For example, here’s what would happen if you included the cells ‘Price Increase’ and ‘Projected’ in the selection:

error on data table

Check your formula and your references. If you got them wrong, you’ll surely get the wrong results in your data table.

Two Variable Data Table

Creating a two-variable data table in Excel is similar to making a one-variable data table.

For convenience, let’s use the same data set:

data set for the data table exercise

But now, besides the price increase, we would also like to know the projected profit if we order 2,3,4 and 5 bulks at once.

Because of that, we’ll be making a few changes to our data table.

First, we’ll be moving the formula cell on the upper left part.

Second, we’ll be adding a new set of rows and columns to give way to the second set of variables cell, the ‘No. of Bulks’.

new set of rows and columns for two variables data table

Let’s proceed into selecting the data set and opening the data table tool.

selecting data set and opening data table tool for two variable data table

Because we’re making a two-variable data table, we’ll now be using both parameters.

On the ‘Row input cell’, refer the cell which contains the default variable value used in the formula. The original value is ‘1’, so enter “C3”.

On the ‘Column input cell’, refer the cell which contains the default variable value used in the formula. The original value is ‘50%’, so enter “C5”.

Then, click ‘OK’.

referencing variable values for row input cell and column input cell

Here are the results:

results of running a two variable data table

Deleting a data table

Have you tried deleting a single value in the table?

Did you get an error similar to the one below?

error while deleting a data table

Excel uses the ‘TABLE’ function to calculate the results in the data table. This function uses an array formula which you can check in the formula bar.

Kasper Langmann, Co-founder of Spreadsheeto

Try clicking a cell with the result and you’ll see curly brackets {} surrounding the formula:

curly brackets surrounding an array formula

Because of that, you can’t edit or change a cell in the table individually. The only thing you can do is edit or delete the entire array of cells.

So, to delete the results, select the cells which contain the results and delete them.

deleting cells on data table

Editing the results of a data table

You can’t edit each result in a data table but you can change all of them into a single value.

To do so, select all the cells with the results and on the formula bar, delete the whole formula and enter your desired value.

For our example, let’s change all the values to “300”.

changing values on data table

Because you’re dealing with an array formula, press ‘Ctrl’ + ‘Enter’ to enter your value.

ctrl plus enter to replace value on data table

Wrapping things up…

A data table is a time saver. Whenever you have different scenarios for your data set, you don’t have to do them separately.

If you’re having trouble understanding how to create a data table, download the exercise file and test it out yourself.

Kasper Langmann, Co-founder of Spreadsheeto