Microsoft Office Tutorials and References

In Depth Information

**Using Names**

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:

=Table1[[#Totals],[Income]]

=SUM(Table1[Income])

=Table1[[#Totals],[Income]]-Table1[[#Totals],[Expenses]]

=SUM(Table1[Income])-SUM(Table1[Expenses])

=SUMIF(Table1[State],”Oregon”,Table1[Income])

=Table1[@Expenses]

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.

Using Names

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.)