in this video let's talk about named ranges in Excel and Google Sheets
let's do a basic example here let's say I have a few numbers
and if I wanted to get the total of these I would go here and do something like sum open parentheses
and then select this range then I can close parentheses hit enter I'm going to get my total
so that's one way of doing this now what I could do instead of using this regular ranges like B4
through B6 I could go here select this range and go to this box up here this is what we call a name
box let me just zoom out here so you can see how this looks so we have this name box up here and
basically we name this range so I'm going to call this for example coffee I'm going to type the name
hit enter now I got a new name called coffee which means I should be able to do some and then type
the range C called coffee and it's going to show up and if I enter that gives me the total so that
same name box that I did right here is available in Excel too exactly in the same spot right here
see you select your range you go click here type the name hit enter you get exactly the same result
now what's also nice about named ranges is that this name coffee is now available
across this entire file so I could create new tabs down here and from any tab I could
do equal sum and then the name coffee like so hit enter and I would be getting
that same total so this name coffee is now available across this entire file right here
so something you want to remember about these named ranges if I go back is that at some point
you may decide you want to expand the range instead of having these three cells in the
range maybe you want to add a couple of more down here right so if you wanted to change that
coffee to include these two in it one thing you cannot do is just select like this type coffee
and hit enter so it's not going to allow you to override existing names I already have a named
range called coffee so now I couldn't use that same name for a new Range so you have two options
here you either have to give it a different name or you have to change the existing one so to give
it a new name obviously that's going to be pretty straightforward you would just select all of these
go up in this box and just type any other name I could just call this like coffee 2 or something
and by the way you cannot have spaces or special characters you could have underscores in this
names so see now it's called coffee two I could go here and change this to coffee too which will
include those new numbers but if I want to change that existing coffee name then what I would do I
would go to this data and here under data we have named ranges and this is where you would basically
find that particular name click edit and modify this range so this is where you can modify it or
if you don't want to keep it you can click on this trash and delete it now in Excel the way you're
going to find those names if I go back to this once you select something and you name this coffee
up here it's going to be all the same the same way you would have to go back and modify the range and
you would find it under this formulas tab up here under this name manager so if I click on his name
manager that's going to give me the names that exist and if you wanted to delete it you delete
it here or you click edit and change the range to a different range I'm going to cancel this
so one other thing I want to mention here when you work with some files that other people made
you probably don't know what names they've used in their spreadsheets or if they use any at all
so for that reason what are you gonna do here see right here this is the name manager so using this
name box we could basically just go and type for example A5 and hit enter and see how it
takes me to that cell A5 and I could also just type that name coffee and hit enter and see how
it highlights that range coffee but what I can also do if I click on this drop down it's going
to show me what are your existing names that are available in this particular file and you can see
that in this file I got coffee and I got coffee too now you could click at any one of these so
for example just to give you an example if I'm on this tab and I go to the drop down and click
on this name coffee you'll see it will take me to the right sheet here and it will highlight
the range so it basically shows me what that name coffee is or if I go to coffee too see I can see
what it is and it works exactly the same way in Excel you would open this drop down now in Excel
I only did one so you can see that particular name appears here in this list coffee and if you
selected something else you open this and hit coffee it will select it for you and basically
just display what that actual named range is so if you're wondering what that name refers to you
can always open this box click on the name and you'll find out what it is now one other thing
I'm going to mention is that with named ranges you can name a particular range like when you select
the range it could be like a range like this it could be just a few cells like that you could
also name a range as a column so you could click on this whole column C and name the range up here
hit enter and now you got this column named and it will refer to it as a column so if
I go to my formulas and name manager you'll see that this my range what it refers to is
this entire column C so it doesn't go from C1 through C I don't know whatever it just refers
to column C so the same thing you could do in Google Sheets you can just click on the whole
column or the whole row for that matter as well or multiple rows multiple columns this works
just fine another thing to know about named ranges is that name ranges basically create
something similar to an absolute reference so what that means is that if you use that name
in your formula and you drag it down that range is not going to move so for example
this formula if I just go back here and do a regular sum and point to this range
with a regular reference like this if I start dragging this formula down you'll see how that
range will move with every row to the next row below but if I drag down this one which has
this name then it's not going to move so we basically keep referring to the same name
which ends up being the same range so essentially that becomes similar to when you select this range
and do F4 or at dollar signs to make it absolute reference and when you drag this down it remains
the same in all of these formulas that's what you end up getting now so far all of this was
exactly the same in Excel and Google Sheets but what's also interesting about this named
ranges is that you can use them from different files as well and this is the part that's going
to be a little different between Excel and Google Sheets just because the way you refer
to other files in Excel and Google Sheets is a little different so for example let's say
I have this file let me just enter a couple of numbers here and if I just go ahead and
highlight this and name this range I'm just going to call this apple that's the name of
this particular range so now if I wanted to point to this range if I open another file
so regularly if I go here and do something like equal sum I could then go to this file select this
close parentheses all here in the formula bar and hit enter and if I open this formula you'll see
what this says so this refers to book one being the name of this other file and if that file was
closed this would be the drive or the SharePoint wherever you're linking to the full path to the
file and then we got exclamation sign and then the name of the range notice that this doesn't give me
the name of the tab even though this tab is called sheet1 we just have the name of the range itself
called Apple so we could point to that Apple range from a different file too but we need to make sure
that we have a reference to the file before that and there is exclamation separator between those
and that's pretty straightforward so in Excel you could literally just hit equals to sum open
parentheses go to the other file select it and it automatically picks up that name called Apple for
me now let's try to do the same sort of thing in Google Sheets so I'm going to go back to Google
Sheets so this range right here let's name this again I'm going to call it Apple just to
show you the similarities and differences between Excel and Google Sheets so now this
is called Apple which means from this file I could do some apple and that would work just fine right
but now I want to do this from a different file so I'm going to go to a different file and to be
able to refer to anything from a different file in Google Sheets we have to use import range formula
now this is going to ask us for URL for the range so I'm going to do in quotes the ID or
the link to the other spreadsheet so I'm going to go here copy this URL and paste it here and that
needs to be in quotes from both sides you can do either the whole link or just this ID right here
I'm going to give this just a length for now comma and then we need the range as a string
so I'm going to open parentheses and then in parenthesis here let's just enter Apple
which is the name of the range so I'm going to hit enter now because we're linking to a
different file we need to allow access and verify that we actually have access to that spreadsheet
otherwise we could have linked to anything right and you can see I'm already pulling the range
like this with this name apple and now we can just go ahead and do some around this whole thing
and close parentheses and hit enter and you can see I got that total from the other file using
this named range and again I want you to pay attention to the fact that I did not have to
use the name of the sheet so even though the tab here is called sheet1 because that Apple
range name refers to a named range across the whole file we don't need the name of the
tab we simply just refer to the name itself called apple and it knows what it refers to
and that's how we can refer to named ranges from other files as well so as you can see
that works just fine no problem if you like my content make sure to like and comment
subscribe to the channel thanks for watching and I'll see in the next video