when you write Excel formulas for the most part you are going to use cell references and that is a fairly standard way of writing formulas however on occasion you may need to use something else to help people understand the formulas that you are writing for example you might have someone who isn't used to using spreadsheets in general and they really kind of get confused when it comes to figuring out cell references or maybe it's just that the spreadsheet is so large that using cell references t
akes quite a bit of time to figure out where are those cells and what's the limit and where do they end and all that kind of thing so what you can do instead to help the person who may be reading your Excel spreadsheet and looking at your formulas and trying to understand what they mean you can use a named range instead now there's one kind of particular thing that we need to look at with that but named ranges can make a difference between people understanding your formulas and really struggling
to understand them hi I'm Terry one the consumer technology specialist for Midcontinent public library and let's look at it cell and using named ranges as we look at our kind of small example today we can see that we have a company that has different regions and as I look at this we have a northeast southeast Midwest Northwest and southwest regions and this is evidently for an entire year because it has numbers for quarter one quarter two quarter three and quarter four and what we would like to
do is add in the totals here for the different quarters in row nine but we would also like to get the totals for the different regions in column F and generally what we might decide to do is in B9 we might go up to autosum and click on it and it would try to fill in the cell references for that first C column that we want to add so it' be equal sum B4 through B8 and certainly that works but what if we have someone who's going to be looking at this spreadsheet who isn't as familiar with spreadsh
eets and you think that really it might help them if instead of using cell references you use actual names for the data that you are going to be using using in that case what we can do is Select B4 through B8 and we would like to name this range but we do need to be a little careful we do know that qtr1 we can't have a space in the name and we can't start a named range name with a number so let's go up to the name box and click and what happens if if I just type qtr1 because now I don't have a s
pace well this brings up another really important point when you work with Excel so let me press enter and look what it did at the far right we have qtr1 Row one so Excel is so large that sometimes it's easy to try to come up with a named range that in fact is really a cell name because here we have qtr 1 2 3 and four but those are cells so we need to be a little careful that we don't confuse the issue by using a cell name for a named range well let me go back home and this time I still like the
idea of using qtr1 but what I'm going to do is Select those cells and go to the name box but I'm going to type qtr and add in an underscore this time one now the other caution is I definitely need to press enter Because you can see I even have a blinking insertion point to the right of the one let me press enter and now that sets that named range into the workings of excel well let's also then name quarter 2 and we'll do it the same way go to the name box and click in it we'll type qtr2 and pre
ss enter and now it's a name in the name box let's look at C quarter 3 I'll click in the name box type qtr3 and press enter and then we have quarter 4 go to the name box qtr ror4 and press enter now you'll notice that I have a drop down arrow here on the right hand side of the name box so I can click that drop down and look these are actually locations now quarter 1 quarter 2 quarter 3 quarter 4 I could click on this qtr1 for example and it takes me right to those cells so the name box when I ty
pe in a Cell reference takes me to the cell reference if I type in a named range and look at the drop- down list I can also navigate to a named range well that is pretty slick isn't it well now let's think about what we can do to get the total for the quarters generally when you want to add a column of numbers you would go to in this case B9 and then go to auto sum or you might even just say equal sum and then Excel would try to fill in with the cell references that would be B4 through B8 but we
have a range name so in fact whether I type equals sum and quarter one or I even go to that auto sum button now that I've name the range look what happens it says you want to add the numbers that are in the quarter 1 range now that is really slick and it is so intuitive compared to even older versions of excel well yeah that's what we want to do so we will press enter and there's our answer now when I click in B9 notice that in my formula bar it actually says sum of quarter 1 which just maybe m
akes more sense than sum of B4 through B8 and keep in mind this is a really small spreadsheet if you had a really large spreadsheet that also kind of makes the difference I I don't have to go scrolling down through the spreadsheet to figure out where the end is to make sure that that's all accurate it's all just right here done for me the one thing though is that then when you use cell references you would click in B9 find the fill handle get that skinny plus sign and click and drag across accep
t look at what happens when we use a name range well let me click in C9 here and when I look at the formula bar uhoh this is still quarter 1 and when I look at D9 it's the sum of quarter 1 so the only thing about using named ranges is that they are absolute references so we can't use the fill handle to click and drag to copy that formula because well it's just the same formula over and over again so let me remove those three although this isn't very difficult either I simply go to C9 now go to a
uto sum and it says well you want to add quarter to I'll press enter there we go now I can go to D9 go to auto sum and it picks up that this is the sum of quarter three press enter and then in E9 go to the auto sum button and again it's picked up that this is quarter 4 and I can press enter and now I have the totals for each one of those quarters well if I can get a total for a quarter guess what else I can do I could also get totals for the regions but before I do that I need to create named ra
nges for each of the regions so let's look at that let's start with the Northeast region which is in row four and we're going to select B4 through E4 and again we're going to name this range the same way that we named the quarter ranges so I'm going to go up to the name box click in the name box and we're going to type in E and Lily we can even have just two letters for a named range and it will work so let's press enter and then let's also name Southeast which is B5 through E5 and we'll go back
up to the name box and we'll say Southeast press enter we'll name Midwest we'll first of all select B6 through E6 and type MW press enter then we'll select B7 through E7 go to the name box and this is again Northwest press enter and then Southwest is B8 through E8 go to the name box in Southwest press enter we can again go to that drop down arrow that's to the right side of the name box and look now we have in alphabetical order Midwest Northeast Northwest and then of course we have the quarter
s that we created and then Southeast and Southwest well let's go ahead then and in F4 starting out out let's put in our total for the Northeast region and I'm even going to go to home here and go to auto sum and it picks up that I need the sum of Northeast press enter now I have to remember that I cannot use the fill handle that I need to compute each of these individually but that's okay uh to calculate a total is an easy process so we'll go back up to Auto Sound it picks up that that is southe
ast press enter the next one will go to autosum now this is kind of interesting because with autosum it's saying oh so do you want to add the cells above because the idea of Auto sum it looks above first and then to the left second so in this case we're going to have to disapoint the sum and we're going to need to actually type in M now as soon as I type the m i get all sorts of functions here that as I come down here is Midwest and look at that little icon for Midwest it's showing us that it is
a named range now we can click on that and it even in the highlighting shows me what's going to be added I probably want to end the parentheses there and press enter now let's see what happens with Northwest this is kind of interesting I can go to autosum and it's going to try to pick up the cell contents above where we're putting in some and all we need to do is say no no no that's not what we want to do we want Northwest so we'll type in in and immediately it takes us to the list of in named
ranges and functions so we can see Northeast here we're going to have to be a little careful all we have to come down till we find an alphabetical order Northwest but these icons really help you find what you're looking for and then press enter and if you have something like this happen you can always go to the formula bar and just fix it in the formula bar so then our last different region here is Southwest and I might even this time just type equals sum left parenthesis and then let me type S
and again it's picked up the southeast before it picked up the Southwest let's see if we can actually get Southwest in here and I may need to instead of single clicking double click aha now I can end the parentheses and press enter and so I have very readable formulas yet again equals some northeast southeast Midwest Northwest Southwest and it just makes it so much easier to read again large spreadsheets for people who may not be familiar with Excel or spreadsheets in general named ranges really
make a difference now let's look at another way to name ranges that may give you a few more options or if you just like to have a little more control over what you're doing then this may be something that you would like to do so first of all let us go down to the worksheet tab for quarter sales 2 and you'll notice that it's a direct copy of the first worksheet that we saw so it still has the five regions and the four quarters and we would like to create totals well what we can do instead of sel
ecting the cells and going to the name box is that we can go up to the tabs above the ribbon and go to the formula formulas Tab and when we go to the formulas tab we can see that in the second of the different groups that are on this particular tab we have defined names with several buttons here that we may want to explore and investigate the first one being name manager Define name using formula and then even create from a selection so let's try some of these possibilities now and first of all
let's just look at the name manager and see what we've done so far and how they show up there so let's click on name manager and look at that it shows us each one of those items that we have given a name name to it shows you the actual values that are in those cells and then it tells you its location in the workbook so this is the quarter sales worksheet and then what sales are being referenced and right now it says that the scope is the entire workbook so no matter how many worksheets I have I
can always refer to any of these range names because the scope is workbook now I can change that if I want to notice also if I need to change the cell references within a range I could go to edit and let me click on that and so right now it tells me that I've selected Midwest the scope is the workbook and then down at the bottom it shows me that I have B6 through E6 but maybe I need to make that G6 or something instead and I can even add a comment if I need to do that and then of course we'll ca
ncel but that is how I could edit I can also say we've done away with the northwest region and so I may decide that I want to delete that Northwest named range and I could do that if I wanted to that as well so there's a lot of help and information just right at your fingertips if you come to the name manager well let's close out for a second now that we've talked about that a little bit and because we do have these other buttons on the right so the first one is Define name so we could again sta
rt here with B4 through B8 and Define the name and I can either Define the name name or apply name so we'll Define the name and it gives us basically the same dialogue box that we just saw but in this case we're going to call this qtr underscore one and then we need to make it slightly different so I'm just going to call it 1A what is the scope well it could be the entire workbook it could be just the one worksheet quarter sales or another worksheet quarter sales 2 or the sales 3 so basically it
's all of the different worksheets that are available but we'll just say workbook and then it even gives me a chance to edit the range of this particular named range by coming down here and doing some editing if I need to do that but we'll say okay now we can go through the same process again for qtr2 and we could go back to define the name and it's a couple more clicks but depending on what you need to do it could be precisely what you need to get the answers that you want so here we have a new
name qtr2 a and we'll click okay we'll again select these names Define the name and then this would be qtr rore 3A and one more quarter 4 Define the name and we'll call this qtr4 a and click okay now when it comes to the totals notice how it says use in Formula well first of all let's just kind of do a preview of that so we can click and it gives us all of the range names that we have created oh so what we can do is this equals sum left perins and then I can go up to the ribbon and select use i
n formula and what is the first one qtr1 a and it puts that name in for me and then I could put in the right parenthesis if I want to do that well let's do that again let's finish out these quarters so again I can say equal sum left parins use this in a formula and it's going to be qtr2 a press enter quarter 3 is equals sum left parenthesis using a formula qtr that's 3A press enter and really while the fill handle is perhaps faster this isn't impossible it's actually pretty easy to do using the
formula and this is quarter 4 a and press enter and we have the totals then as I click in B9 and look at the formula bar it just says equal sum quarter 1 a quarter 2 a 3 a 4 a and that is how I can definitely Define the name and use it in a formula there is one other button in this defined names group and that is the create from selection and it's really kind of an interesting choice you have to be a little more careful when you use it so create from selection you can automatically generate name
s from the selected cells and it has default settings that you may not suspect so let's create from selection and it says I can create names from values in the top row which basically means means I could create names from the top row of where I've selected the left column from where I've selected the bottom row or the right column let me make sure that I have in fact selected A3 through A9 because let's say that I want to create a named range that's called regions I can create from selection it'
s even saying top row really means the top cell of what you've selected so it's a little confusing and then we'll click okay now let's look at the name manager because it's really hard to tell what's going on with that when we go to the name manager we can see that we now have a regions named range right here and here are the items within that named range northeast southeast Midwest Etc and then we can close that can also be just extremely helpful now here's where it gets kind of interesting let
's say that we have quarter Sales New Year this is a totally blank worksheet so what I could do is click in A3 and where it says create from selection remember we have our name manager I could decide that I want to use in a formula regions press enter oh my goodness and it fills in the names of those regions for me is that crazy or what that could really help me very quickly fill in information from another worksheet that I simply maybe want to copy part of that information over over here to thi
s new blank worksheet while you may not use named ranges all the time I think you can see how they could be very helpful and useful and save you some time and maybe some heartache if you are working with people who don't understand cell references very well they can be just invaluable in helping you explain the information that is in your spreadsheet so that everyone understands what's going on and how your formulas work in just a a very basic way so when you create your formulas and when you lo
ok at the information that's in your spreadsheet be sure to consider using named ranges to learn even more be sure to go to mymcpl.org slashonline learning for such great resources as universal class Learning Express Library LinkedIn learning and udemy business we also Premier videos every Wednesday and Friday at 1:00 if you miss a video premiere you'll still be able to find it later when it's convenient for you you can find us on Facebook at mcpl 360 and you can find us on YouTube by going to o
ur mcmo Channel
Comments