all right so in this video i want to go over some easy ways to navigate through
a spreadsheet primarily i'm going to be talking about this name box which is
currently a new feature if you're familiar with excel name box it's pretty much the same thing
so the first thing if you want to navigate to an excel cell and you have a larger spreadsheet
the name box will let you navigate to it really easy all you have to do just click in this box
type the cell where you want to go to so let's say i want to navigate through b 1000 cell so i
just type b1000 right in this box hit enter and that should see take me to that particular cell
in my spreadsheet right away and that's pretty much how this thing works so you can navigate to
different cells you can also highlight a whole range if you wanted to so let's say i wanted to
select this g column from the second row up to this row 1000 right so all i have to do just
go here and type g2 colon g1000 hit enter and that should highlight that entire range
just to give you a smaller example so you can actually visually see what's going on here
if i go here and type g4 colon g15 something like that hit enter see from g4 through g15 that area
basically gets highlighted you can also use this to navigate to different worksheets in a similar
fashion so let's say i had another worksheet over here this is called sheet5 then if i wanted to go
to that worksheet to a particular cell i could just go to this name box do something like sheet
5 which is the name of that other worksheet in this particular case and then do exclamation sign
and then follow it by the actual cell where i want to go to so let's say b7 so that's sheet
5 exclamation b7 if i hit enter c that takes me to b7 cell in worksheet that's called sheet 5.
you can also use this feature to define named ranges if you're familiar with named ranges
in general you probably know them when you highlight of range right click maybe
and then here see define named range that used to be the way to do this now we can do that
a little faster so if i want to give a particular range a particular name i just highlight
the area go to this box see currently it says g2 through g18 which is our highlighted area just go
in the box and give this a name so i'm going to call this sales or something so this is going to
be the name so if i'd enter that will now create a new named range called sales which means if i
wanted to sum these numbers i should be able to do equal sum open parenthesis and then type the range
name sales close parenthesis and hit enter and that should sum up see it highlights those cells
in the background those basically cells just like that you should also be able to navigate to that
named range by simply typing the name right here so if i type sales in a box hit enter that should
see highlight the named range and that should also happen if i'm in a different worksheet right here
and go here and type sales hit enter see it brings me to this worksheet to that particular named
range selected all those defined names so let's just do another one i'm going to call this wraps
all these names that we define are gonna leave in this named range box so if i open this drop down
see those names are automatically appearing here this is wraps this is sales and it kind
of shows where the range points to and these are our other worksheets sheet six sheet seven
so again if i click on reps it will highlight reps if i go click on sales it would highlight
sales pretty much like that one other thing that's probably worth to mention if i decide to
change the sales range right now it goes from g2 through g18 so i'm gonna highlight
something longer than that and go here and type sales again hit enter you'll see that it jumps
back to the previous one if i go check sales it's still the old range so you cannot override an
existing name if you already have one so what you have to do in those cases just change the
actual range so you could just go in here in the drop down click on this manage named ranges and
that should pop up this now you could say okay this should go until not 18 but let's say 23.
that's actually not the correct one so it says the named range i'm going to cancel that one
and go to the one that we actually want to modify this is it and this is where i would do
23 just like that done so now that should be modified if i go here and open sales see it will
highlight the new range you can also go to the same area again by going to this drop down manage
named ranges and then in this area you could also delete this thing so if i don't want to have
this anymore i can just click on this click on this little delete icon remove delete icon remove
and that should get rid of those named ranges from here as you can see they're gone so finally
i'm gonna talk about a few shortcuts also to navigate through a data set like this and that
would be some control or command shortcuts depending if you're on a mac or not a mac
so if i'm in this cell on top and i click control or if you're on a mac everything i say control is
going to be command so if i press that and do down arrow key on my keyboard see jumps all the
way down to the bottom of this particular range of cells if i do command up or control up i get
back to the top of this and then i can do again control or command right that goes to this cell
to the right and if i go left and down and down while i have command pressed you'll see it went
all the way down to the last row and last cell in that a column command up we're back to our
worksheet and command up we're back to the top so what this is actually doing it finds the next
blank or non-blank cell depending where you are located at that point so to give you an example if
i have some empty cells right in here if i go to the top here and do command down or control down
it goes until it hits a blank cell and it stops and then the next time if i do this because the
next cell is blank it's going to keep going until it finds something that's not blank and it's going
to stop so if i do control down or command down again you see it goes to this cell and it stops
and then the next time it's gonna find a blank cell again and it's gonna stop see here we go and
then finally after this there's nothing else so it's gonna keep going until it hits the end of the
entire spreadsheet so again command up up up get back to the top you can also use the same trick
going right right to the last column left left we're back to this particular area if you combine
this command up down left right with shift you can also select the cells as you're jumping so if i'm
in this top cell i do ctrl shift or command shift down see it highlights until the blank
i do it again it goes until the next non-blank cell and then again it goes until the next blank
and it's pretty much going to stop just like that and then you could do ctrl shift write or
command shift right to select this entire area so i'm gonna get back to the top command up left
to get back to the top here so again sometimes see because of this blanks because it's so much
tougher to select this now i only have just one blank so it's not a
big deal but if you have a lot of them this command shift down may not get you there
so this is where you can use this box right here on top so if i want to for example select this
d column i can see that the last row is 1 1650 so i can just go in here and do d2 d
1650 hit enter and that should just highlight that entire area so if i just check see it
starts from here and it goes all the way down to the bottom of this and we should be able to also
do open references here so if i do d2 colon d see what it does it goes from the
top and it basically finds where the last line in the spreadsheet is and it goes all the way down
and that's the name box thanks for watching please subscribe and i'll see the next video