What is an Unqualified Structured Reference in Excel?

The image below differentiates a Structured Reference from an 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

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.

Name the Table:

• 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