FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Structured References in Excel

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Create Structured References in Excel?

Structured references start with Excel tables. When the tables are created in Excel, it automatically makes structured references for you.

Now take a look at the below image.

• Step 1: We had given a link to cell B3. Instead of showing the link as B2, it is showing as Table1[@Sales]. Here Table1 is the table’s name, and @Sales is the column we refer to. All the cells in this column are referred to by a Table name and followed by the column heading name.
• Step 2: Now, we will change the table name to Data_Table and the column heading to Amount.
• Step 3: Place a cursor inside the table > go to Table Design  >Table Name to change the table name.
• Step 4: Mention the table name as Data_Table.
• Step 5: Now, give a reference to the B3 cell.

So, we have understood that structured reference has two parts: Table Name and Column Name.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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 the normal range and Excel table.

SUM Formula for Normal Range.

SUM Formula for Excel Table.

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

The structured reference in the Excel table shows the updated value. Still, 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. We have a product name, quantity, and price information. Using this information, we need to arrive at the sales value.

The formula is Qty * Price to get the sales value. Let us apply this formula to the table.

Formula says [@QTY] * [@PRICE]. It 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 problems as well. We are all familiar with applying the and copying or dragging it to the other remaining cells. It is not the same process as structured references. It works a bit differently.

If we want to sum price and sales value, we will copy and paste or drag the current formula to the other two cells, giving us the SUM value of price and sale value.

Now, apply the same formula for the Excel table for the “Qty” column.

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

It is not showing the total of the “Price” column. Rather, it only shows the total of the “Qty” column. 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 limitations. For example, we can no longer do the copy-paste job with structured references. So, then how do we overcome this limitation?

The solution is very simple. First, we need to drag the formula instead of copying it. Select the formula cell, use the , and drag it to the remaining two cells to change the column reference to “Price” and “Sale Value.”

Now, we have updated formulas to get the respective totals.

Problem #2

We have seen one problem with the structure references. We also found the solution, but we have one more issue: we cannot make the call as an absolute reference if we drag the formula to other cells.

Let us take a look at the below example now. We have a sales table with multiple entries, and we want to consolidate the data using the .

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

We have applied the formula for Jan month. Unfortunately, since it is a structured reference, we cannot copy and paste the formula to the remaining two columns. However, it will not change the reference to Feb and Mar, so that we will drag the formula.

We did not get any values in the “Feb” and “Mar” columns. What would be the problem? Closely look at the formula.

We have dragged the formula from Jan month. Since we dragged the formula, the SUMIF function’s first argument is the criteria range, Sales_Table [Product]. Therefore, 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 references, we do not have the luxury of using the F4 key to alter the referencing type.

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

Now, we can drag the formula to the other remaining two columns. Again, the criteria range will be constant, and other column references will change accordingly.

Pro Tip: To make the ROW an , 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 them off by following the below steps.

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

Things to Remember

• We must double the column name to make the absolute reference in structured reference.
• We cannot copy the structured reference formula. Instead, we need to drag the formula.
• We cannot see which cell we are referring to in structured references.
• We can turn them off if you are not interested in structured references.

Recommended Articles

This article has been a guide to what Structured References are in Excel. Here we learn how to create structured references in with the examples and problems and how to switch them off. You can learn more from Excel from the following functions: –