WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Structured References in Excel

Structured References in Excel

How to Create Structured References in Excel?

Structured References starts with excel tables. As soon as the tables are created in excel, it automatically creates structured references for you.

Now take a look at the below image.

Structured Reference

  • Step 1: I had given a link to the cell B3. Instead of showing the link as B2, it is showing as Table1[@Sales]. Here Table1 is the name of the table, and @Sales is the column we are referring to. All the cells in this column are referred to by a Table name and followed by the column heading name.
  • Step 2: Now, I will change the table name to Data_Table and change the column heading to Amount.
  • Step 3: In order to change the table name, place a cursor inside the table > go to Design >Table Name.

Change Table Name 1

  • Step 4: Mention the table name as Data_Table.

Change Table Name 2

  • Step 5: Now, change give a reference to the B3 cell.

Structured Reference 1

So we have understood that structured reference has two parts Table Name & Column Name.

Examples

You can download this Structured References Excel Template here – Structured References Excel Template

Example #1

Using structured references, you can make your formula dynamic. Unlike normal cell references, it allows the formula to be live in case of addition and deletion in the data range.

Let me apply the SUM formula for both normal range and excel table.

SUM Formula for Normal Range.

Dynamic Structured Reference 1.1

SUM Formula for Excel Table.

Dynamic Structured Reference 1.2

Let me add a few lines to the data of both normal and excel tables. I have added 2 line items to the data, now see the difference.

Dynamic Structured Reference 1.3

Dynamic Structured Reference 1.4

Structured reference in the excel table shows the updated value, but the normal data range does not show the updated values unless you make some changes to the formula manually.

Example #2

Now, look at one more example. I have a Product Name, Quantity, and Price information. Using this information, I need to arrive at the Sales Value.

Structured Reference Example 2.1

In order to get the sales value, the formula is Qty * Price. Let’s apply this formula to the table.

Structured Reference Example 2.2

Formula says [@QTY] * [@PRICE]. This is more understandable than the normal reference of B2 * C2. We do not get the table name if we are putting the formula inside the table.

Problems with Excel Structured References

While using structured references, we face some problems, which are enlisted below.

Problem #1

Structured references have their own problems as well. We are all familiar with applying the excel formula and copying or dragging it to the other remaining cells. This is not the same process in Structured References. It works a bit differently.

Now, look at the below example. I have applied the SUM formula in excel for the normal range.

Structured Reference Problem 1.1

If I want to sum Price and Sales Value, I will simply just copy and paste or drag the current formula to the other two cells, and it will give me the SUM value of Price & Sale Value.

Structured Reference Problem 1.2

Now apply the same formula for the excel table for the Qty column.

Structured Reference Problem 1.3

Now we got the sum of Qty column. Like normal range, formula copy the current formula and paste it to Price column to get the total of Price.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Structured Reference Problem 1.4

Oh my god!!! It is not showing the total of the Price column; rather, it is still showing the total of the Qty column only. So, we cannot copy and paste this formula to the adjacent cell or any other cell to refer to the relative column or row.

Drag the Formula to Change the Reference

Now we know its limitation. We cannot do the copy-paste job anymore with structured references. Then how do we overcome this limitation?

The solution is very simple. We just need to drag the formula instead of copying. Select the formula cell and use the fill handle and drag it to the remaining two cells to change the column reference to Price and Sale Value.

Structured Reference Problem 1.5

Now we have updated formulas to get the respective totals.

Problem #2

We have seen one problem with the structure references, and we found the solution as well, but we have one more problem here, we cannot make the call as an absolute reference if we are dragging the formula to other cells.

Let’s take a look at the below example now. I have a sales table with multiple entries, and I want to consolidate the data by using the SUMIF function in excel.

Problem 2.1

Now I will apply the SUMIF function to get the consolidated sales values for each product.

Problem 2.2

I have applied the formula for Jan month. Since it is a structured reference, we cannot copy and paste the formula to the remaining two columns. It will not change the reference to Feb & Mar, so I will drag the formula.

Problem 2.6

Oh!! I did not get any values in Feb & Mar column. What would be the problem??? Closely look at the formula.

We have dragged the formula from Jan month. In the SUMIF function first argument is Criteria Range Sales_Table [Product] since we dragged the formula. It has changed to Sales_Table [Jan].

So how do we deal with it?? We need to make the first argument, i.e., the Product column as absolute and other columns as a relative reference. Unlike normal reference, we do not have the luxury of using the F4 key to alter the referencing type.

The solution is we need to duplicate the referencing column, as shown in the below image.

Problem 2.3

Problem 2.4

Now we can drag the formula to other reaming two columns. Criteria Range will be constant, and other column references will change accordingly.

Problem 2.5

Pro Tip: In order to make the ROW as an absolute reference, we need to make a double ROW entry, but we need to put @ symbol before the ROW name.

=Sales_Table[@[Product]:[Product]]

How to Turn Off Structured Reference in Excel?

If you are not a fan of structured references, you can turn off by following the below steps.

  • Step 1: Go to FILE > Options.
  • Step 2: Formulas > Uncheck Use table names in formulas.

Turn Off Reference

Things to Remember

  • In order to make the absolute reference in structured reference, we need to double the column name.
  • We cannot copy the formula of structured reference; instead, we need to drag the formula.
  • We cannot see exactly which cell we are referring to in structured references.
  • If you are not interested in structured references, you can turn them off.

Recommended Articles

This has been a guide to what are structured references in excel. Here we learn how to create structured references in Excel Tables with the examples, problems, and how to switch it off. You can learn more from excel from the following functions –

  • Mixed References in Excel
  • Circular Reference in Excel
  • Relative References in Excel
  • Absolute Reference in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Structured References Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More