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

116 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    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] (Admin, 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
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      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