<< 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 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.