Part II: Working with Formulas and Functions
The at symbol (@) that precedes the column header represents “this row.” So [@Actual] means “the value in the
Actual column in this row.”
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’re much easier to understand.
To override the automatic column formulas, access the Proofing tab of the Excel Options dialog box. Click
AutoCorrect Options and then select the AutoFormat As You Type tab in the AutoCorrect dialog box. Remove the
check mark from Fill Formulas in Tables to Create Calculated Columns.
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.
Remember that you don’t need to create names for tables and columns. The data in the table itself has a range
name, which is created automatically 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.
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 that 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 shown in Figure 9.11. This table is named Table1. To calculate the sum
of all of the data in the table, enter this formula into a cell outside the table:
=SUM(Table1)
This formula will return the sum of all of the data (excluding calculated total row values, if
any), even if rows or columns are added or deleted. And if you change the name of Table1,
