Absolute or Static structured references in Excel table – How to

Just a quick note on how to do absolute referencing if you are using structured references in Excel tables. For the ones who are not familiar with structured references and tables, your tutorial is baking and you will surely be able to catch up afterward.

Referencing – A quick review

Those who know structured references are already aware of one nuisance in using these as a reference especially in formulae. Cutting it short when you use simple row:column addresses you have to option to make the references absolute or relative and this enables you to reference in four different ways for example if you have to refer to cell A1 you can refer in the following ways:

$A$1 – this makes both rows and column static

$A1 – this makes the column static, however, relieves the row and it will change when the contents of this cell are dragged downwards or upwards

A$1- this makes the row static, however, relieves the column from being static and it will change when the contents of this cell are dragged leftwards or rightwards

A1 – this puts no restriction at all on any part of reference and rows and columns will change if they are dragged using a fill handle.

Structured references under tables – Problem

If you have already taken the help of relative-absolute referencing in excel you must know how helpful they are in speeding up your work as you don’t have to type in the formula for each cell separately.

In situations when you have tables and you have built up a formula using table’s structured references then you are almost out of luck to drag fill the formula in multiple cells correctly as there is no option on making structured references static or absolute as we use to do under normal cell referencing.

Replicating the problem

Have a look at the following sample data

We have regions and we have different products sold by different sales person. What we want to is to sum up all the sales of the product by each sales person. We can achieve this using:

• Normal cell references
• Table’s structured references

Following is the data set we used for this article:

Using normal cell references it is easy. We can use SUMIF formula to do the job and formula will be as follows:

=SUMIF($B2:$B13,$B18,C2:C13)

Now with having columns static or absolute reference to columns, we can easily calculate the total sales by sales person 2 and 3 by simply dragging it to next two cells to the right.

Using table’s structured references our formula will change a bit as it will then use table’s header to refer and in this case our formula will be:

=SUMIF(Table1[Product code],[Product code],Table1[Sales Man 1])

So far so good, but problem starts from this point onwards, if we drag the cell so that we can calculate the total sales for sales person 2 and 3, it doesn’t work! The reason is that when we move cells rightward, reference shifts rightward as well:

We really wish we had a way to make structured references absolute, but no… its not possible… you cannot use $ sign to make column or row static.

A workaround

I believe that this doesn’t has to be a solution for this problem but somehow it works. So here it is:

If we change the structured reference to a range and then try to drag the formula across columns then the reference does not shift! And calculations are carried out correctly. Following are the formula we have used above as before and the one I suggested now as after:

Before: =SUMIF(Table1[Product code],[Product code],Table1[Sales Man 1])

After: =SUMIF(Table1[[Product code]:[Product code]],Table3[[Product code]:[Product code]],Table1[Sales Man 1])

Critically analyzing the formula we see two things:

• the references that we wanted to be static so they don’t shift when drag the formula cell are changed to “range” with the same name repeating twice for the same column.
• The criteria is also referenced as range and in addition to that table name is also appended with it.

With this formula in place now simply drag it to the right and see it working flawlessly as following animation shows:

Extending Table with structured reference used in formula

One more to notice is that extending table with next criteria in the cell works well too. In the next row product code BB is provided and the rest of the calculations are done automatically:

References:

Absolute Formula References in Structured Tables – Thanks to Jon. His tutorial sparked the idea.