How Do I Create a Two Way Data Table in Excel 2007?

Save

A two-way data table, also called a two-variable data table, is a tool used in Excel to perform a 'what-if' analysis on the effects of changes in any two variables on the output of a formula. This eliminates the need to re-type variables in formula-referenced cells numerous times to see the outputs of various possibilities, or to copy and paste a single formula many times in reference to different variable combinations.

Creating a Two-Way Data Table

  • After deciding which variables and formula to test, list all the values of your first variable vertically, from top to bottom, in any column, and all the values of second variable horizontally, beginning one column above and one row to the right of the first value of your first variable.

    Create or copy your desired formula immediately above the first value of the first variable. Your formula may reference any information that you've entered in other cells, depending on the purpose of your data analysis.

    Highlight all cells enclosed by your horizontal and vertical variable lists, then click 'Data -> Data Tools -> What-If Analysis -> Data Table' to access the data table dialog box.

    Enter the input cell for the first variable in the 'column input cell' box, and the input cell for the second variable in the 'row input cell' box. The input cell will be the cell location of the value that you wish to be replaced by each of the respective variables in the what-if analysis.

    Click 'OK' in the data table dialog box, and Excel 2007 will fill in all the cells between your two variable lists with the corresponding outputs of the given formula based on the variables listed in the specific output cell's column and row.

Example

  • As a simple example, to compare the effects of different combinations of three possible unit costs and sales prices on your company's gross margin, you could list the three possible costs vertically, and the three possible sales prices horizontally.

    In the example, you would already have an income statement prepared on the same worksheet that lists values for cost, price, unit sales, and gross margin. You would then copy, or retype, the formula from the original gross margin cell directly above the first value for possible costs in your table, since this is the value that you wish to monitor.

    In the data table dialog box, the column input cell would be the location of the original 'cost' value in the previously created income statement. The row input cell would be the location of the original 'sales' price value. After clicking 'OK', you would then be able to see the gross margin values for all nine combinations of unit cost and sales price at a glance.

Considerations

  • According to faculty.fuqua.duke.edu, an XY chart is a great way to visually spot trends and areas of opportunity brought to light in your data table, since this type of chart can use two-variable combinations to chart single points. To create an XY chart, first highlight the group of cells in your table that you wish to chart, then click 'Insert -> Charts -> Scatter'.

References

  • Photo Credit Pixland/Pixland/Getty Images
Promoted By Zergnet

Comments

Resources

You May Also Like

  • How to Create an Excel Table

    Excel is a software program used for creating spreadsheets. These spreadsheets can be used to organize data and perform calculations. Excel includes...

  • How to Use Data Tables in Excel

    Excel tables are designed so that you can see how different inputs affect the same formula. If you want to use a...

  • How to Calculate ANOVA on a Table

    The type of statistical test used to analyze the results from designs with one independent variable and more than two levels or...

  • How to Create a Table in Excel 2003

    Tables in Microsoft Excel 2003 not only help users organize their data but calculate it as well. Excel tables can automatically compute...

  • How to Use Excel for ANOVA

    Statistics classes teach the Analysis of Variance (ANOVA) as a type of test to determine statistical correlation. Normally statistics used specialized software...

  • How to Interpret an SPSS ANOVA Output

    ANOVA is short for the statistical test Analysis of Variance. When you are comparing two groups, such as a treatment and a...

  • How to Create a One Way ANOVA in Excel

    In Microsoft Excel, install the Analysis ToolPak add-in to gain access to a large collection of data-analysis tools, including t-tests and several...

  • How to Interpret Excel ANOVA

    ANOVA stands for analysis of variance. The two-way with replication ANOVA test examines data from repeated trials during which both "factors" and...

  • How to Run ANOVA in Excel 2007

    Analysis of variance, or ANOVA, is an advanced statistical technique that analyzes data and tests a hypothesis concerning the population mean. ANOVA...

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!