How to Create a Data Table in Excel 2010

(1)

How to Create a Data Table in Excel 2010

Nicole Bernstein

Introduction

Excel 2010 is a useful tool for data analysis and calculations. Most college students are familiar with the basic functions of this software, yet many are unaware of the existence of data tables—a time-saving analysis feature contained in Excel. This instruction set will define data tables and their use, and will walk through a general example of how to create a two-variable data table. These instructions are designed for users comfortable with basic aspects of Excel, such as inputting data and creating formulas. All necessary terms will be defined throughout the instruction set, as applicable.

A data table is a feature in the “what-if” analysis section of Excel commands. “What-if”

commands analyze how a change in the value of one cell will affect the results of other formulas contained in the Excel worksheet. Data tables specifically allow the user to look at two separate variables in a formula, and see how different combinations of values for those two variables will affect the solution of the formula. For example, if you were interested in looking at how different interest rates and initial loan amounts affect your monthly loan payment, you could use a two- variable data table to see how the monthly payment differs between an initial loan of $10,000 versus $100,000, or between interest rates of 1% and 6%—and any value in between. Using a data table saves the Excel user from having to enter a new formula each time he or she wants to examine the result of the formula for new variables.

This document will walk through a general example of a two-variable polynomial function to illustrate how to use the data table function. The instructions are separated into two primary sections. The first section describes how to input all of the initial conditions—such as constant variables and changing variables—for the specific formula you would like Excel to analyze. The second section provides instructions on creating the data table from the input information. Upon completion of these instructions, you will have generated a two-variable data table that will analyze a function for a range of different values. This activity should take around 5-10 minutes to

complete, and requires only a computer equipped with Microsoft Excel 2010.

(2)

Inputting the Initial Conditions of your Formula

Step 1

Open a blank Excel worksheet.

Step 2

On paper or in Excel, write down the function you are interested in examining.

In this general example, we use a simple polynomial function (shown below).

Step 3

For your formula, decide which two variables will be able to change. We will call these changing variables.

Here we have chosen to change x and y. Changing variable 1 (x) is shown in blue. Changing variable 2 (y) is shown in green.

Step 4

All variables other than the two changing variables chosen in Step 3 will be constants. For your formula, list all constants. We will call these constant variables.

The constant variables for this example formula are shown in red.

(3)

Step 5

In Column B of the spreadsheet, type the letter of each constant variable you listed in Step 4 into a different cell. In Column C, list the numerical value of each constant variable next to its letter.

In this example, we have randomly assigned each constant variable a numerical value. For your calculations, you will know values for these constant variables.

Step 6

Below the constant variable letters in Column B, type the letters of your two changing variables. In Column C, list a numerical value for each of the changing variables next to its letter. This value can be completely random, and will not affect the results of the data table.

Constant Variables

Changing Variables Constant Variables

(4)

Creating a Data Table

Step 7

Type your function into cell F6 by referencing the Column C cells you created in Steps 5 and 6 (cells highlighted below in yellow). The formula will output a single numerical result.

If you are unfamiliar with creating formulas in Excel, more information is available on Microsoft’s webpage—this topic is beyond the scope of these instructions.

Step 8

In Row 6—directly to the right of the formula cell created in Step 7—insert in separate cells all of the numerical values for changing variable 1 (x) that you would like Excel to consider in your data table. We will call changing variable 1 the row variable.

These values are not required to be integers, and the list can be as long or as short as you desire.

(5)

Step 9

In Column F—directly beneath the formula cell created in Step 7—insert in separate cells all of the numerical values for changing variable 2 (y) that you would like Excel to consider. We will call changing variable 2 the column variable.

Step 10

Click on the formula cell, and drag your mouse until the entire region of cells, including the formula cell (F6) and all row and column values are highlighted (see visual).

Constant Variables

Changing Variables

(6)

Keeping the range of cell highlighted, (1) click the “Data” tab in the title bar, (2) select “What-If Analysis”, and (3) select “Data Table.”

After you select data table, a window will pop up asking for two input values: row input cell and column input cell.

Step 12

For “row input cell,” select the cell in Column C that is directly next to the letter of your row variable.

In this case, that variable is x, and the row input cell is indicated by the red arrow below.

1 1

2 1 3 1

(7)

Step 13

For “column input cell,” select the cell in Column C that is directly next to the letter of your column variable.

In this case, that variable is y, and the column input cell is indicated by the red arrow below.

Step 14

Select “OK” to generate the data table. Each highlighted cell will generate a value that is the solution to the formula cell, should the x-value of its column and the y-value of its row be substituted into the formula. You have now created a data table.

Changing Variables Constant Variables Changing Variables Constant Variables

(8)

Conclusion

You have now learned how to create a two-variable data table in Excel. This feature can be applied to any equation of your choice, in order to examine its behavior as two of its variables change concurrently. Now that you have learned how to complete this simple task, feel free to explore the other data analysis tools that Excel 2010 has to offer, such as Goal-Seek and Scenario Manager!