I don't use OFFSET Anymore! I Use Another Function Instead.

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.

📚RELATED LESSONS

❓What do you prefer to use for your dynamic named ranges? Please let me know in the COMMENTS.

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

📢 Please leave me a COMMENT. I read them all!


🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

⏲ TIMESTAMPS
0:00 Excel Dynamic Named Ranges
1:13 Dynamic Named Ranges with INDEX
3:32 Defining Names
4:50 Dynamic Named Range for a Row with INDEX
7:35 Dynamic Named Range for a Column with INDEX
10:31 Dynamic Named Ranges with OFFSET
11:59 Dynamic Named Range for a Row with OFFSET
13:21 Dynamic Named Range for a Column with OFFSET
14:58 Dynamic Named Ranges for Charts
17:53 Common Causes of Errors
19:15 Relative Dynamic Named Ranges
19:43 Alternatives to Dynamic Named Ranges

[waitplanwp]

Many thanks for all the years of help and support, you are a special soul who always loves to help, may God bless you and your family for all the good you bring to the analyst community.

Lots of love from all your fans from Israel!

[MLFranklin]

That first example was mind-blowing. I'm going to have to watch that a few more times.

[aicx]

Many thanks. As a self-taught user of excel in consolidation & analysis of data, your sharing of excel use is of great help.

[Donkeys_Dad_Adam]

I've used INDEX and MATCH for years now, but how it all worked was always a bit mysterious to me (back when I started). This is a fantastic explanation of how the syntax works! Bravo

[michaelclifton]

Thanks Mynda. I work in Business Analytics and you've saved me more than once on specific technical issues.

[michaeldavenport]

Outstanding! Many thanks to you for the great instruction.

[VVeiQuek]

Thanks for sharing. So much content to pack in just one video. I am looping the third time now.

OFFSET as a method of range in VBA is way more intuitive than OFFSET as a formula. Like, it really puts one's spatial visualization to the test like LAMBDA recursive formula. (Excel was supposed to do the math for us, but we have to do the math first for Excel to process.)

[p.k]

This is the answer I didn't even know I was looking for! Thank you so much! 😊

[alexrosen]

Superuseful and very educational tutorial. Thanks a lot 🙏

[sunnybaggu]

Thank you for sharing the use of index function in a smarter way.
Along with the your technical information and skills, i really admire your excellent presentation skills. TAKE and DROP function can also be helpful in these cases to select ranges.
Best wishes

[steven.h]

I was thinking Tables the whole way through this production, then it hit me 19:43 👍😎✊

[GeertDelmulle]

19:45 Yes, finally, that how I would approach the challenge — using tables.
Much easier, albeit not trivial either: you might still need named ranges or even the INDIRECT function if you want to use table formulas for data validation. That’s exactly what I used for creating a risk register template with auto charts, not so long ago.
The resulting ease of use for the end users is great when it’s fully automatic.
Thanks for the video. :-)

[CarlosEliezerHernandezPintor]

I knew this can be done with SEQUENCE but with the colon symbol at the beginning it’s easier. Thanks Mynda !

[peterbriggs]

Great presentation...even watching late at night 😎

[tahirshah]

It is not for one to know a function, but to incorporate a function with other functions like tools. Mynda you are very intelligent.

[tomoo]

This is very clever and well described, I would typically use tables much more in my ways of working as I find it much simpler and it opens up all the range options tables contain by default as shown in your linked video and at the end of this one. I will now choose not to use offset though :)

[SpotlightImpact]

well drat! I just finally learned to use OFFSET for a dynamic 12-month chart... now I'll have to try this!

[daXcel]

CHOOSECOLUMS +CHOOSEROWS would also do the trick...Great video as always

[Quidisi]

Amazing content in this video!
So many of my difficulties would be solved if Excel would allow me to use spilled arrays inside of Excel Tables.
Anyway, thanks again. I've already watched this twice.

[IamTheReaper]

Detailed 🤯














.



















.














😎