Chapter 9: Introducing Formulas and Functions
9
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 specific column in the table. The following for-
mula 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 9.12 shows the formula AutoComplete helping to create a for-
mula 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 9.12
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.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.