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 vs Explicit Reference-What is an Unqualified Structured Reference in Excel

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.

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.

Qualified Structured Reference


Components of an Unqualified Structured Reference

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


Unqualified Structured Reference 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.


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.

Creating Table

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.

Table Name

Referring to Table Parts as an Unqualified Structured Reference:

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

Unqualified Structured Reference Example

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.

Easy to Understand-What is an Unqualified Structured Reference in Excel

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.

Easy to Modify

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

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

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF