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

แชร์
ฝัง
  • เผยแพร่เมื่อ 28 ส.ค. 2024

ความคิดเห็น • 84

  • @patrickschardt7724
    @patrickschardt7724 3 ปีที่แล้ว +10

    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_
      @chandoo_  3 ปีที่แล้ว +2

      Thanks Patrick.
      Awesome Bonus tip btw...

    • @GeertDelmulle
      @GeertDelmulle 2 ปีที่แล้ว

      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
      @yashmittal4744 2 ปีที่แล้ว

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

  • @sarnathk1946
    @sarnathk1946 3 ปีที่แล้ว +5

    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_
      @chandoo_  3 ปีที่แล้ว

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

  • @dakshbhatnagar
    @dakshbhatnagar 3 ปีที่แล้ว +4

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

    • @sarnathk1946
      @sarnathk1946 3 ปีที่แล้ว +1

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

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      I appreciate that.

  • @jonk3361
    @jonk3361 3 ปีที่แล้ว

    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.

  • @canirmalchoudhary8173
    @canirmalchoudhary8173 3 ปีที่แล้ว +1

    I expect more basic tutorials like this

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +2

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

  • @HrishikeshBhardwaj-hk5xw
    @HrishikeshBhardwaj-hk5xw 5 หลายเดือนก่อน

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

    • @chandoo_
      @chandoo_  5 หลายเดือนก่อน

      GFC - Gunar Fan Club 😂

    • @HrishikeshBhardwaj-hk5xw
      @HrishikeshBhardwaj-hk5xw 5 หลายเดือนก่อน

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

  • @cinthiacicilio
    @cinthiacicilio 2 ปีที่แล้ว

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

  • @guptavikas9681
    @guptavikas9681 5 หลายเดือนก่อน

    You have the best ideas.

  • @ramadiga5099
    @ramadiga5099 3 ปีที่แล้ว

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

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

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

  • @wayneedmondson1065
    @wayneedmondson1065 2 ปีที่แล้ว +1

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

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว +1

      You are welcome Wayne :)

  • @priteshthakker
    @priteshthakker 3 ปีที่แล้ว

    Indeed a very crucial aspect for building excel reports

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Bingo!

  • @partymaschine92
    @partymaschine92 3 ปีที่แล้ว +3

    @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_
      @chandoo_  3 ปีที่แล้ว +1

      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 :)

  • @syedm4657
    @syedm4657 3 ปีที่แล้ว

    Excellent...\very clear and neat .

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Glad it was helpful!

  • @kss2066
    @kss2066 3 ปีที่แล้ว +1

    loved the lesson

  • @MARC1TIM
    @MARC1TIM 2 ปีที่แล้ว

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

  • @michellegill1278
    @michellegill1278 9 หลายเดือนก่อน

    Excellent video!!!

  • @abhisheksaroj9248
    @abhisheksaroj9248 3 ปีที่แล้ว

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

  • @lakbaylaboy2644
    @lakbaylaboy2644 3 ปีที่แล้ว

    Present! attendance check. Teacher Chandoo. more power!

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Lakbay ✔

  • @pk3846
    @pk3846 3 ปีที่แล้ว

    Thank you Chandoo... Was waiting for it.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome PK.

  • @kushkhan3219
    @kushkhan3219 3 ปีที่แล้ว

    Thanks sir share good knowledge .

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome Kush...

  • @sawaimalhi8439
    @sawaimalhi8439 ปีที่แล้ว

    great concepts love you sir

  • @yuthakrainarong265
    @yuthakrainarong265 3 ปีที่แล้ว

    Very constructively useful basics.

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Yutha...

  • @shabarilucky5
    @shabarilucky5 3 ปีที่แล้ว

    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 🤝

  • @rajamk6855
    @rajamk6855 5 หลายเดือนก่อน

    Amazing contents Chandoo.....

  • @amanmalalo8653
    @amanmalalo8653 3 ปีที่แล้ว +1

    Nice.

  • @abhilashn2993
    @abhilashn2993 ปีที่แล้ว

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

  • @ryanschumacher5149
    @ryanschumacher5149 3 ปีที่แล้ว

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

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

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

  • @Ljirao
    @Ljirao 3 ปีที่แล้ว +1

    Super...👍👌👏👏

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Ljirao...

  • @jagatkrishna1543
    @jagatkrishna1543 3 ปีที่แล้ว +1

    Thanks Sir 🙏💕

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      You are welcome JK...

  • @775shahrukh
    @775shahrukh 25 วันที่ผ่านมา

    chandoo is chandoo....unmatchable guy

  • @ubaidillahmuhammad20
    @ubaidillahmuhammad20 3 ปีที่แล้ว +1

    loved it

  • @BigCliq
    @BigCliq 5 หลายเดือนก่อน

    Thanks

  • @pandharinathjoshi6565
    @pandharinathjoshi6565 3 ปีที่แล้ว

    Nice sir

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thanks Pandarinath...

  • @bittubsb
    @bittubsb 3 ปีที่แล้ว

    Greeeeeeeat...

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      Thankyooooou..

  • @dcpowered
    @dcpowered 2 ปีที่แล้ว

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

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      I will. That is on the cards.

  • @himanshubatra7410
    @himanshubatra7410 3 ปีที่แล้ว

    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_
      @chandoo_  3 ปีที่แล้ว +1

      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

    • @himanshubatra7410
      @himanshubatra7410 3 ปีที่แล้ว

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

  • @ysantosh
    @ysantosh 3 ปีที่แล้ว

    Nice video anna. (Chandoo = good content)

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      Thanks Santosh... 😍

  • @bhavanareddy6637
    @bhavanareddy6637 3 ปีที่แล้ว

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

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

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

  • @johannesmp3
    @johannesmp3 3 ปีที่แล้ว

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

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว

      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.

  • @shaikusman536
    @shaikusman536 3 ปีที่แล้ว

    Hey Chandoo you are $AWSOME$CHANDOO

  • @JoseAntonioMorato
    @JoseAntonioMorato 3 ปีที่แล้ว +1

    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_
      @chandoo_  3 ปีที่แล้ว

      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
      @JoseAntonioMorato 3 ปีที่แล้ว

      @@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. 🤗

  • @Movies_Clips811
    @Movies_Clips811 3 ปีที่แล้ว

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

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

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

  • @venkym8159
    @venkym8159 ปีที่แล้ว

    Pls do in telugu language same video

  • @mirayunos4976
    @mirayunos4976 2 ปีที่แล้ว

    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_
      @chandoo_  2 ปีที่แล้ว

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

  • @sreejeshjnair6755
    @sreejeshjnair6755 3 ปีที่แล้ว

    Not mention about the rc type reference

    • @chandoo_
      @chandoo_  3 ปีที่แล้ว +1

      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

  • @adan8657
    @adan8657 2 ปีที่แล้ว

    You can also name a single cell,.

    • @chandoo_
      @chandoo_  2 ปีที่แล้ว

      Good one...

  • @sriramnavin4975
    @sriramnavin4975 3 ปีที่แล้ว

    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_
      @chandoo_  3 ปีที่แล้ว +1

      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.