Use F4 key to lock reference in a table
When you create formula with the named range of a table, you can't easily block the reference of your column.
For instance, if you used the fill-handle to copy to the right, the reference of your column will change :( The only way to avoid that is to write [[column1]:[column1]] for all your reference (very annoying)
So my suggestion to block the reference of a named range is to use the F4 key.
Your formula is =SUM(table1[column1]) and after pressing F4 it becomes =SUM(table1[[column1]:[column1]])
Good suggestion Frederic – thanks for taking the time to post it. We’ll take a look at the F4 loop and including other functionality like this as soon as we can. We’ll be spending more time on the things with more votes – so folks that really want to see this should keep voting it up.
John [MS XL]
Jeff Robson commented
Agreed! The ability of control how table references behave using simple $ notation would be very useful, particularly when you want to mix fixed & relative references in a single formula
Claire S commented
I think a fixed table reference should look like: Table1[$Column1] (or Table1[$[Column 1]] for column names with spaces or punctuation). This would keep the familiar $ sign indicating the fixed reference, and would also reduce the length of the formula as you wouldn't need to duplicate the column name as a range.
Edward Paul commented
Recently I have encountered a similar type of excel formula reference issue. In my case, I stuck with this “Excel Found a Problem with One or More Formula References” Error.
When I started searching for the fixes to resolve this excel formula error. I got this informative post….so you can check this out to know how to resolve any kind of excel formula reference error.
It's such a shame to spoil structured references in tables by making this so awkward
Absolutely - this is the single biggest detriment of using tables rather than a simple range.
Ben Sacheri commented
How many more votes are needed? It has been more than 2.5 years.
Excel MVP Jon Acampora created an add-in that will let you toggle table column references from relative to absolute by pressing F4. This is the capability I'd like to see built-in to Excel.
It would also be nice if when I drag a cell range to copy it, if Excel could detect table column references in the formula and prompt me with the "Auto Fill Options" menu. The menu would show an option to copy column references as Absolute or Relative.
Bryon Smedley commented
Please, oh please, oh please, oh, please, oh PLEEEEEASE!!!!!!
Antony Pihut commented
Can I vote 3 times!!! :)
This is desperately needed! Relative table references are a joke! Also, like the ideas in the comments below:
Jan Karel Pieterse - Once absolute reference is in place, get rid of difference b/t dragging fill handle and copying and pasting.
Kenneth Barber - The notation for an absolute table reference should be more in line with the absolute cell reference (i.e. - use $$).
eugenio and Ryan - F4 can toggle between table, non-table, relative and absolute references.
I agree with Kenneth Barber, a notation like table1[$[column1]] would be much better to read for absolute reference, the way it is makes the formula too long and it is simpler to write. It would also use a convention that we are used to.
Isaac Demme commented
To clarify my vote: I really don't care much about the F4 shortcut but I very much need the ability to use both fixed and relative references within the table[column] references.
Currently I end up using INDIRECT("table[reference]") whenever I want a fixed reference, which can cause problems later, e.g. when table/column/row names are changed.
The F4 should loop as follows
If I refer to a table,
TableReference --> A1 --> $A$1 --> A$1 --> $A1 --> TableReference
If I refer to a pivot table,
GetPivotData --> A1 --> $A$1 --> A$1 --> $A1 --> GetPivotData
If I refer to a named range,
Name --> A1 --> $A$1 --> A$1 --> $A1 --> Name
If I named a cell in a pivot table
GetPivotData --> Name --> A1 --> $A$1 --> A$1 --> $A1 --> GetPivotData
If you're making a reference to a named column in a table then surely 90+% of the time you want that to be an absolute reference. I can't see why the default is a relative reference in the first place. It seems inconsistent with the concept of named tables with named columns.
Likewise, it should be much easier to input A1 style references if you're using a table. If you edit a cell and click on a table cell, you get the table-style reference. Ctrl+click does give you something like B2:B2, but you have to edit the second part of the reference. F4 could also toggle between table and non table style references.
Kenneth Barber commented
On that note, the notation table1[$[column1]] would be much better than table1[[column1]:[column1]] for absolute references.
Zack Barresse commented
Ditto to what Jan states. It's highly confusing with the different behaviors.
Doug Glancy commented
This was the first thing I thought of and was going to submit it it wasn't already. Good explanation Frederic.
Jan Karel Pieterse commented
Once the F4 toggle is in place, please get rid of the different behaviour of dragging with fill handle versus copying and pasting formulas, this is highly confusing.
Ken Puls commented
I agree that using F4 to toggle the relative and absolute referencing for table headers would be valuable. This is far from discoverable and would make this a much more consistent experience with other formulas.