Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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]])

128 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Frederic LE GUEN shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
Accepting Votes  ·  AdminExcel Team [MSFT] (Product Owner, Office.com) responded  · 

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.

Best,
John [MS XL]

15 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • AHarris commented  ·   ·  Flag as inappropriate

    Absolutely - this is the single biggest detriment of using tables rather than a simple range.

  • Ben Sacheri commented  ·   ·  Flag as inappropriate

    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.
    https://www.excelcampus.com/tools/absolute-reference-add-in/

    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.

  • RAF commented  ·   ·  Flag as inappropriate

    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.

  • DJunqueira commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

  • Ryan commented  ·   ·  Flag as inappropriate

    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

    Etc.

  • Nick commented  ·   ·  Flag as inappropriate

    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.

  • eugenio commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    On that note, the notation table1[$[column1]] would be much better than table1[[column1]:[column1]] for absolute references.

  • Doug Glancy commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

Feedback and Knowledge Base