Microsoft Office Tutorials and References
In Depth Information
Working with Tables
Figure 9-13: This table shows sales by month and by region.
To change the name of a table, select any cell in the table, use the Table Name box in
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 following formula returns the sum of the data in the Sales column:
=SUM(Table2[Sales])
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
result to ignore hidden rows. To ignore filtered rows, use either of the following formulas:
=SUBTOTAL(109,Table2[Sales])
=AGGREGATE(9,1,Table2[Sales])
Even better, Excel provides some helpful assistance when you create a formula that refers to
data within a table. Figure 9-14 shows the Formula AutoComplete feature helping create a
formula by showing a list of the elements in the table.
Here’s another example that returns the sum of the January sales:
=SUMIF(Table2[Month],”Jan”,Table2[Sales])
For an explanation of the SUMIF worksheet function, refer to Chapter 7.
 
Search JabSto ::




Custom Search