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

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

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.

## 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%. When making a data table, the input cells should be in the same worksheet as with the data table.

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’. 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: Then, select the data table including the variable values, the formula cell, and the blank cells. Click ‘Data’ from the tab list. On the ‘Forecast’ group, click ‘What-If Analysis’ and choose ‘Data Table’ from the options. 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’. Immediately, you’ll see the results on the blank cells as seen below (cells are formatted to currency): 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: 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: 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’. Let’s proceed into selecting the data set and opening the data table tool. 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’. Here are the results: ## Deleting a data table

Have you tried deleting a single value in the table?

Did you get an error similar to the one below? 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.

Try clicking a cell with the result and you’ll see curly brackets {} surrounding the 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. ## 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”. Because you’re dealing with an array formula, press ‘Ctrl’ + ‘Enter’ to enter your value. ## 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.