Microsoft Office Tutorials and References
In Depth Information
Keep in mind that I didn't define any names in this worksheet. The formula uses table references that are based
on the column names. If you change the text in a column header, any formulas that refer to that data update
automatically. That's an example of how working with a table is easier than working with a regular list.
Although I entered the formula into the first data row of the table, that's not necessary. I could have put that for-
mula in any cell in column D. Any time you enter a formula into any cell in an empty table column, it will auto-
matically fill all the cells in that column. And if you need to edit the formula, edit the copy in any row, and Ex-
cel automatically copies the edited formula to the other cells in the column.
The preceding steps use the pointing technique to create the formula. Alternatively, you can enter the formula
manually using standard cell references. For example, you can enter the following formula in cell E3:
If you type the formulas using cell references, Excel still copies the formula to the other cells automatically: It
just doesn't use the column headings.
When Excel inserts a calculated column formula, it also displays an icon. Click the icon
to display some options, one of which is Stop Automatically Creating Calculated
Columns. Select this option if you prefer to do your own copying within a column.
Referencing data in a table
The preceding section describes how to create a column of formulas within a table — often called a “calculated
column.” What about formulas outside a table that refer to data inside of a table? You can take advantage of the
structured table referencing that uses the table name, column headers, and other table elements. You don't need
to create names for these items.
The table itself has a name that was assigned automatically when you created the table (for example, Table1 ),
and you can refer to data within the table by using the column header text.
You can, of course, use standard cell references to refer to data in a table, but the structured table referencing
has a distinct advantage: The names adjust automatically if the table size changes by adding or deleting rows.
Figure 9-14 shows a simple table that contains regional sales information. Excel named this table Table2 when
it was created because it was the second table in the workbook. To calculate the sum of all the values in the
table, use this formula: