Excel Tables - Absolute Column and Cell References
ฝัง
- เผยแพร่เมื่อ 23 ก.ค. 2024
- 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.
Learn more than 150 Excel formulas amzn.to/3Rg87Go
You can download the Excel workbook used in the tutorial here - bit.ly/3vHdUer
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;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - แนวปฏิบัติและการใช้ชีวิต
![รวมเพลงลูกทุ่ง 6 หนุ่มไทบ้าน ฮิตติดกระแส ชุดที่ 1 l มนต์แคน ไมค์ ไผ่ เสถียร ไหมไทย พี [Longplay]](http://i.ytimg.com/vi/GD2v1OjMw5I/mqdefault.jpg)
Really good teaching. The flow from one example into the next was really well structured.
Thank you, buddy.
Brilliant! Thank you for explaining this with do much detail.
Thanks, Celia.
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!
My pleasure
Thank you for the tutorial. Our beloved structured references!!!
I absolutely love them 😊
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.
You're welcome, Jason. Yes, hopefully they do. I completely agree.
Agreeee
Thank you very much. I was looking for this tutorial for months.
Happy to help. Thank you.
I've been looking for this...thanks a lot.
You're welcome. Happy to help.
thanks, it was exactly what i wanted to know and more, keep up the good work
Thanks, will do!
First video that was actually able to help me with this issue. Thank you!!
You're welcome!
Really Great Helpful Tips...Thank You Alan :)
Thank you, Darryl.
Very useful, it is very interesting and new issue. Thanks a lot.
Thank you, Lotfy.
Thank you so much.
You're welcome, Mark. Thank you.
Great video. There are not much on this subject and i was looking for exactly the same!!.👍👍😘😘
Glad you liked it!! Thank you 👍
What if we need to fix rows too in the same table scenario?. Plz make a short video on that🙏❤️
Thanks
You're welcome, Tai.
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!!
Thank you, Wayne. Yes, many people copy and paste the formula from the bar to prevent the changing column references.
Ctrl + R is a nice method.
Thank you. Maybe I should use tables a lot more.
You're welcome, Mark. Yes, you definitely should 😊 I love them.
I don’t use absolute table references often so it’s always great to have a reminder
👍
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?
Thanks, Luc. I am not sure exactly what you mean by the question, but I have videos on using INDIRECT with table columns headers. Also using XLOOKUP with a column header specified by a cell value. And also a recent video on sorting by a column selected from a drop-down - th-cam.com/video/Hj_mkYdin7k/w-d-xo.html
@@Computergaga Thank you for your answer. The way to solve my issue is with indirect but I was making a mistake in the way I wrote the formula. Now it works fine.
This is the secret property and I think nobody knows.
Thank you very much 🤝
Thank you, Emre 👍
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.
I'm sure it is somewhere but don't have it to hand. Not sure of a key combinations though it does help a little as you type.
A neat trick is to select 2 column headers as Excel generates a range, that you can then edit. Saves half the typing.
Yes.
👍
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 :)
Thanks, Ruth. Dot above the number?
Hi Sir, i am not aware of the mathematical term, but when a number goes on forever in a calculation, to save time, some of my teacher put a dot above the number to signal it is a repetitive number. I would rlly appreciate u showing us how to do that.
Sorry, I don't know this.
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?
Happy to help, Ankur. No, you will need the classic absolute reference with the $ for cell ranges.
@@Computergaga thank you!
Hello sir, am anoop from mauritius. Am working on loto lottery,and i have come to a conclusion that we can win that. Sir. Am not good in excel. Please show me how to arrange the number already played so that i can arrange other number which is to be calculated from the formula you'd published. Please help me
How can u choose table column, not table column?
Sorry, I don't understand the question. But you can either type the table followed by column in the formula, or you can click the table header to reference it.
@@Computergaga in your excel, when you select coulmn B, it is =AVERAGE(Grades[Maths]), when i select in a new excel that i created is =AVERAGE(B2:B7). mine does not show [ ], do you know why
@@Computergaga I have worked out because your data is table . Thx very much
Great! Finally (after months searching) I know how to do that!
Thank you very much!
I really win my day...
Glad I could help, Carlos.