Structured References in Excel

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

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.

How to Create Structured References in Excel?

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

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 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 * . 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 excel formula and copying or dragging it to the other remaining cells. It is not the same process as structured references. It works a bit differently.

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

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 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. 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 SUMIF function in excel.

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 . Therefore, it has changed to Sales_Table .

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 absolute reference, we need to make a double ROW entry, but we need to put @ symbol before the ROW name.

=Sales_Table:]

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.