When Should You Use the Hash Sign in Excel Formulas?

Рет қаралды 1,077,693

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
The Hash sign or the pound sign (#) in an Excel formula allows you to easily reference an entire spilled range. You can only use this if you have Office 365 version and already have Excel functions like filter, unique and sort. This way Your Excel formula will automatically adjust and include the whole range as soon as the source data changes.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/hash-formul...
I'll show you how and when you can use the # reference in Excel formulas and also when you shouldn't be using the hash reference in your excel formulas.
🔍 Key Takeaways:
▪️ Learn about the use of the hash sign (#) in Excel formulas.
▪️ Understand how and where you can't use the hash sign.
▪️ Discover the concept of spilled ranges in Excel.
▪️ Get insights into practical applications of the hash sign in formulas.
▪️ Find out why you can't use certain functions like Filter, Sort, and Unique in Excel tables.
▪️ Bonus tip: How to select specific parts of a spilled range with the Index function.
If your Excel hash reference doesn't work, it's likely because you aren't referencing a spilled range in Excel. It can also be that you're using the reference inside an Excel table. In which case you'll see the spill error message (#spill). In this video I also explain why you can't use dynamic array functions like filter, sort and unique inside excel tables.
If you have Excel for Microsoft 365 you already have this functionality.
Link to Excel Functions in 2021 video: • 5 Excel Functions EVER...
Link to my COMPLETE Excel Course about the NEW Dynamic Array functions: courses.xelplus.com/p/excel-d...
00:00 What does the Hash / Pound Sign mean in an Excel Formula
06:08 Practical Example for the Hash Sign in Excel
07:18 Why You Can't Use Filter, Sort and Unique in Excel Tables (and other ranges that spill)
09:12 Bonus! How To Select Specific Part of a Spilled Range in Excel with Index Function
LINKS to complete playlist about new functions in Excel 365: • Excel for Office 365 &...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #Office365

Пікірлер: 543

@LeilaGharani 9 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/hash-formula-file

@mrk8212 2 жыл бұрын

I am considered an expert at work, but this teacher shows me that I don't know anything about XL.

@mgpvii 2 жыл бұрын

Yes, same here but in addition this teacher makes me feel that I’m stupid.

@rowlandks 2 жыл бұрын

Right!!

@FaaBioo972 2 жыл бұрын

Same here without a doubt 😅

@Uncensored-Updates 2 жыл бұрын

Whenever I watch her videos ,I feel like I have learnt nothing about excel.

@brandonly27 Жыл бұрын

Half of the stuff she talks about I can't use. Because i only have non-365 office.

@lowkey007_ 3 жыл бұрын

Being a student who wants to up my Excel skills, your contents are pure gold! Thank you very much! 🙏🏼

@colormile-vid8888 3 жыл бұрын

Thank you Leila! As always your videos are very practical, useful and very informative.

@blakwngbrd 2 жыл бұрын

Thank you for this introduction! I love your teaching style. You do a great job of anticipating questions and answering them just as I think of them :)

@LeilaGharani 2 жыл бұрын

You are so welcome!

@komiku666 3 жыл бұрын

Leila, many thanks for being there for us ❤️ your tutorials make my life a lot easier

@darielhayes5547 2 жыл бұрын

you prolly dont care but does someone know of a tool to get back into an Instagram account..? I somehow lost my login password. I would love any assistance you can offer me!

@nomanaslam6305 2 жыл бұрын

Hi, I have just created a complete dynamic dashboard using MS FORMs and Excel. Thanks to your simple and structured videos. Looking forward to learn more. Please make a series of videos on power query

@rogerangel4257 3 жыл бұрын

You got me when you showed the practical example. Thank you for the video!

@irwankurniawan1737 2 жыл бұрын

I've been questioning this # for years. It's clear now. Awesome presentation, thanks!

@rosalieduallo1837 3 жыл бұрын

Hello Ms. Leila, you're great. Love to watch your videos, really helpful❤️

@siddharthranawat1814 3 жыл бұрын

One thing I must say leila we can see the hard work you put into your videos you audio quality is clear, you always look great in your video, you cover every part of the topic when you make the video and the videos are available in highest quality. You are an real perfectionist 😄😄😇😊

3 жыл бұрын

You are great! One my favorite channels on KZfaq. So useful!

@giossicom 3 жыл бұрын

TBH this channel is amazing, fantastic content and stunning speech.

@andrew7586 2 жыл бұрын

Great video on the hash function. I was trying to find a way to get an auto expanding two-variable table where the 'x' and 'y' values of my data table were created by SEQUENCE function but didn't know the HASH operator existed until now.

@lindsayhemy2064 2 жыл бұрын

Thankyou Leila, your voice is very easy to listen to, although I have completed some excel courses years ago I know very little and I am enjoying playing with excel again in my retirement. I am using excel to automate the starting grids at our local speedway. I am sure that after watching a few more of your lessons I will be able to make a lot of improvements Thanks again Lindsay

3 жыл бұрын

I am improving on Excel, thank you Leila, you are genius.

@herrsan 3 жыл бұрын

I really like the way you explain things. Please keep up the great work!

@wtfisthishandlebs 3 жыл бұрын

This lady is the flippen no.1. Thanks for everything Leila

@douglasbrown3354 3 жыл бұрын

Excellent video instruction! Your teaching on the # sign was clear and concise. Thank you! I also did not know about the spilled range and what that was all about. I assume that the # sign can be used as part of a dashboard report (it kind of reminds me of Excel's Camera feature). Again, thank you!

@vijayarjunwadkar 3 жыл бұрын

Nice video as usual! Crisp explanation and to the point coverage, giving just enough to get the work done. Love your approach Leila! Liked the bonus tip, which is sure to help in many ways! Thanks again! 😊👍

@LeilaGharani 3 жыл бұрын

Much appreciated, Vijay!

@jamesduncanlinch6322 3 жыл бұрын

This tutorials are pure gold. I don’t work with excel normally, but this is not only clear and informative but enjoyable . I am going to share it to the moon. Thanks again!

@LeilaGharani 3 жыл бұрын

You're very welcome, James! I'm glad you like the tutorials.

@birgitjohannes6804 3 жыл бұрын

Thank you! Another great video. What I like in particular are your teaching skills, your pace and that you show us a practical example. And I could go on and on 😉: The time stamps, the hidden details and features you show us, your clear understandable English ( I´m not a native speaker) etc........

@LeilaGharani 3 жыл бұрын

Wow, thank you Birgit!

@Elathechick 3 жыл бұрын

Hi Leila, I love your videos and recommend them to my team at work. Thank you for putting out this content!

@LeilaGharani 3 жыл бұрын

Awesome! Thank you!

@TexasStar007 3 жыл бұрын

You are one of my top five best Leila!

@bijuraveendranathan8629 2 жыл бұрын

Learned how to sum the range obtained from FILTER formula using Sum, Index and # after watching this. Great learning experience. 🙏🙏🙏

@fayi85 3 жыл бұрын

Before I start watching a video, I click on Like icon, I am sure Ms. Leila will not disappoint me. Thank you for your great efforts 👍👍👍

@mohammedhassan2808 3 жыл бұрын

Excellent Leila, Each time you come with very unique lesson, keep it up

@jsfuntophia2674 2 жыл бұрын

Really good teacher. Makes the subject matter really ready to understand. Unfortunately not using 365 yet & i doubt my company will be upgrading soon😊

@isaacmwabila5830 2 жыл бұрын

Well explained as usual, Thank you Leila!👏👏👏👏👏👏👍🏾

@tarnaka504 3 жыл бұрын

Your voice is awesome...very easy to understand.. Thank you!

@boredomgotmehere 3 жыл бұрын

The best excel masterclass on youtube. Thank you Leila.

@LeilaGharani 3 жыл бұрын

Thank you! I'm glad you found this useful :)

@khangvutien2538 2 жыл бұрын

Great explanation. Thanks. I subscribed.

@YouExcelTutorials 3 жыл бұрын

Very useful tutorial Leila, thanks for sharing!

@ujjwaldeo6770 Жыл бұрын

Definitely useful...I am just picking up on a lot of Excel functionalities lately...wanna step up my Excel game for the future (taken up your Udemy course on VBA, too😊)....BUT...that bonus tip...Leila....that's simply awesome...I am still in awe of how there are so many Excel tricks and tools that APPEAR so simple...but the time and effort they can save us...really really great! Thanks for this.👍🏻🙂

@HumilityisaVirtue 4 ай бұрын

Your videos are a tremendous help.

@anatalstaya8221 3 жыл бұрын

Useful information and great explanation!

@flaviogarlatticosta 3 ай бұрын

Fantastic, a subject I didn't know, now a new world has opened up in front of me. Really good.

@tha2irtalib343 3 жыл бұрын

اسلوب التقدمة رائع و مبسط خالي من التعقيد ، اسلوب فريد ليس له مثيل تستحقين عليه الف نجمة ، شكرا ليلى على المشاركة ، كنت عونا متميزا لدخولنا لعالم الاكسل .

@victorsgbaptista 2 жыл бұрын

Great video! You teach Excel very well! Congrats!

@rohitshukla4652 3 жыл бұрын

You're one of the best excel instructor, I have enrolled to your various courses, I am deeply thankful to you and your team for always bringing out the best for all of us... lots of love from me and from India🙏❤

@LeilaGharani 3 жыл бұрын

Wow, thank you!

@ashishtrivedi8253 3 жыл бұрын

My feelings are mutual!

@elactecnology 2 жыл бұрын

Leila is a game changer with her special tips from Excel. Thank you

@LeilaGharani 2 жыл бұрын

Glad you think so!

@pwells2389 3 жыл бұрын

Very interesting and helpful as always. Liked the smooth nudge for subs 😃

@mattschoular8844 3 жыл бұрын

Thanks Leila. These tips are very helpful. Keep up the great work.

@LeilaGharani 3 жыл бұрын

Thank you, Matt! Will do!

@harishnegi0903 3 жыл бұрын

Thanks for the video I have subscribed your channel Very helpful

@jasonperry6046 3 жыл бұрын

KZfaq just introduced me to your channel. I am looking forward to watching more of your videos. You do a great job at describing how this function works.

@x4ms 4 ай бұрын

Thank you for this excel-lent presentation.

@neldopardoyuvero5181 3 жыл бұрын

Since I found you here in KZfaq I love your videos!!!!

@MisterD28 6 ай бұрын

Leila’s videos are great! You can always learn something new, useful, and fun! I would recommend checking out her courses.

@fernandomessala7194 3 жыл бұрын

Thanks for sharing your knowledge with the community! It seems to be a no-end road when trying to learn Excel.

@LeilaGharani 3 жыл бұрын

There's always more to learn :)

@elcidioalfredoalfredo2172 3 жыл бұрын

Thank you very much Leila. What a wonderful Video.

@benjaminr.6077 3 жыл бұрын

Thanks a lot! It answers all my questions

@mypalbill 3 жыл бұрын

Just discovered your channel; amazing work, really appreciate the videos!

@LeilaGharani 3 жыл бұрын

Awesome, thank you!

@Val-M. 3 жыл бұрын

Leila - just when I think I am getting pretty good at Excel . . . BOOM ! You show me/us another thing that is very handy to know for us pretend Excel 'masters'. That was a good one. Thank you.

@asccatweazle5343 3 жыл бұрын

Thank you, very instructive 🙂

@IlaPatel811 3 жыл бұрын

Always useful tips, thanks Leila!

@LeilaGharani 3 жыл бұрын

My pleasure!

@paolosoloperto4948 2 жыл бұрын

Leila is always a pleasure to see your videos, they learn many new things.

@LeilaGharani 2 жыл бұрын

Happy to hear that, Paolo!

@allforedandtech 2 жыл бұрын

Great Practical Example

@madhavjoshi9040 3 жыл бұрын

Wow! Very lucid explanation.

@luisfernandocuestasanchez4343 3 жыл бұрын

I'm in love with you 😍 Thank you for all the things you share with us😊

@LeilaGharani 3 жыл бұрын

My pleasure 😊

@ashishtrivedi8253 3 жыл бұрын

Perfectly understood and very well explained! Thx for the video.

@LeilaGharani 3 жыл бұрын

You're very welcome!

@nicolaspettini3143 2 жыл бұрын

Great tips! Thanks

@brighnquisitive6217 3 жыл бұрын

Continuing to produce excellent. Thank you, Leila

@video_banani_nahi_aati 3 жыл бұрын

You really gives useful and new things. Love u mam.

@mohammadashiq9996 3 жыл бұрын

I LIKE THIS VEDIO HELPED ME WHAT I HAD MISSED. THANK YOU

@rietberg 3 жыл бұрын

Awesome, thank you for doing these videos. Web data scraping is an area I am struggling with...

@tsvs58 3 жыл бұрын

Excellent tips

@martinelduin2214 3 жыл бұрын

Very helpful. You're videos are always well done in content and instructional method.

@LeilaGharani 3 жыл бұрын

Glad you think so, Marty!

@shuttleking 3 жыл бұрын

today morning, I was thinking about when to use #sign in excel and KZfaq auto-suggested me this video of yours… 😊 thanks Leila

@rbfreitas 2 жыл бұрын

Another very useful video! Thanks for sharing!

@MarkFuchs 2 жыл бұрын

Thank you! You do a great job of explaining complex, at least to me, formulas.

@LeilaGharani 2 жыл бұрын

Great to hear, Mark!

@anthonyjoe6275 2 жыл бұрын

The way you explain which is quite interesting 👌

@storytime5108 3 жыл бұрын

Awesome job Leila, Thanks.

@ahmetx7456 2 жыл бұрын

You are the best teacher. 🙌🏻🙌🏻🙌🏻🙌🏻🙌🏻🙌🏻

@AnilSingh-mj6lj 2 жыл бұрын

Very Informative Video!

@dzl999 3 жыл бұрын

Excellent video, clear and informative. Thank you.

@LeilaGharani 3 жыл бұрын

Glad you enjoyed it!

@jacquescayrol3843 3 жыл бұрын

thanks a lot for all your excellents videos !

@setha9818 3 жыл бұрын

I thought I'm an Excel expert, but this is all new stuff - lambda, spills, etc. Thanks for helping me keep updated. Great video.

@LeilaGharani 3 жыл бұрын

Thanks for watching!

@mahman7464 2 жыл бұрын

Lol.. I never knew hash in excel, though excel is my primary platform and I see it million times at my work. Now I know!!! Loads of thanks to you...👍👍🙂

@algawzy 3 жыл бұрын

Thanks for your valuable time 🌷

@karenginokeeler 3 жыл бұрын

Thank you, Leila❣

@ExcelWithChris 3 жыл бұрын

Loved it. Thanx for another great one.

@theverticalmove7784 2 жыл бұрын

Very informative and useful information ... Thank You

@Bassemyt Жыл бұрын

Really You Made it Simple and Easy

@unosec 2 жыл бұрын

thanks for valuable tutorial

@geoffphil 3 жыл бұрын

Thanks - excellent teaching.

@umesh2403 3 жыл бұрын

Gr8 work there. All the best

@realpulsecoin 3 жыл бұрын

Great content Leila!

@fernandoperlas9575 3 жыл бұрын

Great teacher.

@HenricAndersen 3 жыл бұрын

Another great video👍👍👍 Thank you Leila 🙏

@LeilaGharani 3 жыл бұрын

You’re most welcome, Henric 😊

@praveenk1672 3 жыл бұрын

I started using # in the dynamic data validations based on your suggestions in your previous video. It is really a cool function

@LeilaGharani 3 жыл бұрын

Great to hear!

@rajeshghai1494 3 жыл бұрын

You r a great teacher 🙏🙏🙏🙏

@faran2229 3 жыл бұрын

Love your videos Leila joon 💕

@varchar3587 2 жыл бұрын

Thanks a lot for the video. As you presented Unique and # with the same results, finally I an not sure to understand in which situation I should need # (instead of any function)

@ruhannegi8376 2 жыл бұрын

Before watching videos I use to think I am good in Excel.. now I feel I have not even started . Thanks a lot for such new and amazing videos.. lots of love from India ...