Excel Table Absolute Structured References

Mynda Treacy

June 17, 2025

Excel Tables are brilliant for structured data and dynamic formulas, but if you’ve ever copied a formula across columns and ended up with unexpected results, you’re not alone. The issue? Table references behave differently depending on how you copy them - and Excel doesn’t give you a straightforward way to lock them like with normal cell references.

In this guide, you’ll learn how to lock table references the right way - plus a smart shortcut to save time.

Excel Table Absolute Structured References Video

Subscribe YouTube

Get the Practice File and Cheat Sheet

Enter your email address below to download the free files.



By submitting your email address you agree that we can email you our Excel newsletter.

The Problem with Copying Excel Table Formulas

Let’s say you’re using a formula like this:

=SUMIFS(Stock[Stock OH], Stock[Group], $D$4)

This sums the “Stock OH” column based on a “Group” match in D4 as shown below:

how to copy formulas in and Excel table?

Sounds great… until you copy it.

  • Copy/paste (Ctrl+C, Ctrl+V): Structured references are treated as absolute. That means they don’t shift when pasted - great if that’s what you want, but usually it’s not.
  • Left-click and drag: Structured references are treated as relative. When you drag the formula across, the column references shift - and your formula breaks:
why Excel table references are hard to cppy?

So, how do you lock a specific table reference?

Locking a Single Column Reference in a Table

To lock a column like Stock[Group], wrap it in an extra set of square brackets and repeat the column reference like this:

Stock[ [Group] : [Group] ]

Note: Spaces added for clarity.

This tells Excel: lock the reference from the Group column to the Group column - essentially, just this column.

Now when you drag the formula across, it won’t shift.

how to lock a column reference in an Excel table?

Shortcut to Insert Table Structured References Quickly

Instead of manually typing double brackets and colons, select two columns in the formula editor. For example:

Stock[[Group]:[Product]] 

as shown below:

Excel interface

Then just replace ‘Product’ with ‘Group’ and you're done. Excel writes the double brackets and colon etc. for you, plus no memorizing syntax required.

Locking Multiple Columns in Table References

Double brackets work for ranges too:

=XLOOKUP(J4, Sales[[Product]:[Product]], Sales[[East]:[West]])
  • The first part looks for a match in the Product column (locked).
  • The second part returns values from East to West (locked by default when multiple columns are selected with your mouse).

To return these horizontally but spill vertically, wrap it in TRANSPOSE (see screenshot below):

=TRANSPOSE(XLOOKUP(J4, Sales[[Product]:[Product]], Sales[[East]:[West]]))
how to lock multiple columns in an Excel table reference?

Making Multi-Column References Relative

By default, Excel locks multi-column references like:

=SUM(Sales[[North]:[East]])

Copy it across and it won’t shift.

To make it relative, remove the double brackets and prefix each column with the table name.

=SUM(Sales[North]:Sales[East])
how to make multi-column reference dynamic in an Excel Table?

Now it will shift when copied across - perfect for side-by-side calculations.

Locking Row References in Excel Tables

When working inside a table, Excel uses the @ symbol to refer to values in the current row:

= [@Allocation] * [@[Admin Overhead]]
how to lock a row reference in an Excel table?

Drag it across and - uh oh - it shifts columns.

To lock it:

1. Select a multicell range across columns like cells C4:D4, Excel will insert the structured reference with the double brackets and colon already inserted:

    =Overheads[@[Allocation]:[Admin Overhead]]

    2. Then replace the second column with the actual one you want and complete your formula:

      = Overheads[@[Allocation]:[Allocation]] * [@[Admin Overhead]]

      Now it's locked.

      Referencing Rows in a Table

      You can also reference multiple columns in the same row:

      =SUM(Overheads[@[Admin Allocated]:[Marketing Allocated]])
      how to refer multiple columns in the same row in an Excel table?

      This sums across columns in the same row.

      Tip: If the column name has no spaces (like Allocation), you can even skip the extra brackets after the @ sign:

      =[@Allocation]

      Cheat Sheet: Table Reference Syntax

      I know there’s a lot of scenarios, so I included this cheat sheet in the Excel file you can download above to help you quickly find what you need:

      how to refer a row in an Excel table?

      Tip: Don’t try to memorize these - just use the mouse and have Excel insert them for you, then tweak as needed.

      Final Thoughts

      Locking structured references isn’t always intuitive, but once you know the double bracket trick and the mouse shortcut to insert them, it becomes second nature. Whether you’re locking rows, columns, or ranges, the key is using the right syntax for the right copy method.

      Are you using Excel Tables to their full potential? See what else Tables can do, check out our comprehensive Excel Tables lesson here.

      AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

      CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

      Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

      Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

      24 thoughts on “Excel Table Absolute Structured References”

      1. Eye roll at people who love structured references.

        Show us how to do a fairly simple accumulation sumif based on multiple columns.

        If I see that done, maybe then I’ll start giving them some love.

        Reply
        • I agree, referencing multiple columns isn’t the most intuitive, but like anything, once you get the hang of it it’s not that difficult. That said, don’t forget you can submit ideas for improvements to Excel on UserVoice.

          Reply
      2. Really wonderful excel data informations. Even only 20 pages but for me are really
        more than that. Thank you very much !!

        Reply
      3. Great article. I use Absolute structured references all of the time, but I still learned something.

        I have a reoccuring issue… I export excel data from my eCommerce components. These components are in active development, and from time to time the structure of the excel export will change, e.g. new columns added or simply ordering changed.

        For example, previously, my excel export had the following column order…
        Date Submitted, club, clubid, feedback, comment, email, ChildsName.
        I pasted this into my “Data” table

        I had another table which analysed the raw data, and the following formula counted the number of people who gave feedback of 3 (column in Analysis table has heading = “3”…

        =COUNTIFS(Data[[feedback]:[feedback]],Analysis[[#Headers],[3]],Data[[clubid]:[clubid]],Analysis[[ClubID]:[ClubID]])

        After a recent excel update, the excel export has a different order
        Date Submitted, club, feedback, comment, email, ChildsName, clubid
        When I paste this into my “Data” table, the formulas in my Analysis table get changed to…

        =COUNTIFS(Data[[comment]:[comment]],Analysis[[#Headers],[3]],Data[[feedback]:[feedback]],Analysis[[ClubID]:[ClubID]])

        This is incredibly infuriating, as my reports keep breaking, as the developers work their magic on the eCommerce components. Is there a way to write my analysis formula, so that absolute structured references will always refer to the correct column, while remaining absolute?

        Thanks for any help you can provide.

        Reply
        • There are a few ways out of this situation. Are you using excel 2010 or higher? You can try Power Query in this case, to import the new data into your Data sheet.
          Another way out is to use vba to import data by columns, this way your Data headers will always remain the same, and you will import the corresponding columns from the source file into the correct column.
          Changing data headers in the destination file, to match the position of the source columns, will allow you to paste only data, not headers. By default, when you rename a column in a table, excel will automatically update that name in all formulas from that workbook, and this is a very good behavior, but in your case, pasting data including headers is not a good option. Rearranging columns in a defined table is easy, just drag the header cell in the position you want.
          Catalin

          Reply
      4. Is there a way to refer to a cell in a seperate table using lookup criteria from the initial table? I got errors with power pivot since my data contains too many duplicates to create relationships. Essentially I want to compare Bob’s budget each month to his forecast in a seperate table, while avoid using a vlookup to ease reading the formula. If combinations of syntax or structured references can look up say an employee number in a range of rows of the other data table, that would be great

        Reply
        • Hi Alexander,
          That’s what Power Pivot does. Load your lookup table to power pivot and add a relationship between data table and lookup table, that’s all you need.
          Or, you can load to data model both the data table and the lookup table, and you can merge them into a single table.
          Catalin

          Reply
      5. Mynda,

        This is great. There is always so much to learn with Excel. Favorite part by far is how to actually use absolute referencing structured references.

        Honestly because of the flexibility of Excel Tables to expand without having to use offset formulas in name ranges, why wouldn’t you want to use them all of the time.

        Thanks

        Brad

        Reply
        • I agree completely, Brad 🙂 I’m happy to put up with the few limitations of Tables to have the structured reference functionality.

          Mynda

          Reply
      6. Dear Phil & Mynda,
        Thanks for an excellent tutorial and a superb add-in. I will personally thank Jon on his website.
        You have a knack of coming out with something new / arcane everytime you post. So glad to receive your tips.
        Thank you very much!!!
        Adi 🙂

        Reply
      7. Hi Mynda

        Another good tutorial from you about some tricky Excel functionality. However, whilst structured referencing has some advantages, I find them a pain in the proverbial :-(, because:
        (1) the “ease of deciphering” the formula is outweighed by the time-consuming fiddling around that is required to make the references absolute or relative as required, and
        (2) what appears to be the inability to have relative referencing when copying and pasting a structured reference. Copying and pasting of formulae and utilising the absolute/relative referencing that comes with that is one of the absolute fundamentals (pardon the pun) in the use of Excel. One should not have to drag across columns / down rows (or use fill handle?) to have structured references remain relative! What if your table has hundreds or thousands of rows/columns – dragging or using the fill handle should NOT be the only option.

        If I’ve missed something about using structured referencing with Excel tables that overcomes the latter problem when copying, I’d be very happy to be enlightened.

        Regards
        Col

        Reply
        • Hi Col,

          I agree that they are fiddly and should be more user friendly, although Jon’s free addin goes a long way to fixing their limitations.

          While the fill handle requirement is a pain I find if I drag it across the columns where required, I can then double click the bottom right corner of the cell containing my new formula to fill it down the column, so it’s a bit more painful than a regular formula but still worth the while.

          For me the benefits of the dynamic ranges that are built into Excel Tables makes them worth using and putting up with their absolute reference hassles.

          I hope that the absolute/relative limitations might be fixed in a future Excel release, however I don’t have any inside info on that, it’s just my wish!

          Mynda

          Reply
      8. Thank you Mynda! This is a great tutorial. I did not know about the relative references to multiple columns. That’s a great trick that I will have to incorporate into the add-in.

        Have a good one! 🙂

        Reply

      Leave a Comment

      Current ye@r *