SECRET Excel Named Range Shortcuts to Save Time
HTML-код
- Опубликовано: 3 авг 2024
- Defining names in Excel has a load of benefits, from faster formula writing, easier to read workbooks and more robust files. In this video I cover the various types of names you can define and some shortcuts the experts use. Download the Excel file here: www.myonlinetraininghub.com/e...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
0:00 Introduction
0:21 Types of Defined Names
0:37 Defining Named Cells
1:37 Defining Named Ranges
2:07 Defining Names from Tables
2:56 Defining Named Constants
4:07 Defining Named Formulas
5:42 Defined Name Scope
8:53 Defined Name Shortcut 
Наука
Hi Mynda , I thank God for people like you, thanks very much for your contents , God bless you always !
So glad I could help 😊
I had never given much thought to scope when working with named ranges. I appreciate your clear explanation with examples. That last bit using "=!B2" was fantastic!
Glad it was helpful, Patrick!
It seems there's always something more to learn about Excel Names.
I just realized that storing a formula in a name is very useful to reuse it around the workbook (with all the advantages that it represents in terms of maintaining the formulas), but also not lose the formula in case we delete all the cells that contained it. It's like a UDF without VBA code.
Glad it was helpful!
Even the videos you think are beginner or intermediate I find useful. I am always picking something up. Thank you!
So pleased to hear that, Rodney!
I have been using defined names quite a bit lately and it is such a great tool. But, there is always something to learn. There were certainly some good tidbits in here. Thank you.
Cheers, James. Glad it was worth your while to watch 😊
I always love the handy shortcuts you share! Such time savers. Thank you so much!
Glad you like them, Bob!
Thank you for covering name scope. So many videos cover just naming a cell or range, but hard to find one that covers everything! Super simple and easy to follow along. Cheers.
My pleasure!
Love it! Great trick for this "=!A1". Thank you!
Glad you liked it!
Very clear Mynda. I knew how to do this, but usually forget to do so in my day to day work. Thanks!
Glad it was helpful, Chris!
Thanks Mynda, I always learn something new from your channel. At last in this video, Removing sheet name infront of ! in formula and using Same Name Range with different values as per different sheets was great idea.
Great to hear, Girish!
Thank you for another great video. I needed this a few days ago so i came to your channel. Helpful and clear as always.
Ty
Glad it was helpful, Larry!
Great tutorial! Thank you Mynda!!!
Cheers, Ivan!
eloquent explanation of the different paths of name creation. didnt know you could create a variable name without first choosing a cell with value, sweet !!
Glad you discovered something new. Naming an empty cell will carry the value of zero, just as it would if you were to enter a formula that referenced the cell.
Thank you Mynda. It's good to be reminded, especially as I clearly don't use defined names enough. But the thought of models using Named Constants fills me with dread!
You're welcome, Henry! Named constants are better than hard keyed values in cells, don't you think?
@@MyOnlineTrainingHubYes if I had only those two choices, but both are anathema for me!
Very clear explanation. Thanks for the solution to copying worksheet and creating duplicate names. Very helpful. Thanks Mynda
Glad it was helpful, Grainne!
Very useful video! Thanks Mynda for some cool tips on named ranges, which are going to help a lot! 😊👍
Great to hear, Vijay!
I’ve been somewhat confused by names in Excel for years… great explanation!
Glad it was helpful, John!
Hi Mynda!Great Tutorial With Some Really Handy Shortcuts...Thank You :)
Glad you like them, Darryl!
Thanks a lot. Your videos are always a great help to optimize my job☺️
That’s great to hear 🙏
Great tips Mynda! Thanks!!
Cheers, Wayne!
Very helpful and thorough. Helped me through my college project. Thank you!❤
Awesome to hear 😊
Excellent tutorial, as always!
Glad you liked it!
Wow - I just chanced across your channel today, and that little tip about just using the "!" in front of a name is priceless! A while ago I was adding some sheets to a workbook that used a lot of names, and ran into many issues with them. This at least solves one of them. The other big issue I had was that if I copied one of my worksheets to another workbook it had a tendency to drag along all of the names from the original workbook as well. I ended up writing some macros to clear them out, but it was a right pain, and made me cautious about using names since then. Maybe I will give them another chance, especially since I recently upgraded from Excel 2013 to MS 365, and am now starting to learn about much of the very useful new functionality it provides.
Many thanks for your videos. I have subscribed, and will be watching a lot more of them!
Awesome to hear you found my video helpful! Unfortunately, copying names when moving/copying sheets is by design so your formulas don't break.
You are great! Thank you for your work!
Thanks so much 😊
Your videos have helped me so much! Thank you for sharing all that you do =)
You are so welcome!
Very good - clear and concise.
Glad you liked it 😊
Really interesting stuff, thank you !!
My pleasure!
Thank Mynda. I was unaware of the various options of named ranges. As powerful as they may be, it seems there is an inherent risk with using the correct named rNge and/or updating values correctly, particularly if the workbook is shared with other users.
Thanks for showing the options nonetheless.
My pleasure, Matt. Yes, if you're going to hand over a workbook that uses defined names then you need to take some time to explain it to them.
Good one during complex calculations
Glad you liked it, Shoaib!
Thanks! Great video
Glad you liked it!
last trick was awesome.
Glad you liked it, Rajan 😊
That last tip is cool, where using a blank sheet name (just the '!') you get a "this sheet" style reference.
Glad you liked it 😊
If you want to create _sheet-scoped_ named range with Name Box, you need:
1) if sheet name does _not_ contain spaces, enter: *Sheet2!MyRange* (i.e. sheet name plus "!")
2) if sheet name does contain spaces, enter: 'Sheet 2'!MyRange (note single quotes around sheet name)
Thanks for sharing, Johny!
@@MyOnlineTrainingHub 🙂
Thanks Mynda
Pleasure 😊
Vavvv, =!$B$2 is very very hidden tips Mynda! Thank you for sharing.
And, I would liked to see this name ranges including Hyperlink because Hyperlink likes named ranges to navigate between sheets and ranges.
and,
There is an unknown screet tip is that when defining named column and row ranges at the same time for an specific range, you don't need to use index and match or Vlookup or Xlookup formulas. Instead, just use defined named ranges like = A1:E1 B2:B5 (intersection of ranges give us the desired value )
So, name manager and using named ranges in differeten type of the application in excel is either saving time or helping data management system of excel (less consuming time)
Glad you liked it, Emre! Thanks for the reminder about the space intersect operator. I wrote about that back in 2012, but rarely use it: www.myonlinetraininghub.com/excel-factor-15-the-lazy-lookup
@@MyOnlineTrainingHub Mynda, good article about this unknown excel tip. and it is making excel lookup functions more faster and attractive.
Besides, I didn't know about space intersect for type of usage like: =SUM(FY_2010:FY_2011 Skirts:Shorts)
Absolutaly, crazy and lazy:)
Thank you for sharing this useful tricks
And,
I haven't seen VBA tutorial videos in your channel. Could you please add some good examples with mixing other excel applications regarding VBA?
beautiful presentation.
Thanks so much!
the last one... makes me swallow my whole cup of coffee in one go !
😁 glad you liked it!
That was EXCEL-LENT!
Thanks so much!
Excellent
Thank you so much 😀
Hi, how I can use the named range in VBA macros? Thank you. Greetings from Rome, Italy.
Very helpful as always, but it might sadden you to know that the biggest takeaway for me was finding out that I could CTRL-Click-Drag a new worksheet to create a copy!
#mindblown 😄
😁 the little tips are often the best. Glad you liked it.
As allways much usefull, thanks
And funny as it is, just today I show a college the function, but you Are much better to give the hole picture
Glad it was helpful, Ivan!
very helpuflull!
Great to hear 🙏
Thanks
Welcome 😊
谢谢😊
You're welcome 😊
Thank you for your very clear explanation and tricks to defining Names. How can I "print" the list of "Defined Names"?
On the Formulas tab > 'Use in Formula' > Paste Names > Paste List. This will paste them to the worksheet from where you can then print them.
Brilliant and interesting as ever Mynda, thank you. I've learnt some new things here regarding name types and scope but I think named ranges are widely misunderstood. Do you use them much?
I've inherited workbooks with really messy Name Manager definitions with #REFs everywhere that needed to be tidied up. This seems to be because named ranges were set up with a default Workbook scope and when the original worksheet was deleted the named range remained as orphaned. In one case I couldn't even copy a worksheet without 100 error messages.
Glad you liked the video! I do use names quite a lot, but yes they can create a messy workbook if you don't manage them properly.
super
Thank you 😊
Genius
Glad you liked it, James!
Thanks for this video! I have a similar situation presented in the scope and shortcut section of the video.
I have a workbook for invoicing partners. It has a summary sheet that refers to the subtotals from each additional sheet. Each asset is tracked on a separate sheet, and can be as many as 20 or more assets/sheets. Would using Sheet level scope to define the subtotal columns be the best option for the summary sheet?
As it is now, the subtotal columns are just referenced by their cells and can grow and shrink each quarter. I have to manually adjust the sum formulas on the summary sheet for each asset.
Each quarter, a asset is deleted or added, where I just create a copy of the last sheet. If I use Sheet level scope, will it copy the defined name to the new sheet?
Yes, and yes.
Very good explanation, I know should use the feature more, but found when you start using tables and pivot tables name range management can become a bit of nightmare.
Thanks, Clive. When you start using Tables you don't need defined names because Tables have their own version called Structured References: www.myonlinetraininghub.com/excel-tables so you're already on the right track 😊
I have used Names to help with formulas in a person spreadsheet which shows when bills are due and allows for weekends. e.g =IF(OR(MortgageWeekday,MortgageWeekend),MortgageAmount,0) where MortgageWeekday and MortgagWeekend have formulae that look up a date, see if it is a weekday or weekend and also if it matches the due date. What I can't figure out is how to allow for bank holidays within the formulae.
Hard to say from your formula, but you might like to check out the WORKDAY.INTL function to allow for weekends and holidays: www.myonlinetraininghub.com/excel-functions/excel-workday-intl-function
How do I add suffix in sequence 1,2,3,4,5 etc and when renumbered again when the column name change for example
Apple is 1 banana is 2 and Orage is 3, apple again is 2
Not sure what you mean exactly. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, is there any way to hide some cells data in printing without hiding the row of those cells?
Thanks
Another way to hide data is to format it the same colour as the cell.
It probably not related with the topic. I have problem when locked some data, it could'nt be filtered. Can Excel lock some data meanwhile we still able to filter data we want to filter. Thanks for your response
Not sure I follow the issue, Ambar. Please post your question and sample Excel file on our forum where we can replicate the issue and help you further: www.myonlinetraininghub.com/excel-forum
Can we use named cells in conditions of countif for example? I'm trying to write something like this: countif(grades_range,">pass_score") where pass_score is the named cell; but it always gives a zero.
Yes, you can. I suspect there's something else wrong. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I'm doing a timesheet in excel and wanted to know how to name a range (3hrs, 3.5hrs, 4hrs, 4.5hrs, and 5hrs)
Not sure what you mean by those times as a named range. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
In a named cell range.. what if i want to only sum specific few cell rows and not all? =SUM(US_Sales) is summing all rows. I only need some rows to be summed
Hard to visualise what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Nice, thanks! Mynda Define Names for Cells does not work with Calculated Columns. I have a Pivot Table and I wanted to make it a table without a Data Source. Now when I am trying to add 2 Calculated Fields Values by defining Names and then adding those two columns it does not work. It works if I just use the formula =A2+B2 but with names Let us say my names are = Order_Received + Order_Shipped
it does not work. Thanks
I'm assuming Order_Received and Order_Shipped ranges containing more than one cell e.g. A2:A100 and B2:B100, therefore
= Order_Received + Order_Shipped is the same as
=A2:A100 + B2:B100
Excel doesn't know what to do with those ranges. You'd have to write it:
=SUM(Order_Received, Order_Shipped)
If that doesn't solve it, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thanks! Mynda this formula =SUM(Order_Received, Order_Shipped) did not work it gives you the total for the whole range and paste it in every cell. I will just go with =A2+B2 and then Drag it down. That is the only way it works. Thanks
Ah, ok. Sounds like you need a relative named range as explained here: ruclips.net/video/gCo0zL3-OtE/видео.html
@@MyOnlineTrainingHub Thank you, Mynda! I used Structured References now and it worked. I used = [@[Orders Received]]+@[Orders Shipped] I will watch the video link you pasted right now. Thanks
Today I had seen a formula. =vlookup("value1"&"value2",'Pivotsheet (S)'!c2:m30,2,0)
May I know, why we use (S) reference along with the sheet name in which we will have 2 pivot tables which are formed by the same source data.
As I seen this formula will combine value 1 and 2 and return the value. But please tell me how this reference fetch data from pivoted cache and return the result.
The (S) is in the sheet name, i.e. the sheet name is Pivotsheet (S) It's not fetching data form the Pivot Cache, it's fetching it from the second column in the range C2:M2 on the Pivotsheet (S)
@@MyOnlineTrainingHub sheet name is just " Pivotsheet" not Pivotsheet(s) . This s looks different kind of reference.
There exists VBA to unhide hidden named ranges but there seems to be no information on how to selectively hide things in name manager... Anyone have any ideas?
See this post: exceloffthegrid.com/hide-named-ranges/
3:28 When I input the formula =Sum(US_Sales*fx_constant), I get #VALUE! error. I have followed all the steps as mentioned in the video for defining fx_constant and US_Sales, but stll not getting the sum amount. Can anyone help me with this error?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I have a spreadsheet using named ranges in formulas. The formula returns 0 to the cells, yet the formulas evaluate correctly (when I use F9 in the formula bar or use the Evaluate Formula tool). What am I missing? I am current with my M365 subscription.
Formulas return zero when the cell it evaluates to is empty. If you prefer not to see the zero, you can use a custom number format to hide them: www.myonlinetraininghub.com/excel-custom-number-format-guide#_Toc474757758
@@MyOnlineTrainingHub Thanks for the quick reply and sorry for not being clearer. The formulas I've written should return values other than zero. It's only the cell containing the formula that doesn't cooperate. I "Evaluate Formula" and the formula resolves to the non-zero value I'm expecting. I highlight the formula in the formula bar, hit F9, and the proper non-zero result is displayed. Again, it is only in the cell itself that the value is 0. I don't have any custom formatting applied to the cells. It's very perplexing.
@myonlinetraininghub This is my formula =ROUND(($AG85)+((dRev_1-dRev_0_Plan)*AH$13),0). The answer, confirmed by F9 and Evaluate Formula, is 543, just not in the cell, which shows zero.
Hard to say without seeing your file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How to use named range in sumproduct? Please help
You can use a named range in any of the SUMPRODUCT arguments. If you're stuck, you might like to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Is her accent British or Australian.? I am confused!
It's Bristralian 😁
EXCELlent Work....
I got some Query in last point. 10:10
While I have saved Name with "NamedOnceNamed" giving reference for "SheetName" as "=!$R$335".
Now as I move to other Sheet giving Name as "NamedOnceNamed", it returns exact results.
But, When I put Formula as "='[WorkBookName.xlsx]SheetName'!NamedOnceNamed" OR "SheetName'!NamedOnceNamed" it returns #Ref! Error.
While putting "SheetName!R335" gives the SheetName Value (which is correct OffCource)any comment?
With this type of name you cannot explicitly call it with the sheet or workbook name. It can only be used relatively.
@@MyOnlineTrainingHub
Much obliged.
Your efforts make me learn much more.
Thanks