Microsoft KB Archive/282852

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

Article ID: 282852

Article Last Modified on 6/12/2007

-

APPLIES TO


 * 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

-



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:
 * 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.



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:
 * 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.
 * 1) Click the Number tab.
 * 2) In the Category list, click Custom.
 * 3) In the Type box, type &quot;Item Cost&quot; (with the quotation marks).
 * 4) Click OK.
 * 5) Repeat steps 1-6 for cell B1, but type the format in step 5 as &quot;Tax&quot; (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.

