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.

Structured Reference
  • 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.
Change Table Name 1
  • Step 4: Mention the table name as Data_Table.
Change Table Name 2
  • Step 5: Now, give a reference to the B3 cell.
Structured Reference 1

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.

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. We have added 2 line items to the data and now see the difference.

Dynamic Structured Reference 1.3
Dynamic Structured Reference 1.4

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.

Structured Reference Example 2.1

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

Structured Reference Example 2.2

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 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. 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 excelSUM Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more for the normal range.

Structured Reference Problem 1.1

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.

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 the “Qty” column. Like the normal range, copy the current formula and paste it into the “Price” column to get the total price.

Structured Reference Problem 1.4

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

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. 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 excelSUMIF Function In ExcelThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more.

Problem 2.1

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

Problem 2.2

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.

Problem 2.6

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.

Problem 2.3
Problem 2.4

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.

Problem 2.5

Pro Tip: To make the ROW an absolute referenceAbsolute ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more, 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.
Turn Off Reference

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 Excel TablesExcel TablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more with the examples and problems and how to switch them off. You can learn more from Excel from the following functions: –