When Should You Use the Hash Sign in Excel Formulas?

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.

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.

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

🚩Let’s connect on social:

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

[mrk]

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

[rohitshukla]

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🙏❤

[fayi]

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 👍👍👍

[kenhaley]

Great video as usual. I always learn something from your videos. Thanks!

One thing I just discovered: You can now enter a range, with out a surrounding function--that is, all by itself, (like =A4:B10) and it will act like all the other spillable functions, copying the range. Nothing earth-shaking about that, but you can also do things like: (A4:A10 + B4:B10), and Excel will do the operation for every cell in the range(s) and put the results into a new spilled range.

[elactecnology]

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

[lowkey_]

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

[birgitjohannes]

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)

[siddharthranawat]

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 😄😄😇😊

[setha]

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

[wtfisthishandlebs]

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

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

[rogerangel]

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

[jamesduncanlinch]

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!

[lindsayhemy]

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

[mahman]

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...👍👍🙂

[rudiklein]

This video solved a problem for me which I couldn't figure out myself after spending hours fighting Excel. Thank you!

[bijuraveendranathan]

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

[komiku]

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

[boredomgotmehere]

The best excel masterclass on youtube. Thank you Leila.

[theverticalmove]

Very informative and useful information ... Thank You