Excel Relative Named Ranges - A Secret of Excel Pro Users

Подписаться 615 тыс.

Просмотров 94 тыс.

50% 1

Relative named ranges are a super useful Excel tool, but they can catch out the unsuspecting user. In this video I uncover the reason the name manager sometimes returns kooky results and how to leverage this feature to create dynamic named ranges.
🔻 DOWNLOAD EXCEL FILE HERE: www.myonlinetraininghub.com/r...
🎓 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 Mynda 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 Introduction
0:31 What is a Relative Range
1:30 What is a Relative Named Range
2:47 Scope of Relative Named Ranges
3:19 Dynamic Sheet References
4:23 Key to Creating Relative Named Ranges
6:04 Relative Dynamic Named Ranges
11:21 File Download

Наука

Опубликовано:

27 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 153

@mackenmd 4 года назад

I feel as if I have won the mini-lottery in discovering your channel. This video in particular is super relevant to what I’m working on at a time when time is of the essence. Thank you so much!

@MyOnlineTrainingHub 4 года назад

Glad it was helpful, Mark :-)

@leerv. День назад

"Now in order to use my arrow keys inside this field we have to press F2 to go into Edit mode" --- OMG....I've been using Excel for over 10 years professionally and I always HATED how you couldn't use arrow keys in conditional formatting formulas and the like..... I can't believe the wall was only as high as a single key press this entire time. *forehead smack -- THANK YOU!!!! So many awesome tips in this video! I can't wait to apply them at work and show my colleagues

@MyOnlineTrainingHub День назад

So pleased to hear I could solve that decade long mystery for you 😁

@jamesholtman4026 Год назад

Finally, with your assistance, I get the picture! Many thanks to you Mynda.

@MyOnlineTrainingHub Год назад

Glad it helped!

@brighttriangle 23 дня назад

This was new to us. Thank you for sharing!

@MyOnlineTrainingHub 22 дня назад

Thanks for watching!

@milicapejovic7174 4 года назад

Thank you Mynda! As always, great tutorial! :)

@MyOnlineTrainingHub 4 года назад

Cheers, Milica 😊

@darrylmorgan 4 года назад

Excellent Tutorial,Really Helpful.Thank You Mynda :):):)

@MyOnlineTrainingHub 4 года назад

Thanks, Darryl 😊

@stenionet 2 года назад

That's Gold. It will save me precious time. I wish I had known this 15 years ago. Thank you very much.

@MyOnlineTrainingHub 2 года назад

Great to hear! Better late than never 😊

@paramveerssachdeva 4 года назад

Excellent, thank you so much for sharing this!

@MyOnlineTrainingHub 4 года назад

My pleasure!

@gyozakeynsianism 4 года назад

This was great. I've been using name ranges but not in such an efficient or sophisticated way. This helps! Thank you.

@MyOnlineTrainingHub 4 года назад

Great to know you can make use of relative named ranges :-)

@Victor-ol1lo 4 года назад

Well explained. Thanks Mynda !

@MyOnlineTrainingHub 4 года назад

Cheers, Victor. Glad you liked it :-)

@dennisd5776 4 года назад

Very useful! Thanks for the tip!

@MyOnlineTrainingHub 4 года назад

Glad you'll be able to make use of it :-)

@anv.4614 9 месяцев назад

Dear Mynda, Thank you very much for your lesson. appreciated.

@MyOnlineTrainingHub 9 месяцев назад

You're very welcome!

@JoseAntonioMorato 4 года назад

I am Brazilian and, to understand your videos, I put the subtitle in «English (Automatically Generated)» and, on top of it, «Translate Automatically - Portuguese». Many years ago I wanted to make named formulas with relative intervals that could be inserted in any range of any spreadsheet, but I couldn't, because I was unaware of the need to maintain the "!" before the relative interval. Now that I have learned this, I have already changed several worksheets, eliminating a series of named formulas, each with a different name, because the range was stuck to the created worksheet. Really enjoyed! Thanks! Note: I hope you understood me, as my text was translated by Google Translate.

@MyOnlineTrainingHub 4 года назад

So glad I could solve a mystery for you, Jose :-)

@JoseAntonioMorato 4 года назад

@@MyOnlineTrainingHub And it really resolved! Thank you! 👍🤗😘

@dcasuto 3 года назад

WOW! I have been wanting to learn this forever!!!

@MyOnlineTrainingHub 3 года назад

Awesome! Glad I could help :-)

@conduentservices334 2 года назад

i have no words to appreciate your contribution to solving our issues in just 1 small and crisp tutorials. Thanks a lot from India.

@MyOnlineTrainingHub 2 года назад

Thanks so much! 😊

@DougHExcel 4 года назад

Thanks for the video!

@MyOnlineTrainingHub 4 года назад

My pleasure, Doug :-)

@iankr 2 года назад

Brilliant! Thanks, Mynda. I didn't know you could create relative named ranges! I use named ranges a *lot* in my work where I need to set up a large number of formulae (sometimes in conjunction with INDIRECT, if used sparingly) and to make it easier to audit formulae. I can see how (sometimes) making them relative would be very useful.

@MyOnlineTrainingHub 2 года назад

Glad it was helpful, Ian!

@tonymukelabai1283 8 месяцев назад

Great, straight to the point. Video still valid!

@MyOnlineTrainingHub 8 месяцев назад

Awesome to hear 🙏

@Nevir202 Год назад

Liked your tip on dynamically sizing, but the fact it breaks down if there are ever any blanks in the range got me to thinking. MAX(FILTER(ROW(A:A),A:A"")) This would always find the last row with data in it for dynamic column ranges, for use with INDIRECT. For row ranges, you'd need to convert the number to a letter and do a little extra to dynamically add the correct row number at the end, but overall it would still work.

@MyOnlineTrainingHub Год назад

Thanks for sharing!

@Zvertnie 4 года назад

Amazing. Thank you!

@MyOnlineTrainingHub 4 года назад

Thanks for watching 😊

@captvo 3 года назад

Thank you for that neat trick Mynda! Was not aware about that at all.

@MyOnlineTrainingHub 3 года назад

You are most welcome :-)

@kerronduncan6697 3 года назад

Truly a pro tip. Thanks for sharing!

@MyOnlineTrainingHub 3 года назад

Glad it was helpful!

@w13ken 2 года назад

Great advice Mynda - thank you. Love the reference to Marching Ants 😀

@MyOnlineTrainingHub 2 года назад

You are so welcome!

@jimfitch 4 года назад

Great tip & lesson, as always, Mynda. Never tried this consciously before, but noticed dynamism in named ranges from time to time. Never understood why or how to control it. Now I do. It will be very useful. Thank you!

@MyOnlineTrainingHub 4 года назад

Glad you can make use of it, Jim :-)

@bravucod 4 года назад

Great tutorial!

@MyOnlineTrainingHub 4 года назад

Thanks, Daniel 😊

@kingkock1 2 года назад

Amazing, thank you

@MyOnlineTrainingHub 2 года назад

Glad you liked it!

@ljudevitgaj4855 4 года назад

Thanks

@connie6616 9 месяцев назад

I finally found the solution to solve my sparkline relative range. Thanks so much!

@MyOnlineTrainingHub 9 месяцев назад

Excellent! So pleased I could help 😊

@findthetruth3021 4 года назад

Your videos are amazing

@MyOnlineTrainingHub 4 года назад

Thanks so much!

@JonathanExcels 4 года назад

Very helpful.

@MyOnlineTrainingHub 4 года назад

Thanks for watching, Jonathan :-)

@EduardKorkia 4 года назад

Thank you!!!

@MyOnlineTrainingHub 4 года назад

My pleasure, Eduard :-)

@MirFayaz 4 года назад

wow you are the best trainer of Excel

@MyOnlineTrainingHub 4 года назад

Thank you! That's very kind of you :-)

@joshuamanampiu6489 4 года назад

Really really good!

@MyOnlineTrainingHub 4 года назад

Glad you think so, Joshua!

@leonardoggy 2 года назад

8:08 - Just for this alone you have my eternal gratitude. (Hitting F2 to be able to use arrow keys there.)

@MyOnlineTrainingHub 2 года назад

Glad you liked it, Leonardo! 😊

@prakashsrinivasan7840 2 года назад

I already aware of relative names but u made sparklines which is amazing 👏

@MyOnlineTrainingHub 2 года назад

Glad you liked it!

@pinakikkkm 2 года назад

Thanks!!!!

@MyOnlineTrainingHub 2 года назад

Welcome 😊

@rodneyplunkett6688 4 года назад

Mynda, great tip. I use something similar using the OFFSET & COUNTA functions to create a dynamic named range to use in a pivot table. When new columns and/or rows are added, the pivot table source is automatically updated, not unlike your sparkline dynamic ranges.

@MyOnlineTrainingHub 4 года назад

Cheers, Rodney. Yes, OFFSET is another great tool for dynamic ranges, the downside of OFFSET is that it's a volatile function, which can slow down workbooks. It's fine to use if there's no loss in performance though.

@rodneyplunkett6688 4 года назад

Da. I discovered that as my input data crashed through 500,000 rows and 40 columns. Learning how to use PowerPivot and PowerBI to overcome the performance issues.

@companyconcord 3 года назад

you are very wonderful

@MyOnlineTrainingHub 3 года назад

Thank you so much 😀

@msoffice6037 4 года назад

Thanks for sharing! I feel like I missed a lot not knowing your youtube channel :(

@MyOnlineTrainingHub 4 года назад

There’s always time to catch up 😉

@GeertDelmulle 2 года назад

Great video, as always! :-) Revisiting this video, I want to add that there’s one thing I would like to be able to do, and that is: to be able to change the scope of the defined name after its creation. Excel doesn’t let us - the only way is to delete it and define it anew.

@MyOnlineTrainingHub 2 года назад

Thanks, Geert! Yes, being able to change the scope after creation would be nice.

@stefanogattoCH 4 года назад

C3:INDEX(... never saw such a syntax ever! Thank you!!!

@MyOnlineTrainingHub 4 года назад

Great to know you discovered something new. You can also do INDEX(...:INDEX(... :-)

@stefanogattoCH 4 года назад

@@MyOnlineTrainingHub Gasp!

@EPMeokazi 4 года назад

Great video! For some reason I thought that switching the data range to a table would automatically update the sparklines but it doesn't! Thanks for the tip!

@MyOnlineTrainingHub 4 года назад

Glad you liked it 😊

@naurozmomin6631 3 года назад

Thank you Mynda for your very helpful and precise videos. I wanted to know, if there is some technique to assign a dynamic Name based on column header for data found in that particular column?

@MyOnlineTrainingHub 3 года назад

Not sure what you mean. Perhaps you can post your question on our Excel forum with an example file and we can help you further: www.myonlinetraininghub.com/excel-forum

@cornelbejenaru3523 2 года назад

inspiring

@MyOnlineTrainingHub 2 года назад

Great to hear 😊

@sandeepkothari5000 4 года назад

Wow!

@MyOnlineTrainingHub 4 года назад

Hope you can make use of relative named ranges :-)

@B1897forzajuve 2 года назад

Hi Mynda. Can you please explain how we can use Xlookup instead of Index in the last example? Thank you very much. This channel is an absolute gem.

@MyOnlineTrainingHub 2 года назад

Thank you 😊 you can learn how to use XLOOKUP to return a range here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2ViMm-wuM3U.html

@Shady_Lane 4 года назад

Great video, I never knew you could use named ranges in this way! Just one question, would the extensive use of Relative Named Ranges in a 15MB size file slow down the calculation to any noticeable degree?

@MyOnlineTrainingHub 4 года назад

Names themselves don’t make Excel slower. In fact in many cases when used correctly, names can speed up workbooks. However, if your names contain volatile functions or inefficient formulas, then that can result performance issues, just as these formulas can when used directly in worksheet cells. Mynda

@barttitulaerexcelbart9400 4 года назад

Thank you Mynda, great video. I never new this. I love working with names, use it a lot. I always use "create from selection" but the problem is that you can not change the scope of the name. If you have a lot of sheets and for every sheet you have something like "turnover" this is a hurdle. Any idea how you can use "create from selection and change the scope? Btw: your sparkline solution was sophisticated and relevant for this video, but I would use the insert table feature, works easy!

@MyOnlineTrainingHub 4 года назад

Hi Bart, indeed, not being able to change the scope after creating the name is a hurdle and I'm not aware of a workaround. The Sparklines example was designed for when you can't use a Table e.g. if the source is a PivotTable, which is often the case, although my example didn't use a PivotTable for the sake of simplicity.

@vinamrachandra9611 4 года назад

As most things, this can be solved using VBA. You may create all your names the way you do then just run a procedure to change absolute ranges to relative ranges. You may add a suffix to the names to identify which ones you want to replace. For example a suffix like _rel

@sajimon2010 3 года назад

Thank you for sharing your knowledge. It's a great opportunity and really glad to follow your videos. I have a request. Please give me better way to find out the solution without using VBA or Macro. I just want to prepare an order sheet based on the ingredients given for each menu by selecting or putting total quantity of portioning of multiple menu selected. I just need a summarize report of ingredients based on the selection and total portioning quantity. If you can find a solution for this, please share it with us as soon as possible. Awaiting for your reply. Thank you.

@MyOnlineTrainingHub 3 года назад

Glad you liked it. Please post your Excel question and sample file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@Trucpq 3 месяца назад

Can you make a training of using Function in Named range such as OFFSET, FILES ...

@MyOnlineTrainingHub 3 месяца назад

Like this: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cMLbx7w_0Q8.html

@dagwould 4 года назад

In the dynamic range related to the sparklines table (at about the 9 minute mark), instead of extending the range to accommodate growth, could you instead calculate the last position in the range using offset and counta?

@MyOnlineTrainingHub 4 года назад

INDEX is doing the same thing as OFFSET i.e. returning a dynamic range. You just approach it a little different with INDEX in that you first select the maximum number of cells your range might occupy and then you use COUNTA to determine the size. You can learn more about using INDEX for dynamic named ranges here: www.myonlinetraininghub.com/excel-dynamic-named-ranges

@pixelmasque Год назад

brilliant teacher, clearly explained with good set up and intro as usual.

@MyOnlineTrainingHub Год назад

Thank you kindly!

@bluefishaddicted 2 года назад

Very good! Thank you! Your videos I very clear and useful. But I want to use (insert) the data from a cell with a specific name range into another cell but to come together with the formatting ( font size, back color,, or frame). For instance,, I have a cell A7 with a letter inside "V" and range name “tt” and frame and red background color. And I go in cell K9 and type tt to appear V with frame and the same back color but not. Appear only V if I type +tt without format. I saw this effect in one Excel template (Employee Absent Schedule) but cannot understand how they did this. Could you advise me on how I can do this, please?

@MyOnlineTrainingHub 2 года назад

You can use conditional formatting to automatically apply a format based on the cell contents.

@bluefishaddicted 2 года назад

@@MyOnlineTrainingHub Thank you for the Advice!! I succeeded to manage now. Will be nice if it is possible for you to make a video analyzing how Microsoft did this template. I am talking about the "Employee Absent Schedule" After 30 yrs. working with electronic tables I'm still filling myself lost in this Excel Universe. This is the infinite world of options!!

@kareemkoka1747 2 года назад

can I make drop list from many of define_name range i.e fill the range by another range from drop list

@MyOnlineTrainingHub 2 года назад

I think you mean dependent data validation lists: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pjLAnpBM9dk.html

@jamesholtman4026 Год назад

I love your videos on Excel. I have a question... When using "named" cells/ranges; what is the difference between "[Name]" and "[@Name]" and "@[Name]" ??? The "[Name]" reference works for me in calculating balance in a Checkbook Register spreadsheet, but the others cause errors.

@MyOnlineTrainingHub Год назад

Thank you! [ColumnName] references the whole column, [@ColumnName] references the current row of the column where there are no spaces in the column name, and [@[Column Name]] references the current row of the column where there are spaces in the column name.

@jamesholtman4026 Год назад

@@MyOnlineTrainingHub wow! Thank you for the fast reply. I’m going to have to let this soak into my brain for a while. Again, thank you.

@jamesholtman4026 Год назад

Mynda, I'm still having trouble understanding the use of brackets "[" & "]" and the At sign "@" in name references. I have experimented with different combinations and still find that the use of any brackets causes an error. Eliminating the use of brackets gives me the results I desire in most cases. Another issue I have is that when I EDIT a Name and try to change the definition from Absolute references ($A$2) to a Relative reference (A2), when I close the Name Manager window, these values get changed to Some cell at the bottom of the worksheet. ???

@MyOnlineTrainingHub Год назад

I don't usually write the cell references, instead simply click on the cell you want to reference and let Excel write it. The issue you're having with the names is explained in this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-gCo0zL3-OtE.html

@jamesholtman4026 Год назад

@@MyOnlineTrainingHub Thank you so much for the answer. After watching it 2 times, I'm beginning to understand more and see it clearly (thick head!!!). I really appreciate all of your assistance and enlightenment.

@MrTerjeMa Год назад

Thanks a lot for a great channel. I`v got two questions. At 09:58 - is there any reason for not to remove the absolute referencing? And I followed the last part step by step, but a warning says I can`t start with the =sign. How to go about that - to accept the formula. Thanks again 👍

@MyOnlineTrainingHub Год назад

🙏 Glad you like it! You must remove the absolute referencing so that when the named range is used on each row it correctly picks up the state's data on the current row. If you leave it absolute, it will always return the data for ACT. You don't need an equals sign when referencing the dynamic named ranges in the Sparkline dialog box.

@MrTerjeMa Год назад

@@MyOnlineTrainingHub Thanks for replying. That`s far beyond my expectations. At 09:58 you kept the absolute referencing when counting the columns. At my chart the columns will expand further. Anyway, still having a problem with the basic setup. When the formula is saved, the = sign is added.

@MyOnlineTrainingHub Год назад

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@zdzislawkes 2 года назад

Hello everyone, I have one small question. Can I use the OFFSET(...) function instead of C4:INDEX(...). Are these approaches changeable? Are there any significant differences? Regards

@MyOnlineTrainingHub 2 года назад

Yes, you can use OFFSET instead of INDEX, but OFFSET is a volatile function, so if you use it too much it can have a negative performance impact on your workbook.

@inaction2024 2 года назад

Would it be easier just to turn the range into a table first and then if additional months of data are added, the spark-line will get auto-updated ?

@MyOnlineTrainingHub 2 года назад

That will work if you're keying your data into a table, but if your Sparkline data is coming from a PivotTable then you need relative named ranges.

@MattShinner 2 года назад

Thank you for this. When I copy the spark line over to another sheet like a dashboard the spark line then changes and shows no data. Any idea how to fix this? Relatively new to excel thanks :)

@MyOnlineTrainingHub 2 года назад

You probably need to reset the 'refers to' range for the Sparkline.

@sabyasachiojha3723 2 года назад

Hey thanks for the video but can you tell me, suppose you are using sum formula but you also want to include new cells added to the worksheet in that same sum formula. i mean to say suppose you are calculating sales value from jan to march ...then next month april will be added so we need to include april month values also in the sum formula so...how to do that...Pls help me out

@MyOnlineTrainingHub 2 года назад

There's a tip in this video on how to write a SUM that automatically includes the last value: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Edms-B4ViHo.html

@sabyasachiojha3723 2 года назад

@@MyOnlineTrainingHub thanks but if suppose i have some values in a1 , b1, c1, d1, e1 cell... And in one cell called total i want to have sum for a1, c1 and e1 cell values and also new values are being added every month in such a way that the old values in a1, b1, c1, d1, e1 get shifted to f1, g1, h1, i1, j1 cell and now the total which was previously including cell values from a1, c1 and e1 should now use the values for updated cells of a1,c1 and e1 along with f1 h1 j1 values Inshort earlier i would have sum formula like =sum(a1+c1+e1) But after adding new cells at a1 c1 e1 New sum formula should be =sum(a1+c1+e1+f1+h1+j1)...

@susigan 4 года назад

Someone know if Its possible the Sparkline in the example do the same thing but only select last 3 rows , every time you add a new one , pick last 3 to form the sparkline?

@MyOnlineTrainingHub 4 года назад

Not sure you mean by 'only select the last 3 rows'. You only want sparklines for the last 3? Perhaps you can post your question in our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum

@susigan 4 года назад

@@MyOnlineTrainingHub exactly - "You only want sparklines for the last 3" - ! its possible, ? before i post on forum.

@ceufrscio707 Год назад

4:27

@mmpramees 3 года назад

Is there any way we can do relative naming for images, Thank you

@MyOnlineTrainingHub 3 года назад

You can't reference an image directly in a formula, so no.

@mmpramees 3 года назад

@@MyOnlineTrainingHub I see...Thank you for your reply

@starstick9485 2 года назад

I want to use a named range in an excel Table. I have many tables that use the same data and I want them to all update automatically or dynamically. When I try this I get an error message that "you cannot use a named range in a table". How do I accomplish this?

@MyOnlineTrainingHub 2 года назад

Can you not use the built in table's structured references? www.myonlinetraininghub.com/excel-tables

@mikelennon1078 2 года назад

Strangely Named Range in my sheet is greyed out and I can't make any changes ( edit, delete, etc.....) Any solutions?

@MyOnlineTrainingHub 2 года назад

I wonder if worksheet protection is on? If not, please post your question and sample Excel file/screenshots on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@LotfyKozman 4 года назад

Hi Mynda, I have more than 500 tables in one sheet, How can I convert them all to ranges.

@MyOnlineTrainingHub 4 года назад

Hi Lofty, you'd be best to write some VBA to do this as Power Query would take a long time to set up all those queries. Or, copy the cells that contain the 500 Tables, go to a new sheet, then paste special > values. Assuming there aren't any formulas in the Tables. Mynda

@LotfyKozman 4 года назад

Thanks so much for your kind reply. Unfortunately I can't past them as values cause they contain equations. I tried to highlight all the tables and created a new range name, PQ could handle the range perfectly, but when I make changes in any table, it does not reflect in the PQ👿