Microsoft Office Tutorials and References

In Depth Information

**Correcting Common Formula Errors**

Refer to the table shown in Figure 15.11. This table is named
Table1
. To calculate the sum

of all the data in the table, enter this formula into a cell outside the table:

=SUM(Table1)

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 choosing Table

Tools
➪
Design
➪
Properties
➪
Table Name), the preceding formula would change to:

=SUM(AnnualData)

Most of the time, a formula will refer to a speciﬁ c 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

automatically 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 15.13 shows the formula AutoComplete helping to create a

formula by showing 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 15.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 or

pound sign (
#
). 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.