Structured References in Excel

How to Create Structured References in Excel?

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

Now take a look at the below image.

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

• Step 4: Mention the table name as Data_Table.

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

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

Examples

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.

SUM Formula for Excel Table.

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

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.

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

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 SUM formula in excel for the normal range.

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.

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

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.

Oh my god!!! It is not showing the total of the Price column rather it is still showing the total of 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.

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.

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

I have applied the formula for Jan month, since it is an 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.

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 changes to Sales_Table [Jan].

So how do we deal with it?? We need to make the first argument i.e. 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.

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

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.

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

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