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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q282852
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
XL2007 XL2000 XL2001 XL98 data table tables tabel tabels formula formuals input inputs XL2002 XL2003 XL97 XL2007

Article ID: 282852 - Last Review: 06/12/2007 23:36:15 - 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