Excel 2010 Bible

Chapter 10: Introducing Formulas and Functions

217

Although the formula was entered into the first row of the table, that’s not necessary. Any time a

formula is entered into an empty table column, it will automatically fill all the cells in that column.

And if you need to edit the formula, Excel will automatically copy the edited formula to the other

cells in the column.

Note

The “at” (@) symbol that precedes the column header represents “this row.” n

These steps use the pointing technique to create the formula. Alternatively, you could have entered

the formula manually using standard cell references rather than column headers. For example, you

could have entered the following formula in cell E3:

=D3-C3

If you type the cell references, Excel will still copy the formula to the other cells automatically.

One thing should be clear, however, about formulas that use the column headers instead of cell

references: They are much easier to understand.

Tip

When you add a formula to a column in a table, Excel displays a SmartTag. To override the automatic column

formulas, click the SmartTag and choose Stop Automatically Creating Calculated Columns. Use this option if

you need different formulas for different rows within the table. n

Referencing data in a table .......................................................................................

Excel offers some other ways to refer to data that’s contained in a table by using the table name and

column headers.

Note
Remember that you don’t need to create names for tables and columns. The table itself has a range name,
which is provided when you create the table (for example, Table1), and you can refer to data within the table
by using the column headers — which are not range names. n

You can, of course, use standard cell references to refer to data in a table, but using the table name

and column headers has a distinct advantage: The names adjust automatically if the table size

changes by adding or deleting rows. In addition, formulas the use table names and column headers

will adjust automatically if you change the name of the table or give a new name to a column.

Refer to the table (Table1) shown in Figure 10.11. To calculate the sum of all the data in the

table, use this formula:

=SUM(Table1)