What is an Unqualified Structured Reference in Excel?

Excel Tables assign names to each table and column header. Referring the assigned column header names to formulas within and outside the table is known as Unqualified and Qualified Structured Reference. The below image differentiates Structured Reference from Explicit Reference.

Structured Reference and Its Types

Structured Reference refers to Excel Tables and their parts instead of direct cell references.

Unqualified Structured Reference: When referencing to cells within a table, Excel automatically picks up the Column Name, making it an Unqualified Structured Reference.

Qualified Structured Reference: If users refer to the table parts outside the tables, the reference to a cell is preceded by the Table Name (i.e., Sales). This type of structured reference is known as a Qualified Structured Reference.

Components of an Unqualified Structured Reference

An Unqualified Structured Reference has multiple components in its syntax. This a possible syntax:

Table Name: Sales is the Table Name, referring to the entire table.

Column Specifier: [@[Jan]:[Feb]], [@Jan] or [@Feb] is the Column Specifier.

An Unqualified Structured Reference: Sales[@[Jan]:[Feb]] is an unqualified structured reference.

Creating an Unqualified Structured Reference

An unqualified structured reference requires the data to be in an Excel Table and formulas to be used within the table.

STEPS

Inserting an Excel Table:

• Select the entire dataset.
• Go to Insert > Table (in Tables section) or press CTRL+T to insert an Excel Table.
• In the Create Table dialog box, check My table has headers.
• Click OK.

Assign a Table Name:

• Place the cursor inside the table. Excel displays the Table Design tab.
• Click Table Design.
• Enter the table name in Table Name (Sales) in Properties.

Referring to Table Parts as an Unqualified Structured Reference:

• Use the column specifiers to create an unqualified structured reference.

The Benefits of Using an Unqualified Structured Reference in Excel Formulas

Easy to Understand : It uses the column names instead of cells to assign values.

Dynamic in Nature: If a value is changed, the formula results are  automatically updated.

Easy to Modify: As formulas are easy to understand, it’s easier to modify them.

Read More: How to Reference a Dynamic Component of a Structured Reference in Excel

Download Excel Workbook

Download the following workbook and practice.

Related Articles

• How to Lock a Structured Reference in Excel
• Applications of Absolute Structured References in Excel Table Formulas
• Use IF Function and Structured Reference in Excel
• How to Use HLOOKUP with Structured Reference in Excel

<< Go Back to Structured Reference | Table Formula | Excel Table | Learn Excel