Why is it still a pain to make table column references absolute?

Switching a cell reference from relative to absolute is ideal when you don’t want it shifting in a formula after you copy and paste elsewhere or use the fill handle to populate adjacent cells.

Using A2 as an example, you only have to place the cursor on the inside or outside of the reference, and a press of F4 will prefix the dollar sign ($) to the column letter and row number ($A$2), making it absolute.

A second tap causes the column to turn relative (A$2), a third does the same for the row ($A2), while another restarts the cycle.

The same logic applies to ranges, which contain start and end cells. Predictably, this means there are many more possibilities for combining relative, absolute, and mixed references.

In fact, there are 16 for A2:A11:

1. A2:A11
2. A2:$A$11
3. A2:A$11
4. A2:$A11
5. $A$2:A11
6. $A$2:$A$11
7. $A$2:A$11
8. $A$2:$A11
9. A$2:A11
10. A$2:$A$11
11. A$2:A$11
12. A$2:$A11
13. $A2:A11
14. $A2:$A$11
15. $A2:A$11
16. $A2:$A11

Alternatively, if you’re using tables, you can take advantage of structured references — reader-friendly names that point to tables and columns.

Let’s be honest, you’d rather see tblEmployees[Name] than A2:A11, wouldn’t you?

On occasions, you’ll want to lock a column reference in your formula for the same reasons I mentioned about cells.

The problem is there’s no keyboard shortcut for toggling between a relative and absolute table column. This means having to manually duplicate the first part of the reference, separate each side with a colon, and wrap it inside an extra pair of square brackets: tblEmployees[[Name]:[Name]].

Bit of a faff, isn’t it? I just want to press F4.

17 years on since tables first became a thing, we’re still waiting for this game-changing keyboard shortcut to arrive…