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

Article translations Article translations
Article ID: 282852 - View products that this article applies to.
This article was previously published under Q282852
Expand all | Collapse all


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:
  1. In Excel 2007, open a workbook.
  2. On the Insert menu, click Table.
  3. Provide the source of the data for the table, and then click OK.


The following formula calculates tax on an item based on the item's cost (itemcost):
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:
  1. Select cell A1.
  2. On the Format menu in Office Excel 2003, click Cells.
    In Office Excel 2007, click Cells , click Format, and then click Format Cells.
  3. Click the Number tab.
  4. In the Category list, click Custom.
  5. In the Type box, type "Item Cost" (with the quotation marks).
  6. Click OK.
  7. 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.


For more information about how to use data tables, click the following article number to view the article in the Microsoft Knowledge Base:
282851 How to use Microsoft Excel data tables to analyze information in a database
282855 How to create and use one-input data tables in Microsoft Excel
282856 How to create and use two-input data tables in Microsoft Excel


Article ID: 282852 - Last Review: June 12, 2007 - Revision: 5.3
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office Excel 2007
kbhowto KB282852

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from