**How to Create a** **Data Table**** in Excel**

Using 1 & 2 Variables

**How to Create a**

**Data Table**

**in Excel**

Using 1 & 2 Variables

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.

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 scenariosbyshowing the results in a table. Because it’s in a table form, you caneasily 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.

**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 a*dding: *

- 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**

**from the options.**

*‘Data Table’*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’ functiontocalculate the results in the data table. This function uses anarray formulawhich 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.