Excel Tables - Absolute Column and Cell References

In this video, we look at how to make Excel table cell and column references absolute.

It surprises many that the structured references used by Excel tables are relative. Many you copy a formula, the table references change.

But you can lock table column and cell references. This video will show you the technique to make table references absolute with two examples.

Timings for the video are:
00:00 - Introduction
00:32 - Table columns references changing - and we want it
01:54 - Table columns changing - this is wrong
03:25 - Interesting table reference behaviour
04:17 - Make an absolute table column reference
05:21 - Make an absolute table cell reference absolute

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[ExcelOffTheGrid]

Really good teaching. The flow from one example into the next was really well structured.

[jasonjackson]

Thanks for the tip. I’m beginning to use tables more often at work and this is exactly what I needed to know. I’m very disappointed in Microsoft for making such a common task so difficult to do in tables. I have years of muscle memory of just hitting F4 a few times to lock in a reference. Now I actually have to retype the column name and add a series of brackets and colons! Hopefully they improve this someday.

[CarlosSantos-mzkj]

Great! Finally (after months searching) I know how to do that!
Thank you very much!
I really win my day...

[wmfield]

This is a helpful demonstration, one that I can put to use right away--and avoid a lot of T&E to finally get right. Thanks as always, Alan!

[capricenutt]

First video that was actually able to help me with this issue. Thank you!!

[islamdp]

Thank you very much. I was looking for this tutorial for months.

[THUNDERxSCREAM]

thanks, it was exactly what i wanted to know and more, keep up the good work

[IvanCortinas_ES]

Thank you for the tutorial. Our beloved structured references!!!

[tanweerabbasabbas]

Great video. There are not much on this subject and i was looking for exactly the same!!.👍👍😘😘

[nomondenozulu]

I've been looking for this...thanks a lot.

[patrickschardt]

I don’t use absolute table references often so it’s always great to have a reminder

[LotfyKozman]

Very useful, it is very interesting and new issue. Thanks a lot.

[markpodesta]

Thank you. Maybe I should use tables a lot more.

[darrylmorgan]

Really Great Helpful Tips...Thank You Alan :)

[wayneedmondson]

Hi Alan. Awesome tips! This is always a source of confusion. Thanks for the clear explanation. Interesting to note that if your references are all meant to be absolute, then instead of dragging the fill handle, you can use copy/paste or CTRL+R and you don't need to add the extra brackets and second column ID reference. But, if you have mixed absolute and relative needs, like in your example, then the copy/paste or CTRL+R method won't work (it will treat them all as absolute). Glad to know the correct syntax which will work no matter if you drag the fill handle, copy/paste or CTRL+R. Always clever tricks coming from Computergaga :)) Thanks and Thumbs up!!

[teoxengineer]

This is the secret property and I think nobody knows.
Thank you very much 🤝

[ankurj]

I recently ran into this issue and thought how could I tackle it (making excel official table's cell an absolute reference) but couldn't find the answer and had to go back and convert my official excel table to range, make the needed cell as an absolute reference and then I proceeded with my work. I am glad that I found the answer now. Thanks Alan! BTW does this trick also works on cell ranges too if you want to make cell range an absolute reference?

[lucmorineau]

Thanks for this helpful video. I'm looking for another use case where I would like to make a kind of indirection of the column header to address the targetted column in a formula. Do you have some suggestion to do it?

[rrrrrr]

hi, like i have said so many times before, you are very helpful. I do have one question though: how do you put a dot above the number when typing? Thanks :)

[TropicScand]

Is this explained anywhere in the Help Pages for Excel or a Microsoft Support web page? I am looking for a key combination that does the [[column]:[column]] inset for me.