# How to Create a Data Table in Excel 2010

N/A
N/A
Protected

Share "How to Create a Data Table in Excel 2010"

Copied!
8
0
0

Full text

(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)

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

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

Changing Variables Constant Variables

Changing Variables Constant Variables

(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).

Changing Variables Constant Variables

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

Changing Variables Constant Variables

Changing Variables Constant Variables

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!

References

Related documents

A step by step guide to install, apply fix packs and configure WebSphere Message Broker V6.1 with its subcomponents WebSphere MQ V6.0 and DB2 V9.1 for

End of how prepare statement excel format in this template was helpful to know that in excel with stamped salary calculation on your employees will take tax.. Mortgage debts like me

5.1 Allocation of Difference between 5.7 Complete Equity Method – Workpaper Entries Implied and Book Values: Acquisition Date 5.8 Complete Equity Method – Consolidated Net

14 016 Assisting with and/or supervising tasks of daily life to develop the skills of the individuals with lower support needs to live as autonomously as possible.. The benchmark

If you have created an export solution to format your search results, choose the Excel or Word button to open a list of export solutions.. Choose the Quick Export option to generate

You fill in the data sources and format is to a form that excel exports to create a single excel to your computer science in order data from.. You just need to click the ok button

Bude tu uvedená základná terminológia projektového managementu: projektový management, projekt, cieľ, riziko atď., následne budú popísané analýzy projektu: časová

Based on the results of research and discussion presented here, the proposed HOTS instruments can be used to measure HOTS of high school students in mathematics instruction,