Friday, January 17, 2020

Home Excel tips Excel Structured References

Excel Structured References

January 17, 2020 Excel tips,

Excel Structured References (Table of Contents)

How to Create?
Examples
Problems

What are Structured References?

If you are familiar with formulas in excel, references are not a stranger to you. When we talk about references the basics of references were Cell Reference, Absolute Reference, and Relative References. I hope you have a fair bit of knowledge about these references. In this article, I will cover only STRUCTURED REFERENCES in Excel.

Structured References starts with Excel Tables. It is similar to normal cell references but looks a more readable reference. I would say it works in a way of named ranges but these are auto named ranges by excel table. If you have noticed some of the formulas looks like this: =SUM (B2:B25) and some of the formulas looks like this: =SUM (Table1 [Sales])

The first formula we are all familiar with it. In this article we are concentrating on the second formula, these are called Structured Excel References.

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.

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.

Now I will change the table name to Data_Table and change the column heading to Amount.

In order to change the table name place a cursor inside the table > go to Design >Table Name.

Mention the table name as Data_Table.

Now change give a reference to the B3 cell.

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

You can download this Structured References Excel Template here – Structured References Excel Template

Excel Structured References – Example #1

Structured References are Dynamic

Using excel structured references 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 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 excel table shows the updated value but normal data range does not show the updated values unless you make some changes to the formula manually.

Excel Structured References Example #2

Now, look at one more example. I have a Product Name, Quantity, and Price information. Using this information I need to arrive 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 in excel 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 for the normal range.

 Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects) 4.9 (1,353 ratings)35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

$79 $399

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

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 excel table for 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 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 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 excel 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 structures references and we found the solution as well, but we have one more problem here, we cannot make the cell 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.

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 excel structured reference we cannot copy and paste the formula to 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.

Pro Tip: In order to make the ROW as an absolute reference we need to make 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 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.