Excel data tables are a useful way to show how calculated results depend on one or two variables. There are two types of data tables: one-way data tables which have only one variable changing and two-way data tables which have two variables changing.  Let’s find out how to create each type.

One-Way Data Tables

A one-way data table has only one variable changing and hence it is sometimes called a one-input data table.  The trade-off for having only one variable changing is that several calculations can be shown in the results.

Create one-way data table using column input

1. Set up your Excel sheet so that each variable is entered in a different cell and your results are calculated using these variables.
2. Enter the various values for the input variable that you want to change into a column, leaving a couple of rows spare above it for headings and the first row of the data table.  The data table must be on the same sheet as the information in step 1. (Alternatively, you could enter the input variable in a row – I will show this to you at the end of this section.)
3. In the first row above the column of input variables and in the next column, enter a formula to point to one of the calculations that depends on this input variable.  For example, in the screenshot above my input variables are in rows 24 to 29 in column A so I put my formula in cell B23 (next row above and one column to the right).  In this case, my formula is “=Sub_Total”, where I have named my ranges.  Alternatively, you could use the formula “=B18”.
4. If you want to enter other calculations, then put them in the same row as step 3 but in the next column(s) along.  For example, in cell C23 I entered “=Total_Cost”.
5. Optional: Enter headings and titles to explain what information will be in the data table.
6. Select the range to include the column in step 2 and all the formulas in steps 3 and 4.  In the example above, select A23:C29.  Do not select any headings from step 5 as these are not part of the data table.
7. Select the Data tab, select What-if Analysis in the Data Tools group and then select Data Table… from the drop-down list.
   The Data Table dialog box displays.
   

   Point to the input variable in your calculation

8. In the Column input cell field, enter a reference to the input variable that is changing in your calculation.  Leave the Row input cell field blank. In the example above, the power used is changing. I have named cell B12 as Power_Used so I can enter either “Power_Used” or “B12” in this field.
9. Click OK.
   The dialog box closes and the one-way data table shows the results of the calculations as the input variable changes.
   

   The one way data table is created

10. Optional: Format the data table. You may want to hide the row used in steps 3 and 4, e.g. hide row 23 above.  You may also want to format the numbers.  In the example above, column B is unformatted (i.e. if you apply no number formatting) whereas column C is formatted for currency. In practice, I would format both columns!

It is also possible to create a one-way data table where the possible values are entered in a row instead of a column.

How to create a one-way data table where the variable is in a row

The  process is very similar to that for a column except the rows and columns are swapped around:

• In step 2 enter the various values for the input variable into a row.
• In steps 3 and 4, enter the references to the calculations in the rows below this, but in the column to the left.
• In step 8, enter a reference to the input variable in the Row input cell field.

Two-Way Data Tables

A two-way data table has two variables changing and hence it is sometimes called a two-input data table.  The trade-off for having two variables changing is that only one calculation can be shown in the results.

In the example below we calculate the repayments for a mortgage.

Create a two-way data table

1. Set up your Excel sheet so that each variable is entered in a different cell and your results are calculated using these variables. In the example above, Excel’s PMT function is used to calculate the loan payments (principal plus interest):
   

   PMT function

2. Enter the various values for the first input variable into a row, leaving the first two columns spare.
3. Enter the various values for the second input variable into the column to the left of the values in step 2, starting at the row below step 2.
4. At the intersection of steps 2 and 3, enter a reference to the calculation that you want to show in the data table.  In the example above, the intersection of row 16 and column B is simply B16.  For the reference to the calculation I entered
   “=Payment_Amount” as I named the range B9.  Alternatively, you could enter
   “=B9”.
5. Optional: Enter headings and titles to explain what information will be in the data table.
6. Select the range to include the row in step 2 and the column in step 3.  In the example above, select B16:M25.
7. Select the Data tab, select What-if Analysis in the Data Tools group and then select Data Table… from the drop-down list.
   The Data Table dialog box displays.
   

   Data Table dialog box

8. In the Row input cell field enter a reference to the variable changing in step 2 above.  In the Column input cell field enter a reference to the variable changing in step 3 above.
9. Click OK.
   The dialog box closes and the two-way data table shows the results of the calculation as the input variables change.
   

   Two-way data table created

10. Optional: Format the data table.

Closing Comments

Enjoy showing the results of your calculations as key variables are changed using either a one-way or two-way data table.

If you want to vary more variables than this, then try the Scenario Manager.  I will leave this for another day.