How to use Excel formula references - A1 vs. $A$1 vs $A1 vs A$1 explained

Want to write better & smarter formulas? Then you must master the reference styles in Excel. There are 6 styles in Excel cell refs.
1) Relative reference - A1
2) Absolute reference - $A$1
3) Mixed reference style - $A1
4) Also mixed reference style - A$1
5) Table reference - Table[Column]
6) Table relative - [@Column]
In this video ⏱👇
==============
0:00 - Introduction to Excel reference styles (relative)
1:33 - How to make a reference Absolute
2:20 - Cell reference used in business situation - Example
3:49 - Using Mixed Reference style (A$1, $A1)
5:06 - Using Absolute Reference ($A$1)
6:57 - Using Reference Style in a Range ($A$1:A1) - IMPORTANT TIP
8:10 - Structural References
9:55 - Closing Remarks
Sample file 📗📈
==============
chandoo.org/wp/wp-content/upl...
Learn more about Excel formulas 📚👇
===============================
Top 10 Excel formulas for data analysis work - • Learn these top 10 Exc...
10 Awesome things you can do in Excel without Formulas - • 10 awesome things you ...
Data Analysis in Excel - Beginner to PRO course - • Beginner to Pro FREE E...
#ExcelReferences

[@patrickschardt7724]

Straight to the point with interesting facts and usually some humor. Chandoo for the win Bonus tip: when copying by dragging a table reference, it will act like a relative reference. To make it absolute, use the range operator, : Example - Table1[[Column1]:[Column1]]

[@chandoo_]

Thanks Patrick. Awesome Bonus tip btw...

[@GeertDelmulle]

Furthermore: you can combine the range operator with the @ (I call that the row context operator) such that you can make absolute references in column formulas. In conclusion: there are no limitations when using structured references and they are easier to interpret than the classic cell references, IMO.

[@yashmittal4744]

Hey Patrick I am trying to learn Excel so could you please explain the point you have mentioned in your comment.

[@sarnathk1946]

No fluff. Only pure content. That's why we listen to Chandoo!!! Well done!! Thanks!! The table name was something that was hurting me earlier.. glad I watched this...

[@chandoo_]

Thanks Sarnath... Table names make our life simple.

[@dakshbhatnagar]

I am a simple man. I see a video, I find it helpful, I press like to help the creator.

[@sarnathk1946]

I press like to help people who help creator... :) I m simply simple.... :)

[@chandoo_]

I appreciate that.

[@abhilashn2993]

Awesome explanation ....you are rockstar chandoo...............

[@sawaimalhi8439]

great concepts love you sir

[@jonk3361]

Always learning NEW tips and tricks from your videos. Today was mostly about that running total I've not used that for a long time and you've just refreshed it for me.

[@ubaidillahmuhammad20]

loved it

[@cinthiacicilio]

I never knew there were 6 styles for referencing til today, thanks Chandoo!

[@kss2066]

loved the lesson

[@guptavikas9681]

You have the best ideas.

[@canirmalchoudhary8173]

I expect more basic tutorials like this

[@chandoo_]

Thanks Nirmal. I will be adding more beginner tutorials in next few weeks.

[@lakbaylaboy2644]

Present! attendance check. Teacher Chandoo. more power!

[@chandoo_]

Lakbay ✔

[@amanmalalo8653]

Nice.

[@HrishikeshBhardwaj-hk5xw]

The Gunar Cockshoot guy always gets my attention whether I am watching your videos or using your sample worksheets for practice

[@chandoo_]

GFC - Gunar Fan Club 😂

[@HrishikeshBhardwaj-hk5xw]

@@chandoo_ Haha! Had no idea this guy had such a fanbase. You've got competition, Chandoo bhaiya!

[@ramadiga5099]

That F4 pop up sound brings smile on my face and respect for your creativity increased in my 💓

[@chandoo_]

😀 Oh, well. You will see it randomly pop-up in a few more videos then.

[@priteshthakker]

Indeed a very crucial aspect for building excel reports

[@chandoo_]

Bingo!

[@wayneedmondson1065]

Hi Chandoo. A great summary of reference styles! Thanks for sharing :)) Thumbs up!!

[@chandoo_]

You are welcome Wayne :)

[@syedm4657]

Excellent...\very clear and neat .

[@chandoo_]

Glad it was helpful!

[@michellegill1278]

Excellent video!!!

[@Ljirao]

Super...👍👌👏👏

[@chandoo_]

Thanks Ljirao...

[@abhisheksaroj9248]

Thank you chando you made me crystal clear related to reference 🥰.

[@kushkhan3219]

Thanks sir share good knowledge .

[@chandoo_]

You are welcome Kush...

[@pandharinathjoshi6565]

Nice sir

[@chandoo_]

Thanks Pandarinath...

[@rajamk6855]

Amazing contents Chandoo.....

[@partymaschine92]

@Chandoo, Thanks for sharing this really handy explained content! I really love the way you explain things. However, I would like to add following construction when excel intelligent tables are used: table1[[column1]:[column1]] this will fix the column when you need to spill to the left hand side or right hand side. Of course referring to a row value with @ sign will just work as usual ~best regards from Germany

[@chandoo_]

Very good tip Freshwood... I normally use the Copy / Paste option to fix my table references (if you drag the formula, the refs change, but if you copy paste they remain same). But using col:col is a cool option :)

[@bittubsb]

Greeeeeeeat...

[@chandoo_]

Thankyooooou..

[@pk3846]

Thank you Chandoo... Was waiting for it.

[@chandoo_]

You are welcome PK.

[@ryanschumacher5149]

Chandoo had some fun with the sound effects. 😆 Thank you for the great video! You presented the information very clearly with examples. 👍

[@chandoo_]

I am just experimenting.. Looks like we found a winner.

[@jagatkrishna1543]

Thanks Sir 🙏💕

[@chandoo_]

You are welcome JK...

[@MARC1TIM]

I use named ranges for absolutes whenever I can.... To me it makes easier to read and proof. Good job though

[@yuthakrainarong265]

Very constructively useful basics.

[@chandoo_]

Thanks Yutha...

[@shabarilucky5]

I use most of them daily but I don't know the technical names of those as mixed references and tables references. Thank you chandoo 🤝

[@BigCliq]

Thanks

[@ysantosh]

Nice video anna. (Chandoo = good content)

[@chandoo_]

Thanks Santosh... 😍

[@dcpowered]

Chandoo!! Please make a video about excel data models!

[@chandoo_]

I will. That is on the cards.

[@shaikusman536]

Hey Chandoo you are $AWSOME$CHANDOO

[@johannesmp3]

Great video, Chandoo. What is the difference between referring to a column in a table using the at symbol vs not using it

[@chandoo_]

The [@column] syntax only gets you current row data in the table. Normally, it is used only inside a table for adding extra calculations (as new columns) [Column] syntax refers to entire column of values and useful for doing operations both inside & outside the table. Practice them on the sample file in this video and you should know how & when to use them.

[@himanshubatra7410]

Hi Chandoo, Please make videos on some topics: Indirect() function, getpivotdata() function, powerpivot, I need to learn it, I understand things very clear from you. Thank you :)

[@chandoo_]

Thanks for the suggestions Himanshu. I have content on all these on my website / KZbin. Check out: Getpivotdata - chandoo.org/wp/getpivotdata-in-dashboards/ Power Pivot - kzbin.info/www/bejne/m3TYgYWnhrOYbLs

[@himanshubatra7410]

@@chandoo_ sure, thank you Chandoo Anna. :)

[@bhavanareddy6637]

Bro.... please start giving classes in Telugu on powerbi 🙏🏼🙏🏼🙏🏼🙏🏼.

[@chandoo_]

Thanks Bhavana... I might try to do a Power BI telugu live sometime this year. Let's see.

[@Movies_Clips811]

on a different note can you explanin iMacros for chrome for data automation i find it difficult.

[@chandoo_]

Not sure what iMacros is. I will research it and if I find it interesting, I will make a video.

[@venkym8159]

Pls do in telugu language same video

[@sreejeshjnair6755]

Not mention about the rc type reference

[@chandoo_]

Normally, we don't use R1C1 style. So there is no value in learning that at beginner stages. But the other things you should consider (might add them in a future video) are, Named References Off sheet & Off workbook references 3D references Spill references

[@mirayunos4976]

I hope you dont mind but can you please share the blank data file too? I rely a lot on your sample for exercise :)

[@chandoo_]

Certainly I don't mind. The file is available (has always been) in the video description under "sample file" section. Happy learning Mira 😀

[@JoseAntonioMorato]

Dear Chandoo, How to fix the reference of a cell, inside a table, as it was done, in REF 3, with the formula SUM($E$6:E6), to arrive at the total amount? 🤔

[@chandoo_]

GREAT Question Jose. I will make a video about this for sure. But here is one way to do it. =SUM(sales[[#Headers],[Amount]]:[@Amount]) SUM formula ignores any text values in the data, so it will sum up running total inside the table. Just change the column names based on your table.

[@JoseAntonioMorato]

@@chandoo_ Dear Chandoo, I didn't know the use of the header in the formula. It was very good. ❤ Until now, I used the following formula: =SUM(INDEX([Amount],1):[@Amount]). I'm going to use the method you described and which I thought was really cool. 👍 Hugs. 🤗

[@adan8657]

You can also name a single cell,.

[@chandoo_]

Good one...

[@sriramnavin4975]

Hii Chandoo....I joined as Process Executive in infosys BPM where in Data Management and Manipulation (DMM) i belong to MDM department (Master Data Management) and my role is said to be as DA (Data Analyst) ....i know that this role dont actually focus on using power BI and other visualization tools or even python. But i have good knowledge on Power BI and as well as Data analysis. Can i gain experience in this MDM dept in infosys so that i can mention it in my Resume as had experience as an Data Analyst. Thank you in Advance.

[@chandoo_]

Sorry Sriram. I am not familiar with the inner workings of Infosys to comment. I suggest reaching out to someone else in the organization who is working the position you aspire and getting some mentorship.