What is an Unqualified Structured Reference in Excel?

Excel Tables assign names to each table and column header within a table. Referring the assigned column header names to formulas within and outside the table is known as Unqualified and Qualified Structured Reference, respectively. The below image differentiates between Structured Reference and Explicit Reference. In this article, we demonstrate 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. A Structured Reference within the tables is known as Unqualified Structured Reference and outside the tables it is referred to as Qualified Structured Reference.

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’s parts outside of 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.

What is an Unqualified Structured Reference in Excel?

As we mentioned earlier, Structured References that refer to cells within a table without an Item Specifier are unqualified structured references. Follow the below section to learn more about unqualified structured references in Excel.

Components of an Unqualified Structured Reference

An Unqualified Structured Reference has multiple components in its syntax. Suppose an unqualified structured reference has the latter syntax for a formula.

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. Therefore, users have to go through the below steps to create an unqualified structured reference.

Inserting an Excel Table: Highlight the entire dataset, then go to Insert > Table (in Tables section) or CTRL+T to insert an Excel Table. In the Create Table dialog box, tick My table has headers. Afterward, click OK.

Assign a Table Name: Place the cursor inside the table. In response, Excel displays the Table Design tab. Click on Table Design > enter the table name under Table Name (i.e., Sales) in the Properties section.

Referring to Table Parts as an Unqualified Structured Reference: After inserting the table and table name, use the column specifiers to create an unqualified structured reference.

The Benefits of Using an Unqualified Structured Reference in Excel Formulas

Easy to Understand : Unqualified Structured Reference uses the column names instead of cells to assign values. Therefore, it’s easy for other users to comprehend what the formulas really mean.

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

Easy to Modify: As users easily understand the used formulas, it’s easier to modify them according to their needs.

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

Download Excel Workbook

You may download the following workbook and practice by yourself.

Conclusion

In this article, we discussed the creation and usage of unqualified structured references in Excel. We hope this article clarifies your understanding regarding unqualified structured reference. Comment if you need further clarification and have anything to add.

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