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!
@MyOnlineTrainingHub4 жыл бұрын
Glad it was helpful, Mark :-)
@JoseAntonioMorato4 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
So glad I could solve a mystery for you, Jose :-)
@JoseAntonioMorato4 жыл бұрын
@@MyOnlineTrainingHub And it really resolved! Thank you! 👍🤗😘
@conduentservices3342 жыл бұрын
i have no words to appreciate your contribution to solving our issues in just 1 small and crisp tutorials. Thanks a lot from India.
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much! 😊
@jimfitch4 жыл бұрын
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!
@MyOnlineTrainingHub4 жыл бұрын
Glad you can make use of it, Jim :-)
@iankr2 жыл бұрын
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.
@MyOnlineTrainingHub2 жыл бұрын
Glad it was helpful, Ian!
@stenionet2 жыл бұрын
That's Gold. It will save me precious time. I wish I had known this 15 years ago. Thank you very much.
@MyOnlineTrainingHub2 жыл бұрын
Great to hear! Better late than never 😊
@gyozakeynsianism4 жыл бұрын
This was great. I've been using name ranges but not in such an efficient or sophisticated way. This helps! Thank you.
@MyOnlineTrainingHub4 жыл бұрын
Great to know you can make use of relative named ranges :-)
@leerv.2 ай бұрын
"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
@MyOnlineTrainingHub2 ай бұрын
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!
@Victor-ol1lo4 жыл бұрын
Well explained. Thanks Mynda !
@MyOnlineTrainingHub4 жыл бұрын
Cheers, Victor. Glad you liked it :-)
@anv.461411 ай бұрын
Dear Mynda, Thank you very much for your lesson. appreciated.
@MyOnlineTrainingHub11 ай бұрын
You're very welcome!
@JHatLpool2 ай бұрын
Nice, clear descriptions. Great demonstration.
@MyOnlineTrainingHub2 ай бұрын
Thanks so much 🙏
@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!
@tonymukelabai128310 ай бұрын
Great, straight to the point. Video still valid!
@MyOnlineTrainingHub10 ай бұрын
Awesome to hear 🙏
@rodneyplunkett66884 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
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.
@rodneyplunkett66884 жыл бұрын
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.
@dcasuto3 жыл бұрын
WOW! I have been wanting to learn this forever!!!
@MyOnlineTrainingHub3 жыл бұрын
Awesome! Glad I could help :-)
@connie6616 Жыл бұрын
I finally found the solution to solve my sparkline relative range. Thanks so much!
@MyOnlineTrainingHub Жыл бұрын
Excellent! So pleased I could help 😊
@brighttriangle3 ай бұрын
This was new to us. Thank you for sharing!
@MyOnlineTrainingHub3 ай бұрын
Thanks for watching!
@GeertDelmulle3 жыл бұрын
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.
@MyOnlineTrainingHub3 жыл бұрын
Thanks, Geert! Yes, being able to change the scope after creation would be nice.
@prakashsrinivasan78402 жыл бұрын
I already aware of relative names but u made sparklines which is amazing 👏
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it!
@w13ken2 жыл бұрын
Great advice Mynda - thank you. Love the reference to Marching Ants 😀
@MyOnlineTrainingHub2 жыл бұрын
You are so welcome!
@leonardoggy3 жыл бұрын
8:08 - Just for this alone you have my eternal gratitude. (Hitting F2 to be able to use arrow keys there.)
@MyOnlineTrainingHub3 жыл бұрын
Glad you liked it, Leonardo! 😊
@milicapejovic71744 жыл бұрын
Thank you Mynda! As always, great tutorial! :)
@MyOnlineTrainingHub4 жыл бұрын
Cheers, Milica 😊
@captvo3 жыл бұрын
Thank you for that neat trick Mynda! Was not aware about that at all.
@MyOnlineTrainingHub3 жыл бұрын
You are most welcome :-)
@pixelmasque2 жыл бұрын
brilliant teacher, clearly explained with good set up and intro as usual.
@MyOnlineTrainingHub2 жыл бұрын
Thank you kindly!
@MirFayaz4 жыл бұрын
wow you are the best trainer of Excel
@MyOnlineTrainingHub4 жыл бұрын
Thank you! That's very kind of you :-)
@kingkock12 жыл бұрын
Amazing, thank you
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it!
@kerronduncan66973 жыл бұрын
Truly a pro tip. Thanks for sharing!
@MyOnlineTrainingHub3 жыл бұрын
Glad it was helpful!
@B1897forzajuve3 жыл бұрын
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.
@MyOnlineTrainingHub3 жыл бұрын
Thank you 😊 you can learn how to use XLOOKUP to return a range here: kzbin.info/www/bejne/aIfMfqBjrNqAabc
@DougHExcel4 жыл бұрын
Thanks for the video!
@MyOnlineTrainingHub4 жыл бұрын
My pleasure, Doug :-)
@darrylmorgan4 жыл бұрын
Excellent Tutorial,Really Helpful.Thank You Mynda :):):)
@MyOnlineTrainingHub4 жыл бұрын
Thanks, Darryl 😊
@paramveerssachdeva4 жыл бұрын
Excellent, thank you so much for sharing this!
@MyOnlineTrainingHub4 жыл бұрын
My pleasure!
@dennisd57764 жыл бұрын
Very useful! Thanks for the tip!
@MyOnlineTrainingHub4 жыл бұрын
Glad you'll be able to make use of it :-)
@pinakikkkm2 жыл бұрын
Thanks!!!!
@MyOnlineTrainingHub2 жыл бұрын
Welcome 😊
@EPMeokazi4 жыл бұрын
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!
@MyOnlineTrainingHub4 жыл бұрын
Glad you liked it 😊
@findthetruth30214 жыл бұрын
Your videos are amazing
@MyOnlineTrainingHub4 жыл бұрын
Thanks so much!
@joshuamanampiu64894 жыл бұрын
Really really good!
@MyOnlineTrainingHub4 жыл бұрын
Glad you think so, Joshua!
@ljudevitgaj48554 жыл бұрын
Thanks
@companyconcord4 жыл бұрын
you are very wonderful
@MyOnlineTrainingHub4 жыл бұрын
Thank you so much 😀
@JonathanExcels4 жыл бұрын
Very helpful.
@MyOnlineTrainingHub4 жыл бұрын
Thanks for watching, Jonathan :-)
@bravucod4 жыл бұрын
Great tutorial!
@MyOnlineTrainingHub4 жыл бұрын
Thanks, Daniel 😊
@EduardKorkia4 жыл бұрын
Thank you!!!
@MyOnlineTrainingHub4 жыл бұрын
My pleasure, Eduard :-)
@Shady_Lane4 жыл бұрын
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?
@MyOnlineTrainingHub4 жыл бұрын
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
@stefanogattoCH4 жыл бұрын
C3:INDEX(... never saw such a syntax ever! Thank you!!!
@MyOnlineTrainingHub4 жыл бұрын
Great to know you discovered something new. You can also do INDEX(...:INDEX(... :-)
@stefanogattoCH4 жыл бұрын
@@MyOnlineTrainingHub Gasp!
@sajimon20104 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
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
@msoffice60374 жыл бұрын
Thanks for sharing! I feel like I missed a lot not knowing your youtube channel :(
@MyOnlineTrainingHub4 жыл бұрын
There’s always time to catch up 😉
@cornelbejenaru35232 жыл бұрын
inspiring
@MyOnlineTrainingHub2 жыл бұрын
Great to hear 😊
@Trucpq6 ай бұрын
Can you make a training of using Function in Named range such as OFFSET, FILES ...
@MyOnlineTrainingHub6 ай бұрын
Like this: kzbin.info/www/bejne/mX6vk6ttrMRjh5o
@naurozmomin66314 жыл бұрын
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?
@MyOnlineTrainingHub4 жыл бұрын
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
@kareemkoka17472 жыл бұрын
can I make drop list from many of define_name range i.e fill the range by another range from drop list
@MyOnlineTrainingHub2 жыл бұрын
I think you mean dependent data validation lists: kzbin.info/www/bejne/ppuvcqGmd7Jsms0
@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.
@barttitulaerexcelbart94004 жыл бұрын
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!
@MyOnlineTrainingHub4 жыл бұрын
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.
@vinamrachandra96114 жыл бұрын
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
@inaction20242 жыл бұрын
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 ?
@MyOnlineTrainingHub2 жыл бұрын
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.
@dagwould4 жыл бұрын
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?
@MyOnlineTrainingHub4 жыл бұрын
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
@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
@sandeepkothari50004 жыл бұрын
Wow!
@MyOnlineTrainingHub4 жыл бұрын
Hope you can make use of relative named ranges :-)
@zdzislawkes3 жыл бұрын
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
@MyOnlineTrainingHub3 жыл бұрын
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.
@MattShinner3 жыл бұрын
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 :)
@MyOnlineTrainingHub3 жыл бұрын
You probably need to reset the 'refers to' range for the Sparkline.
@sabyasachiojha37232 жыл бұрын
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
@MyOnlineTrainingHub2 жыл бұрын
There's a tip in this video on how to write a SUM that automatically includes the last value: kzbin.info/www/bejne/e5XQpGB4abucftE
@sabyasachiojha37232 жыл бұрын
@@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)...
@starstick94852 жыл бұрын
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?
@MyOnlineTrainingHub2 жыл бұрын
Can you not use the built in table's structured references? www.myonlinetraininghub.com/excel-tables
@bluefishaddicted3 жыл бұрын
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?
@MyOnlineTrainingHub3 жыл бұрын
You can use conditional formatting to automatically apply a format based on the cell contents.
@bluefishaddicted3 жыл бұрын
@@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!!
@mikelennon10782 жыл бұрын
Strangely Named Range in my sheet is greyed out and I can't make any changes ( edit, delete, etc.....) Any solutions?
@MyOnlineTrainingHub2 жыл бұрын
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
@ceufrscio7072 жыл бұрын
4:27
@excelworx87122 жыл бұрын
Hi Mynda, your webpage returns unavailable. Is there any issue
@MyOnlineTrainingHub2 жыл бұрын
Sorry, site was down! Fixed now.
@susigan4 жыл бұрын
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?
@MyOnlineTrainingHub4 жыл бұрын
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
@susigan4 жыл бұрын
@@MyOnlineTrainingHub exactly - "You only want sparklines for the last 3" - ! its possible, ? before i post on forum.
@mmpramees3 жыл бұрын
Is there any way we can do relative naming for images, Thank you
@MyOnlineTrainingHub3 жыл бұрын
You can't reference an image directly in a formula, so no.
@mmpramees3 жыл бұрын
@@MyOnlineTrainingHub I see...Thank you for your reply
@LotfyKozman4 жыл бұрын
Hi Mynda, I have more than 500 tables in one sheet, How can I convert them all to ranges.
@MyOnlineTrainingHub4 жыл бұрын
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
@LotfyKozman4 жыл бұрын
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👿