Microsoft Office Tutorials and References
In Depth Information
Figure 9-14: This table shows sales by month and by region.
This formula always returns the sum of all the data, even if rows or columns are added or deleted. And if you
change the name of the table, Excel adjusts all formulas that refer to that table automatically. For example, if
you rename Table1 to be Q1Data, the preceding formula changes to
To change the name of a table, select any cell in the table, use the Table Name box of
the Table Tools ⇒ Design ⇒ Properties group. Or, you can use the Name Manager to
change the name of a table (Formulas ⇒ Defined Names ⇒ Name Manager).
Most of the time, your formulas will refer to a specific column in the table rather than the entire table. The fol-
lowing formula returns the sum of the data in the Sales column:
Notice that the column name is enclosed in square brackets. Again, the formula adjusts automatically if you
change the text in the column heading.
Keep in mind that the preceding formula does not adjust if table rows are hidden as a
result of filtering. SUBTOTAL and AGGREGATE are the only functions that change their