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 :)
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 / TH-cam. Check out: Getpivotdata - chandoo.org/wp/getpivotdata-in-dashboards/ Power Pivot - th-cam.com/video/eCuPRqQNe6Y/w-d-xo.html
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.
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.
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.
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.
I expect more basic tutorials like this
Thanks Nirmal. I will be adding more beginner tutorials in next few weeks.
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!
I never knew there were 6 styles for referencing til today, thanks Chandoo!
You have the best ideas.
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.
Hi Chandoo. A great summary of reference styles! Thanks for sharing :)) Thumbs up!!
You are welcome Wayne :)
Indeed a very crucial aspect for building excel reports
Bingo!
@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 :)
Excellent...\very clear and neat .
Glad it was helpful!
loved the lesson
I use named ranges for absolutes whenever I can.... To me it makes easier to read and proof. Good job though
Excellent video!!!
Thank you chando you made me crystal clear related to reference 🥰.
Present! attendance check. Teacher Chandoo. more power!
Lakbay ✔
Thank you Chandoo... Was waiting for it.
You are welcome PK.
Thanks sir share good knowledge .
You are welcome Kush...
great concepts love you sir
Very constructively useful basics.
Thanks Yutha...
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 🤝
Amazing contents Chandoo.....
Nice.
Awesome explanation ....you are rockstar chandoo...............
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.
Super...👍👌👏👏
Thanks Ljirao...
Thanks Sir 🙏💕
You are welcome JK...
chandoo is chandoo....unmatchable guy
loved it
Thanks
Nice sir
Thanks Pandarinath...
Greeeeeeeat...
Thankyooooou..
Chandoo!! Please make a video about excel data models!
I will. That is on the cards.
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 / TH-cam. Check out:
Getpivotdata - chandoo.org/wp/getpivotdata-in-dashboards/
Power Pivot - th-cam.com/video/eCuPRqQNe6Y/w-d-xo.html
@@chandoo_ sure, thank you Chandoo Anna. :)
Nice video anna. (Chandoo = good content)
Thanks Santosh... 😍
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.
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.
Hey Chandoo you are $AWSOME$CHANDOO
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. 🤗
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.
Pls do in telugu language same video
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
You can also name a single cell,.
Good one...
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.