Make Excel Formulas Dynamic with the Hash Sign

Learn Dynamic Arrays with the Hash Operator in Excel.

In Excel, the hash (#) sign is used to reference dynamic arrays, which automatically adjust as new data is added or removed. Dynamic arrays allow formulas to return multiple values, making them more flexible and efficient. Functions like UNIQUE, FILTER, and SEQUENCE return dynamic arrays, which can expand or contract based on the dataset. By using the hash operator after a cell reference (e.g., A1#), you can refer to the entire array generated by a dynamic formula. This makes tasks like creating drop-down lists or calculating results more seamless, as you don't need to manually adjust the range when new data is added—the array adjusts itself.

LEARN:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ Dynamic Arrays
1:47​ - Hash (#) Operator
4:20​ - Real-World Example
8:03​ - Dynamic Drop-Down List
9:25​ - Tables vs Dynamic Arrays

[ahmadzamil]

Thank you for this! Love it! Will be very useful for me as Excel is my bread and butter. For the dynamic list I've been using OFFSET(Cell1, 0, 0, COUNTA(Column)) until today. Now I can just use the Hash.

[markpodesta]

Thank you Kenji. I have been converting formulas to use the # tag.

[catatonicbug]

I can use this! Thank you! It's helpful when you want to apply a formula to an entire list that will grow over time!

[angelviloria]

I’m an expert in tech ed (by education + decades of experience) and your short dense video hit so well on so many dimensions.
This is the first time I’ve seen one of your videos and I immediately subscribed.
Excellent!

[ChocaZed]

love them "Formula" names... seems fitting 😀 Go go McLaren!
Great vid, btw! Keep up the good work 👍

[Chris-chei]

"Thanks for your videos, Kenji! They've been a huge help to me, and they continue to make a difference. Every time I watch one of your videos, I can see how much better I'm getting. I really appreciate your help!

[richlawrence]

Excellent presentation style. Very clear and concise. Thanks.

[abdelhafidassadiki]

You give us valuable and exclusive advice that makes us eager for more. We wish you success and brilliance

[pagedinesh]

Great Sir, Thank you very much... very helpful

[otmanaboutaieb]

OMG that soooo helpful, i use a bunch of code in vba to do what a simple little symbol can do. thank you sooo much 🙏🙏🙏

[bjornclaeys]

Hi Kenji. this was indeed a very useful solution for my files

[pascalejacquelinepetit]

Always informative and exceptionally well presented! Thank you Kenji for great videos. The pace is perfect to follow along, and the reasoning for why things work/don't work, helps us understand how Excel works.

[RikMaxSpeed]

Useful tips and interesting F1 driver names 😊

love it! this is a game changer! kudos Kenji!

[nsanch]

Thank you for the great video Kengi. There was a lot of useful #, table and function information in that.

[bejoscha]

Thanks, that was really well presented and will be very handy for me in the future.

[boototom]

If you name your tables and use pivot tables, the result would be the same but it's interesting to know that exists.

[amahlaka]

Thank you, finally i can ditch the nasty workaround for dynamic dropdowns i had to use

[TheGbelcher]

I’ve been looking for this my whole life. Thanks!

[nikilragav]

In Google sheets, I always do like G3:G. Good to know that I can do the equivalent in excel