What is an Unqualified Structured Reference in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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 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. 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.

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.

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.

=SUM(Sales[@[Jan]:[Feb]])

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. 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.

Creating Table

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.

Table Name

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.

Unqualified Structured Reference Example


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.

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

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

Dynamic

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

Easy to Modify

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

 

 

ExcelDemy
Logo