Microsoft Office Tutorials and References
In Depth Information
Referencing data in a table
Referencing data in a table
Formulas that are outside of a table can refer to data within a table by using the table name and
column headers. You don’t need to create names for these items. The table itself has a name (for
example, Table1), and you can refer to data within the table by using column headers.
You can, of course, use standard cell references to refer to data in a table, but using table
references has a distinct advantage: The names adjust automatically if the table size changes by
adding or deleting rows.
Refer to the table shown earlier, in Figure 160-1. This table was given the name Table1 when it
was created. To calculate the sum of all data in the table, use this formula:
=SUM(Table1)
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 Table1, Excel automatically adjusts formulas that refer to that
table. For example, if you rename Table1 as AnnualData (by using the Name Manager), the
preceding formula changes 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 (but ignores the total row):
=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 160-4 shows the Formula AutoComplete feature helping to create a
formula by showing a list of the elements in the table.
Figure 160-4: The Formula AutoComplete feature is useful when creating a formula that refers
to data in a table.
 
Search JabSto ::




Custom Search