Article ID: 282852 - Last Review: June 12, 2007 - Revision: 5.3

An overview of data tables in Excel 2003 and in Excel 2007

View products that this article applies to.

This article was previously published under Q282852

SUMMARY

You can use data tables to see how different input values affect the results of a formula. By doing so, you can test different input values for a formula without having to retype or copy the formula for each value. To create a data table in Microsoft Excel 2002 or Microsoft Office Excel 2003, click Table on the Data menu. To create a data table in Microsoft Office Excel 2007, follow these steps:

In Excel 2007, open a workbook.
On the Insert menu, click Table.
Provide the source of the data for the table, and then click OK.

Back to the top

MORE INFORMATION

The following formula calculates tax on an item based on the item's cost (itemcost):

=itemcost*8.1%

One way to calculate tax for several items is to type the formula for each item, as shown in the following example:

   Table created manually (with formulas displayed)
   ================================================

         |     A      |   B
      ---|------------|-------
       1 |Item Cost   |Tax
       2 |          15|=A2*8.1%
       3 |        17.5|=A3*8.1%
       4 |       22.35|=A4*8.1%

   Table created manually (with values displayed)
   ==============================================

         |     A    |   B
      ---|----------|-------
       1 | Item Cost|Tax
       2 |    $15.00|  $1.22
       3 |    $17.50|  $1.42
       4 |    $22.35|  $1.81

In the previous examples, you must type or copy the tax formula for each item you want to evaluate. However, if you create a table with the Table command, you only have to type the formula once:

   Table created with the Table command (with formulas displayed)
   ==============================================================

         |     A     |     B
      ---|-----------|------------
       1 | Item Cost | =C1*8.1%    <--The formula is typed once; then the
       2 |         15| =TABLE(,C1) <--Table command puts the results here.
       3 |       17.5| =TABLE(,C1) 
       4 |      22.35| =TABLE(,C1) 

   C1 represents a variable. The values in cells A2:A4 are substituted for
   C1, and the corresponding result is placed in cells B2:B4.

   Table created with the Table command (with values displayed)
   ============================================================

         |     A         B
      ---|-----------|------
       1 |Item Cost  |Tax
       2 |     $15.00| $1.22
       3 |     $17.50| $1.42
       4 |     $22.35| $1.81

The values in cells A1 and B1 are number formats. To duplicate these values, follow these steps:

Select cell A1.
On the Format menu in Office Excel 2003, click Cells.
In Office Excel 2007, click Cells , click Format, and then click Format Cells.
Click the Number tab.
In the Category list, click Custom.
In the Type box, type "Item Cost" (with the quotation marks).
Click OK.
Repeat steps 1-6 for cell B1, but type the format in step 5 as "Tax" (with the quotation marks).

Note In Excel 2007, the column header cells are created and formatted automatically when you insert the table, so we can set the text to the header cells by entering the name directly.

Back to the top