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 75-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 75-4 shows the Formula AutoComplete feature helping to create a formula by
showing a list of the elements in the table.
Figure 75-4: The Formula AutoComplete feature is useful when creating a formula that refers to data in a table.
Search JabSto ::




Custom Search