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.

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

• 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 tables. 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 to 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 formulaExcel FormulaThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more 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 excelSUM Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. It is categorized under the math and trigonometry function entered by typing “=SUM” followed by the values to be summed. The values can be numbers, cell references or ranges.read more 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 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 handleFill HandleThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications.read more 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 excelSUMIF Function In ExcelThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range).read more.

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

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.

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.

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