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]]
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.
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...
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.
@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
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 :)
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.
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 :)
Thanks for the suggestions Himanshu. I have content on all these on my website / RUclips. Check out: Getpivotdata - chandoo.org/wp/getpivotdata-in-dashboards/ Power Pivot - ruclips.net/video/eCuPRqQNe6Y/видео.html
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? 🤔
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.
@@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. 🤗
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
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.
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.
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]]
Thanks Patrick.
Awesome Bonus tip btw...
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.
Hey Patrick I am trying to learn Excel so could you please explain the point you have mentioned in your comment.
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...
Thanks Sarnath... Table names make our life simple.
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.
Hi Chandoo. A great summary of reference styles! Thanks for sharing :)) Thumbs up!!
You are welcome Wayne :)
I am a simple man. I see a video, I find it helpful, I press like to help the creator.
I press like to help people who help creator... :) I m simply simple.... :)
I appreciate that.
That F4 pop up sound brings smile on my face and respect for your creativity increased in my 💓
😀 Oh, well. You will see it randomly pop-up in a few more videos then.
I never knew there were 6 styles for referencing til today, thanks Chandoo!
Thanks sir share good knowledge .
You are welcome Kush...
Indeed a very crucial aspect for building excel reports
Bingo!
loved the lesson
I expect more basic tutorials like this
Thanks Nirmal. I will be adding more beginner tutorials in next few weeks.
Awesome explanation ....you are rockstar chandoo...............
Super...👍👌👏👏
Thanks Ljirao...
Excellent...\very clear and neat .
Glad it was helpful!
Very constructively useful basics.
Thanks Yutha...
great concepts love you sir
loved it
Nice.
@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
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 :)
Present! attendance check. Teacher Chandoo. more power!
Lakbay ✔
Nice sir
Thanks Pandarinath...
Chandoo had some fun with the sound effects. 😆 Thank you for the great video! You presented the information very clearly with examples. 👍
I am just experimenting.. Looks like we found a winner.
You have the best ideas.
Thank you Chandoo... Was waiting for it.
You are welcome PK.
Thank you chando you made me crystal clear related to reference 🥰.
The Gunar Cockshoot guy always gets my attention whether I am watching your videos or using your sample worksheets for practice
GFC - Gunar Fan Club 😂
@@chandoo_ Haha! Had no idea this guy had such a fanbase. You've got competition, Chandoo bhaiya!
Thanks Sir 🙏💕
You are welcome JK...
Amazing contents Chandoo.....
Excellent video!!!
Greeeeeeeat...
Thankyooooou..
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 🤝
Chandoo!! Please make a video about excel data models!
I will. That is on the cards.
I use named ranges for absolutes whenever I can.... To me it makes easier to read and proof. Good job though
Great video, Chandoo. What is the difference between referring to a column in a table using the at symbol vs not using it
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.
on a different note can you explanin iMacros for chrome for data automation i find it difficult.
Not sure what iMacros is. I will research it and if I find it interesting, I will make a video.
Nice video anna. (Chandoo = good content)
Thanks Santosh... 😍
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 :)
Thanks for the suggestions Himanshu.
I have content on all these on my website / RUclips. Check out:
Getpivotdata - chandoo.org/wp/getpivotdata-in-dashboards/
Power Pivot - ruclips.net/video/eCuPRqQNe6Y/видео.html
@@chandoo_ sure, thank you Chandoo Anna. :)
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? 🤔
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.
@@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. 🤗
I hope you dont mind but can you please share the blank data file too? I rely a lot on your sample for exercise :)
Certainly I don't mind. The file is available (has always been) in the video description under "sample file" section. Happy learning Mira 😀
Not mention about the rc type reference
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
Thanks
Hey Chandoo you are $AWSOME$CHANDOO
Bro.... please start giving classes in Telugu on powerbi 🙏🏼🙏🏼🙏🏼🙏🏼.
Thanks Bhavana... I might try to do a Power BI telugu live sometime this year. Let's see.
Pls do in telugu language same video
You can also name a single cell,.
Good one...
chandoo is chandoo....unmatchable guy
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.
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.