I don't use OFFSET Anymore! I Use Another Function Instead.
ฝัง
- เผยแพร่เมื่อ 31 พ.ค. 2024
- Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
⬇️DOWNLOAD the example file here: www.myonlinetraininghub.com/e...
📚RELATED LESSONS
- INDEX & MATCH in 4 Minutes: www.myonlinetraininghub.com/e...
- Relative Named Ranges: www.myonlinetraininghub.com/r...
- Excel Tables Alternative: www.myonlinetraininghub.com/e...
❓What do you prefer to use for your dynamic named ranges? Please let me know in the COMMENTS.
🎓 LEARN MORE in my Excel courses: www.myonlinetraininghub.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
📢 Please leave me a COMMENT. I read them all!
🎯 CONNECT with me on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ 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
![เกิดใหม่ทั้งทีก็เป็นสไลม์ไปซะแล้ว ซีซั่น 3 - ตอนที่ 57 [ซับไทย]](http://i.ytimg.com/vi/pNhft3YZFRE/mqdefault.jpg)
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
Thanks so much 😊 it’s always helpful to really understand the intricacies because then you can start to extend the capabilities 👍
That first example was mind-blowing. I'm going to have to watch that a few more times.
Glad you liked it!
Many thanks. As a self-taught user of excel in consolidation & analysis of data, your sharing of excel use is of great help.
Glad to hear that 🙏😊
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!
Thanks so much!
Excellent tutorial Mynda. Thank you!!
Cheers, Ivan!
Outstanding! Many thanks to you for the great instruction.
Thanks so much!
I was thinking Tables the whole way through this production, then it hit me 19:43 👍😎✊
I wonder if the whole named range stuff should be removed from Excel by now. It seems to offer no benefits over tables and it only confuses newbies.
Actually, up until now I supposed it had some specific usecase that I didn't know about because there's still so much content about it.
It does have special use cases. For example, let's say you summarise your data in a PivotTable and then want to plot it in a scatter chart. It's not possible to create a Pivot Scatter Chart, so you have to trick Excel into using the PivotTable as the source data for your scatter chart and you'll want that chart to update if the data grows/shrinks, in comes the dynamic named range. I use dynamic named ranges all the time to create regular charts from PivotTables e.g. map charts, treemaps, etc.: th-cam.com/video/5vOqZBmBRos/w-d-xo.html
@@MyOnlineTrainingHub Thanks, good to know these usecase 👍
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
Thanks you 🙏 yes, TAKE and DROP are great too.
Superuseful and very educational tutorial. Thanks a lot 🙏
Glad you liked it 🙏
This is the answer I didn't even know I was looking for! Thank you so much! 😊
😁glad you found it helpful!
Thanks Mynda. I work in Business Analytics and you've saved me more than once on specific technical issues.
Awesome to hear 🙏
This accent is so soothing
Aw, thank you 😊
It is not for one to know a function, but to incorporate a function with other functions like tools. Mynda you are very intelligent.
Thanks so much 🙏😊
Useful information as always.
Glad you think so!
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.)
😁 I agree! Although, I do think OFFSET is easier than INDEX for dynamic named ranges, but once you get the hang of them, it's relatively easy.
Thank you for this video!
You are so welcome!
Very useful, thanks for sharing
My pleasure 😊
I knew this can be done with SEQUENCE but with the colon symbol at the beginning it’s easier. Thanks Mynda !
Glad you'll find it useful, Carlos!
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. :-)
I use Tables where possible too, but for interactive tables, charts etc., I use INDEX and if it's not going to be a big file with lots of dynamic named ranges, then I might use OFFSET.
@@MyOnlineTrainingHub OK, for dynamic charts, I use… dynamic ranges: works like a charm and you can hide the DAs below the charts.
Really nice. And everything updates in realtime.
Great presentation...even watching late at night 😎
Thanks for watching!
CHOOSECOLUMS +CHOOSEROWS would also do the trick...Great video as always
Thanks!
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 :)
Yes, Tables are the easiest for simply referencing a dataset. Glad to hear you already use them.
I also Tables as much as possible. However, I sometimes I have data sets which are populated from another source (such as Oracle Smart View), where the rows and columns change. This is a an excellent solution instead of just using a range that is larger than the largest 'expected' range size.
OFFSET works very well for me. 😊
Me too...in small doses😉
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.
Thanks so much!
Thank you for vid.
My pleasure 😊
Thanks
Welcome 😊
Thank you.
Pleasure 😊
What tool are you using for the orange squares you draw to show the area of focus?
Ever thankful for your videos.
I use Camtasia Studio for my videos and animations.
So cleversome!!!😊
😊thanks so much!
Detailed 🤯
.
.
😎
worth reading your comment! 😁🙏
tnx, brilliant
Thanks for watching 😊🙏
Hi, great videos and channel! Just found you. I have a question. I have a sheet of weekly schedule that is several weeks long on one sheet. Wanted to know if you can freeze each week's date by scrolling up in Excel? I tried but it does only the top row (first week) but then when next week comes up it just keeps scrolling up away. Thanks.
Thank you! It's difficult to visualise, but you might find Group and Outline useful for fixing the dates: www.myonlinetraininghub.com/excel-group-and-outline-data
Nice video. What happens if you want to use in the named range box a formula like sort(unique()) that spills? You can only use it by referring to the cell where the formula lives and then adding the # in the named range box. Is there any way to enter the sort(unique()) formula directly in the named range box? Thx
You can define a name using SORT(UNIQUE( etc. If you're stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Interesting. I prefer using FILTER() for dinamic ranges or TAKE() for execute calculations with functions MONTH(TODAY()) in case i have to start from a previous period till today
Absolutely, if you have Microsoft 365. Those functions are the best!
This is brilliant. I had to use this DA formula on a table populated only by formulas which made counta pretty much obsolete.
Instead I used countif with range and “ >”” “ as the criteria. Works a treat.
One question I do have: will the dynamic range grow as the table grows (it’s based on the parameters of the current size of the table) or is the dynamic array essentially fixed to whichever cells that were stopped at?
Thank you.
Awesome to hear! Anything referencing the DA with the # operator will grow as the DA grows.
You can now use the TAKE() function.
Example for a one dimensional range: if data potentially go from B2 until B22 then you can refer to the used part of that range by using TAKE(B2:B22,counta(B2:B22)).
Example for a two dimensional range: if data potentially go from B2 until E22 then you can refer to the used part of that range by using TAKE(B2:E22,counta(B2:B22), counta(B2:E2)).
Thanks for sharing!
excellent
Thanks!
Did you consider using INDEX with implicit intersection (the @ operator). It would simplify example two.
Great idea for cell based results. For this tutorial I really wanted to focus on the different ways you could use INDEX and OFFSET to return ranges using COUNTA or MATCH because implicit intersection doesn't work for dynamic named ranges used in charts.
Great video Mynda. I was never a fan of OFFSET; I always found it a bit clunky and confusing
Glad we agree!
Any advice on how to create a dynamic table that each month returns my no of products sold and total revenue? I i guess a pivot table would be a good solution. I just don't know how to make it isolate specific months, weeks etc from the database.
I'd use Power Query to get the data from the database you want to report on and then a PivotTable to summarise it. Here's an introduction to Power Query th-cam.com/video/L4BuUzccLpo/w-d-xo.html
different challenge do you know how to use this "¦" on a mobile keyboard. It is onthe button in the corner of keyboard next to the 1 and below esc on a normal uk laptop/pc keyboard.
I don't have that symbol on my keyboard. The closest I have is the pipe symbol "|" which is a single vertical line, unlike yours which is two vertical lines. Under my Esc key I have the back tick and tilde symbols ` & ~ However, on my iPhone I go into the second symbols screen to see the pipe and tilde.
Another simple way for devising dynamic ranges: name them via the conventional Formulas>Create from Selection route. Then reconstitute the dataset as a table. The existing range names turn dynamc, without the need to resort to structured references.
Nice tip!
@@MyOnlineTrainingHub Thanks!
The little trick to replace the Offset with Index is really a huge time saver for me.
So pleased it was helpful!
@@MyOnlineTrainingHub Yes. I've noticed that using Offset and especially Indirect severely hamper my sheet calculation. After some research, I understood that since both of them are volatile function, they slow the sheet down. So glad I stumbled upon this while searching for an alternative to Offset
HI,I WANT TO SHOW MULTIPLE MATCH RESULT BY USING INDEX MATCH METHOD IS IT POSSIBLE ?
Probably. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello Mam,
I hope you're having a bright and shining Day just like your smile.
Mam, Please Make a Video that How to calculate Qtd, Mtd, Ytd, Ly6m, L3m, Fiscal Year Sales in excel, Using Sum & Offset dynamic Function.
We are very desperate for this video ❤
Please try make this video in this weekend if possible.🫂
I have a job interview scheduled for 23rd August.
1) How to calculate KPI of l6m Sales.
2) Product Name | Ly3m Sales in pivot table.
You are the only golden hope of excel lovers like me.
Me and my indian friends are looking forward for this video.
You are doing a good job.
Thank you mam!
Thank you for the suggestion. Unfortunately, I wont' have time to do a video for you this weekend, however here is a written tutorial that does it: www.myonlinetraininghub.com/excel-formulas-to-summarise-monthly-data-into-quarters Best of luck with your interview.
Is index function not a volatile. I mean can I use it instead of offset to make a dynamic dropdown list and don't re calculate the sheet every time
Correct. INDEX is not volatile.
Sorry for the ignorance, but why when we highlight only the index part it returns the value on specific cell, but when we use it as part of a range, and then highlight the range, it acts like an INDIRECT() who treats it like a reference, completing the interval? Didn't ever heard of that! 😯
As answered on LinkedIn: when INDEX returns a value, it's actually returning a reference to that cell, which it's then able to evaluate and display the value. It's an illusion of sorts.
Here are 5 things most users don't know INDEX can do: www.myonlinetraininghub.com/5-excel-index-function-secrets
Mam please reply to me why the offset formula does not apply in my window 11 laptop for a fixed reference F4 key doesn't work Why does it happen please tell me
Sounds like you need to press the Fn key to enable function keys on your laptop keyboard.
@@MyOnlineTrainingHub thks a lot mam
Isn’t it only dynamic but to the address of your absolute references? What if it grows beyond the 16 or 17 rows?
If you expect your range to be larger than that used in my example, then allow for that and select a bigger range. I just used a small range so it was visible on screen while recording. You would adapt the concept to your own needs.
@@MyOnlineTrainingHub I see. For a dynamic range that utilizes 'any' possible shape the COUNTA() would be the entire row and/or column I guess?..or I suppose a table definition would be better to capture any changes to column layouts and not having to worry about the number of rows.
Was this all about avoiding tables? I’m an amateur, so all this ‘allow for growth’ makes me lose focus to the point, that I’m still not quite sure what I got from the video.
No, not at all. I mention tables at the end of the video as an alternative. Allowing for growth is referring to more rows and columns being added to your data. These functions are ideal for when you’re referencing data not in a table e.g. it might be in a report format or a PivotTable.
@@MyOnlineTrainingHubI get what you’re saying … now. For most of the video, though, I was confused.
offset is now annoying... imagine i use =offset then use match.. icant match the first row on the offset i dunno if its a bugg or what
Not a bug. I suspect OFFSET it not returning the range you think it should be. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
In this example, you would be better off converting the data to a Table (VBA Listobject) and referencing that (by its name) as the array in your index() function, that way it is already a dynamic range. Rather than selecting cells outside the range which could end up with unconnected data ie B16 (as per your formula).
Yes, VBA is an option, but keep in mind that VBA comes with a overhead in that it requires enabling and is not executable online. Plus, VBA functions are typically much slower than built in functions.
@@MyOnlineTrainingHub Sorry, I may have confused things by adding supplementary information that VBA object name of a Table as a list object - forget VBA. What I meant was by converting the data to a table first, with [Insert>Table], or the short cut [Ctrl+T] (after selecting any cell in the data range) you will convert the range to an Excel Table, (usually initially seen with the blue stripes, but this can be removed or changed). The table itself is a dynamic range which grows/shrinks automatically with any change to the data, that way you can use a formula such as "=INDEX(Table1,MATCH($B$51,Table1[Category],0),MATCH(TEXT($C$50,0),Table1[#Headers],0))" where the table name defaulted in my case to "Table1". No VBA required. NB. I had to convert the year from a number to text here, using ("TEXT($C$50,0)") because the Table1[#Headers] expects returns strings, so I converted the lookup value for the header range to a string datatype... Edit: I see you did point viewers to your Tables functionality video at the end of this video. I would just say in general excel tables are so useful, and enforce consistency (column functions, dynamic named ranges etc) that if users are working with anything that looks like tabular data they should convert the data range to an Excel table and then work with table functionality.
I put this here... i viewed this first@16sc of upload
Thanks for watching.
💙
🙏
Offset- a function I despise! - I inherited spreadsheets littered with offset- so hard to debug, sooo slow. I rarely use it. Index has come back into its own with dynamic arrays.
I can imagine that involves a lot of opening the name manager to see the range being returned 🥱 yes, INDEX is even more amazing now it can spill. 🤩
This is immense 🥵...
But worth it 😉
I will stay with OFFSET. This is overcomplicated to solve the basic things.
Fair enough. At least you're now aware of the alternatives...you never know when it might be useful.
bla bla bla