Tables in worksheets and cell references

<< Click to Display Table of Contents >>

Tables in worksheets and cell references

As described in the introduction of the section Tables in worksheets, the ribbon command Insert | Table allows you to create "Tables in Worksheets". Below you will learn more about the special types of cell references that are available for cells in such a table.

Namely, apart from the usual types of cell references, the cells inside a table can also be addressed using names and special specifiers, as detailed below:

Using names

Names can be used in cell references to cells in a table as follows:

Addressing tables by their name

When you use the name of a table in a cell reference, it represents the entire table (with all of its cells, except for the header row and the total row).
Example:
=SUM(Table3) returns the sum of all cells in the table named "Table3".

Tip: The name of a table can be changed via the dialog box of the command Properties table_prop_icon on the contextual ribbon tab Table.

Addressing table columns by their column heading

To address a single table column in a cell reference, enter the name of the table, followed by the column heading enclosed in brackets.
Example:
=SUM(Table3[Spring]) returns the sum of all cells in the column with a column heading "Spring" in a table named "Table3".

Addressing adjacent table columns

You can also address a series of adjacent table columns. To do so, use the notation [[FirstColumn]:[LastColumn]].
Example:
=SUM(Table3[[Spring]:[Autumn]]) returns the sum of all cells in the columns "Spring" through "Autumn" in the table.

Addressing non-adjacent table columns

To address multiple non-adjacent table columns, separate them with a comma instead of a colon.
Example:
=SUM(Table3[Summer],Table3[Winter]) returns the sum of the columns "Summer" and "Winter".
Note that in this case the name of the table has to be specified for each column.

Using specifiers

Apart from names, special specifiers that represent a certain component of a table can be used in cell references with tables.

Specifiers must be entered behind the table name, enclosed in brackets.

For example, the cell reference Table3[#Totals] represents all cells in the total row of the table named "Table3".

The following specifiers are available:

#All:

Represents the entire table including header row and total row, for example Table3[#All].

#Data:

Represents all cells in the table that contain the actual data. These are all cells except header row and the total row.

#Headers:

Represents the cells in the header row of the table (the row containing the column headers).

#Totals:

Represents the cells in the total row of the table. (Note: If the total row is disabled, the cell reference will return a #REF! error value.)

@[ColumnName]:

Represents the cell that is in the same row of the specified column. This is useful especially for calculations within a table.
For example, a cell reference like [@Spring] used in a table stands for the cell that is located in the same row of the column with the title "Spring".
Example:
=SUM([@[Spring]:[Winter]]) returns the sum of those values in the columns "Spring" through "Winter" that are in the same row as the cell where this calculation was entered.

Note: PlanMaker does not distinguish between uppercase and lowercase letters in specifiers.