Excel Relative Named Ranges - A Secret of Excel Pro Users

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

[@mackenmd]

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]

Glad it was helpful, Mark :-)

[@JHatLpool]

Nice, clear descriptions. Great demonstration.

[@MyOnlineTrainingHub]

Thanks so much 🙏

[@milicapejovic7174]

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

[@MyOnlineTrainingHub]

Cheers, Milica 😊

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Thanks, Darryl 😊

[@brighttriangle]

This was new to us. Thank you for sharing!

[@MyOnlineTrainingHub]

Thanks for watching!

[@Victor-ol1lo]

Well explained. Thanks Mynda !

[@MyOnlineTrainingHub]

Cheers, Victor. Glad you liked it :-)

[@jamesholtman4026]

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

[@MyOnlineTrainingHub]

Glad it helped!

[@stenionet]

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

[@MyOnlineTrainingHub]

Great to hear! Better late than never 😊

[@paramveerssachdeva]

Excellent, thank you so much for sharing this!

[@MyOnlineTrainingHub]

My pleasure!

[@anv.4614]

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

[@MyOnlineTrainingHub]

You're very welcome!

[@dennisd5776]

Very useful! Thanks for the tip!

[@MyOnlineTrainingHub]

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

[@gyozakeynsianism]

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

[@MyOnlineTrainingHub]

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

[@conduentservices334]

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]

Thanks so much! 😊

[@tonymukelabai1283]

Great, straight to the point. Video still valid!

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@dcasuto]

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

[@MyOnlineTrainingHub]

Awesome! Glad I could help :-)

[@kerronduncan6697]

Truly a pro tip. Thanks for sharing!

[@MyOnlineTrainingHub]

Glad it was helpful!

[@captvo]

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

[@MyOnlineTrainingHub]

You are most welcome :-)

[@w13ken]

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

[@MyOnlineTrainingHub]

You are so welcome!

[@Zvertnie]

Amazing. Thank you!

[@MyOnlineTrainingHub]

Thanks for watching 😊

[@kingkock1]

Amazing, thank you

[@MyOnlineTrainingHub]

Glad you liked it!

[@iankr]

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]

Glad it was helpful, Ian!

[@jimfitch]

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]

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

[@DougHExcel]

Thanks for the video!

[@MyOnlineTrainingHub]

My pleasure, Doug :-)

[@bravucod]

Great tutorial!

[@MyOnlineTrainingHub]

Thanks, Daniel 😊

[@connie6616]

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

[@MyOnlineTrainingHub]

Excellent! So pleased I could help 😊

[@ljudevitgaj4855]

Thanks

[@MirFayaz]

wow you are the best trainer of Excel

[@MyOnlineTrainingHub]

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

[@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 😁

[@findthetruth3021]

Your videos are amazing

[@MyOnlineTrainingHub]

Thanks so much!

[@JonathanExcels]

Very helpful.

[@MyOnlineTrainingHub]

Thanks for watching, Jonathan :-)

[@joshuamanampiu6489]

Really really good!

[@MyOnlineTrainingHub]

Glad you think so, Joshua!

[@JoseAntonioMorato]

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]

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

[@JoseAntonioMorato]

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

[@pixelmasque]

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

[@MyOnlineTrainingHub]

Thank you kindly!

[@EduardKorkia]

Thank you!!!

[@MyOnlineTrainingHub]

My pleasure, Eduard :-)

[@pinakikkkm]

Thanks!!!!

[@MyOnlineTrainingHub]

Welcome 😊

[@prakashsrinivasan7840]

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

[@MyOnlineTrainingHub]

Glad you liked it!

[@rodneyplunkett6688]

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]

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]

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.

[@leonardoggy]

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

[@MyOnlineTrainingHub]

Glad you liked it, Leonardo! 😊

[@companyconcord]

you are very wonderful

[@MyOnlineTrainingHub]

Thank you so much 😀

[@naurozmomin6631]

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]

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

[@GeertDelmulle]

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]

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

[@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!

[@EPMeokazi]

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]

Glad you liked it 😊

[@msoffice6037]

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

[@MyOnlineTrainingHub]

There’s always time to catch up 😉

[@Shady_Lane]

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]

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

[@B1897forzajuve]

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]

Thank you 😊 you can learn how to use XLOOKUP to return a range here: kzbin.info/www/bejne/aIfMfqBjrNqAabc

[@stefanogattoCH]

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

[@MyOnlineTrainingHub]

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

[@stefanogattoCH]

@@MyOnlineTrainingHub Gasp!

[@cornelbejenaru3523]

inspiring

[@MyOnlineTrainingHub]

Great to hear 😊

[@sandeepkothari5000]

Wow!

[@MyOnlineTrainingHub]

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

[@sajimon2010]

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]

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

[@bluefishaddicted]

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]

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

[@bluefishaddicted]

@@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!!

[@barttitulaerexcelbart9400]

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]

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]

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

[@dagwould]

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]

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

[@MattShinner]

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]

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

[@kareemkoka1747]

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

[@MyOnlineTrainingHub]

I think you mean dependent data validation lists: kzbin.info/www/bejne/ppuvcqGmd7Jsms0

[@Trucpq]

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

[@MyOnlineTrainingHub]

Like this: kzbin.info/www/bejne/mX6vk6ttrMRjh5o

[@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]

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]

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]

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]

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.

[@sabyasachiojha3723]

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]

There's a tip in this video on how to write a SUM that automatically includes the last value: kzbin.info/www/bejne/e5XQpGB4abucftE

[@sabyasachiojha3723]

@@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)...

[@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: kzbin.info/www/bejne/nXTSYa2CaJKCqqc

[@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.

[@mmpramees]

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

[@MyOnlineTrainingHub]

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

[@mmpramees]

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

[@susigan]

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]

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]

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

[@starstick9485]

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]

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

[@LotfyKozman]

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

[@MyOnlineTrainingHub]

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]

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👿

[@mikelennon1078]

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

[@MyOnlineTrainingHub]

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

[@excelworx8712]

Hi Mynda, your webpage returns unavailable. Is there any issue

[@MyOnlineTrainingHub]

Sorry, site was down! Fixed now.

[@ceufrscio707]

4:27