In this session we're going to look at name ranges but, why bother with name ranges. Well quite simply names are more meaningful than grid references. What is a name range a very quick example in this table to highlight the first names .Type in the name box first underscore name. This is because excel doesn't like spaces and name ranges and then enter so we now have a name range called first name.
Advantages of name range
Wherever we are on the spreadsheet, we can look up here at first name and go to that range so the first. Advantage of name ranges is navigation. It simplifies and speeds up navigation. It also makes formulas easier to write easy to interpret and easier to edit. Now shows a zooming tool which provides another important benefit of name ranges.
We can also use name ranges to hide things. We can actually put something in the name range and that name range is off the worksheet. Finally as we learn more about excel and move on to some of the more advanced topics name ranges, will crop up time and again.
the disadvantages of name ranges
So, what are the disadvantages of name ranges well at first. If you don't use them often, they can seem just a little bit fiddly. The second disadvantage of name ranges is, they don't lend themselves as well to copying of relative references, although there are some workarounds for this when we are creating name ranges there are a number of rules for what you can put into a name .
Names cannot contain spaces and most punctuation characters they need to begin with a letter or an underscore or a backslash name. The case insensitive and you cannot name ranges like cell references so for example a13 would be forbidden. NO IMAGE
So what are the various ways that ,we can create name ranges .If we want to create a range for each of several columns with common characteristics. To highlight all of these cells with the data including the titles and going to the formulas tab, which is where we'll be working with name ranges go down to define names this is where all the ribbon shortcuts exist. NO IMAGE
If we go to create from selection and you'll see that there's a shortcut for this as well. Create names from values in this case we want to use the top row for the names and the top row will not actually be included in the range. If we then click ok and if we now look down here we will see that we have four new ranges and we could go to any one of these ranges.
If we want to get some more information on the ranges that we've created or if we want to change them go to name manager. We could then select q1 sales here .We can edit the name and we can edit the range and if we want to we can put some comments in .So what else can we do with these ribbon shortcuts well next we can define a name.
If we click on define name it will pick up the range that we have selected. We could manually add a name to that range .So now type in q2 version 2. So now we have a q2 version 2 as well as q2 sales ,so range names can overlap each other and now to talk about the advantages in a bit more detail to bring out some more of the functionality around name ranges navigation. we have seen up here in the name box but we could also use the shortcut key f5 and as well as ranges that we have been to directly. This will also list out the named ranges.
How To Create Name Range In Excel uSE OF Navigation
So we can use this to navigate to any particular range something we'll look at in a bit more detail further on is hyperlinks. We can also use name ranges in hyperlinks. So, if i just hover over this cell which is called hyperlink. We can see down here the word navigate. Navigate is one of the name ranges which we set up earlier. so, if i click on hyperlink we will go to navigate and we have landed here in cell c6.
Now we have added to this cell. Go back a name range called hypertest. So, to click on go back and we go back to hyperlink. So, we can use name ranges to help us greatly with navigating around workbooks. This becomes invaluable. As our workbooks become larger and more complex using names in formula instead of ranges does have some real advantages .
To type a sum formula, here equals sum and this time instead of typing in the range .To use the f3 key and this will bring up available ranges and now to click on q1 sales and then okay close brackets and there we have a sum of q1 cells. This is much more meaningful than just typing in the range the disadvantage.
standard sum function using a normal range
If i try and copy this across ,it's treated as an absolute reference. So it's not working for q2 sales with the cell highlighted .If we click on insert function. We can see the argument another way ,as we know that can be useful for editing functions .Next type in a standard sum function using a normal range. Type the range references in directly and return .If we were to set up formula across our spreadsheet like this and we wanted to change .
How To Create Name Range In Excel ? Those formula relatively quickly from standard ranges to name ranges. What we could do is go up to define name in the ribbon and apply names. We can then choose the one that, we want to apply it to and click ok .Now to show you this again on a cleaner sheet .Firstly to create name ranges create from selection.
How To Create Name Range In Excel create a formula to sum up the cells
Next to create a formula to sum up the cells above typing this in and using control enter. To enter it across the range so we have a range of conventional formula. Now as we can see, if we want to quickly change this from a standard range into a named range. If we go to define range we have got the option to apply names. Now excel is going to take a guess at the names it wants us to use and it's taking a good guess here. So, i'm now click ok and here. We have correctly summed up each of these ranges. If you're working with physically large tables in excel and you want to apply names to them.
using the zoom slider
Either using the zoom slider or going up to view and typing zoom. If we change the custom selection to 39 or less then, we will be able to see the range at a glance quite hard to see. So, zoom in but you can see the ranges are named so, this can be incredibly useful with larger worksheets but you'll see that. If we zoom out here to 40 even we lose that so just to wrap up.
How To Create Name Range In Excel SHORTCUTS
There are lots of advantages to using name ranges they can be a little bit fiddly to start with but once you get the hang of them they're incredibly powerful and will make your life easier and save you quite a bit of time thank you