Excel 2010 Bible

Part II: Working with Formulas and Functions

218

This formula will always return the sum of all 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, Excel

will adjust formulas that refer to that table automatically. For example, if you renamed Table1 to

AnnualData (by using the Name Manager, or by using Table Tools ➪ Design ➪ Properties ➪

Table Name), the preceding formula would change to

=SUM(AnnualData)

Most of the time, you want to refer to a specific column in the table. The following formula returns

the sum of the data in the Actual column:

=SUM(Table1[Actual])

Notice that the column name is enclosed in square brackets. Again, the formula adjusts automati-

cally if you change the text in the column heading.

Even better, Excel provides some helpful assistance when you create a formula that refers to data

within a table. Figure 10.13 shows the formula Autocomplete helping to create a formula by show-

ing a list of the elements in the table. Notice that, in addition to the column headers in the table,

Excel lists other table elements that you can reference: #All, #Data, #Headers, #Totals, and @ -

This Row.

FIGURE 10.13

The formula Autocomplete feature is useful when creating a formula that refers to data in a table.

Correcting Common Formula Errors ................................................................................

Sometimes, when you enter a formula, Excel displays a value that begins with a hash mark (#).

This is a signal that the formula is returning an error value. You have to correct the formula (or

correct a cell that the formula references) to get rid of the error display.

Tip
If the entire cell is filled with hash-mark characters, the column isn’t wide enough to display the value. You can
either widen the column or change the number format of the cell. n