Concept: Structured References in Workbooks

Live data areas in Worksheets workbooks are similar to Excel tables. When you add a live data area to a workbook based on a Workday advanced report, Worksheets creates a table
that contains the live data. By default, Worksheets assigns a live data table name using the word Report
and an incrementing digit. Example: Report1.

Optionally, you can create your own name for the live data table, either at the time you run the data wizard or later. Worksheets supports table names only for advanced reports. If you do rename the live data table, remember that the table name must be unique in the workbook and also can't be the same as any defined names or pivot table names.

After running the data wizard, you can use a special syntax incorporating the table name and column name to specify dynamic references in formulas instead of using literal cell ranges. This combination of a table and column name is a structured reference
. Worksheets updates the range of a structured reference automatically as needed when the live data is refreshed.

Example: Instead of the formula =SUM(B1:B24)
you can use =SUM(SalesCategory[Sales Amount])
where SalesCategory
is the table name and Sales Amount
is the column name. The SalesCategory table name isn't displayed in the live data area; however, if you were to display the live data panel, it would display there.

Keep these considerations in mind:

For live data areas that were created before Workday 23R1, you need to edit the live data area to add a table name. You can do this from the live data panel by clicking

Edit Live Data

or by clicking any of the

Edit

links in the panel.

If you change a table name or column name later, Worksheets updates existing structured references in the workbook
; however, if you have an external reference that contains a table or column name, Worksheets doesn't automatically update the consumer workbook. You need to update the reference manually.

Worksheets doesn't support:

Using structured references (table and column names) to define conditional formatting rules. We do support applying conditional formatting rules to data that was populated using a structured reference.

Uploading files to Worksheets that were originally created in Excel and that contain structured references.

Using structured references in a NOTIFYIF formula.

Structured Reference Syntax Auto-Fill

When you are typing a formula in the formula bar or in a cell, you don't need to remember the syntax for the structured reference. Depending on what you type into the formula, or what you select in the live data table, Worksheets generates and displays the appropriate syntax.

Worksheets generates live data structured reference syntax when you select:

An individual cell in the live data table.

Column headings. Select one or more heading rows.

Columns of live data. You can select either the heading along with the data, or select only the data.

The entire table. Select the entire live data area including headings.

Additionally, if you type a table name and then type a left bracket in the formula, the column names and special identifiers display for selection.

Structured Reference Syntax Guidelines

Table names don't need to be inside quotes or square brackets.

Column names don’t need to be inside quotes, but you do need to surround them with square brackets.

Use the pound symbol (#) and indicators described below to refer to a special subset of a cell range.

When including a formula column in a live data area, the formula doesn't need to include the table name, but it does need to include the at symbol (@) preceding any column names.

Remember to press the unconstrained array keyboard shortcut when submitting a formula: Ctrl+Alt+Enter or Ctrl+Alt+Shift+Enter (Windows) or Command+Option+Enter (Mac).

Special Identifiers for Structured References

Specifier	Example	Refers To
#All	[#All]	The entire table, including column headers and data.
#Data	[#Data]	Only the data rows, no header.
#Headers	[#Headers]	Only the header row, no data.
#This Row or @		Only the cells in the same row as the formula. You can't combine these specifiers with any other item specifier. This must be used in a live data formula column before every header. Example: =SUM([@Current Salary],[@Proposed Bonus])

Syntax Examples

Basic example:

=SUM(CompDetails[New Salary],CompDetails[Stock Amount])

Notes:

The formula adds the values of the two columns together and displays the results in a new column. The table name is CompDetails
.

The column names are New Salary
and Stock Amount
(enclosed in brackets).

For the table of examples below, we added live data to a workbook and named the table CompDetails
. Here's an image showing the column names.

Structured Reference	Content Included	Implied Cell Range
CompDetails or CompDetails[#Data]	All data in the table, without column headers.	A2:G39
=CompDetails[[#All],[Proposed Bonus %]]	All cells in the Proposed Bonus % column	F1:F39
=CompDetails[[#Headers],[Salary Increase %]]	The header of the Salary Increase % column	E1
=CompDetails[[#All],[Compensation Grade]:[Salary Increase %]]	All cells between the Compensation Grade and Salary Increase % columns, inclusive, with headers	B1:E39
=CompDetails[[Compensation Grade]:[New Salary]]	The data between the Compensation Grade and New Salary columns, inclusive, no headers	B2:D39
=CompDetails[[#Headers],[Employee ID]:[New Salary]]	The headers of the columns between Employee ID and New Salary	A1:D1
=CompDetails[[#Headers],[#Data],[Compensation Grade]]	The header and data of the Compensation Grade column	B1:B39
=CompDetails[[#This Row], [New Salary]] or =CompDetails[@New Salary]	The cell at the intersection of the current row and the New Salary column	D5 (if the current row is 5)

Comparing Structured References and Defined Names

Defined names are similar to structured references in some ways, but there are notable differences. This table summarizes the key differences:

Defined Names	Structured References
You can create defined names for any workbook range.	Structured references refer only to data in a live data area, for advanced report data only.
A defined name specifies a fixed range of cells across columns and rows, or you can use the Formula field to define a name for a formula, constant, or non-contiguous set of cell ranges.	A structured reference combines a table name and column names to specify a dynamic range of cells in one or more live data columns.
If the defined name is in a live data area, the range of cells remains the same even if the data changes during a refresh.	The data referred to by the reference is dynamic and is updated automatically after a live data refresh.
A defined names panel ("right pane") shows all defined names so you can view and manage them.	Structured references aren't standalone entities - they're managed as part of the live data area - so they don't need a separate panel.
You can use defined names in Slides presentations.	Structured references aren't visible or usable in Slides presentations.
If you use a defined name in a Slides presentation linked table and later you change its name or range, you need to refresh the linked data in the presentation to see the updated data. If you use a defined name value in a Slides presentation linked value, and then you edit a defined name's range so that the linked value is no longer included, you need to remove and re-add the linked value.	You can change a table name or column name. If you do so, Worksheets updates existing structured references in the workbook; however, if you have an external reference (consumer workbook) that contains the previous table or column name, Worksheets doesn't automatically update it. For more information about external references, see Concept: Using External References to Refer to Data in Other Workbooks.