Microsoft Office Tutorials and References
In Depth Information
Referencing Data in a Table
Beginning with Excel 2007, you can designate a range to be a table by using the
Insert➜Tables➜Table command. Tables add a few new twists to formulas.
When you enter a formula into a cell in a table, Excel automatically copies the formula to all the
other cells in the column — but only if the column was empty. This is known as a calculated
column. If you add a new row to the table, the calculated column formula is entered automatically
for the new row. Most of the time, this is exactly what you want. If you don’t like the idea of
Excel entering formulas for you, use the SmartTag to turn off this feature. The SmartTag appears
after Excel enters the calculated column formula.
Excel also supports “structured referencing” for referring to cells within a table. The table in the
accompanying figure is named Table1.
You can create formulas that refer to cells within the table by using the column headers. In some
cases, using column headers may make your formulas easier to understand. But the real
advantage is that your formulas will continue to be valid if rows are added or removed from the table.
For example, these are all valid formulas that use table references:
The last formula uses an each-at symbol (@), which means “this row.” This formula is valid only
if it’s in a cell in one of the rows occupied by the table.
One of the most useful features in Excel is its ability to provide meaningful names for various
items. For example, you can name cells, ranges, rows, columns, charts, and other objects. You can
even name values or formulas that don’t appear in cells in your worksheet. (See the “Naming
constants” section, later in this chapter.)