In this session, we’re going to look at how to create a named range in excel but, why bother with name ranges. Well, quite simply, names are more meaningful than grid references. You can type in the name box with the underscore name. This is because excel doesn’t like spaces and name ranges.
Why Create a Named Range in Excel?
Wherever we are on the spreadsheet, we can look up here at the 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 it shows a zooming tool that provides another important benefit of name ranges.
We can also use named 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 uptime.
Why Not Create a Named Range in Excel?
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 that they don’t lend themselves to copying relative references. However, there are some workarounds for this when we are creating name ranges. There are several rules for what you can put into a name.
For Example
Names cannot contain spaces, and most punctuation characters need to begin with a letter or an underscore or a backslash name.
So, what are the various ways that we can create name ranges? If we want to make a range for each of several columns with common characteristics to highlight all of these cells with the data, including the titles and formula tabs, this is where all the ribbon shortcuts exist.
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 the case of using the top row for the names, and the top row will not actually be included in the range. If we then click ok and look down, we will see that four new ranges exist, 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. We can edit the name, range and we can even put some comments in. So, what else can we do with these ribbon shortcuts?
If we click on the defined 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. Now, we have a q2 version 2 as well as q2 sales so that range names can overlap each other.
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 in the name box. We could also use the shortcut key f5 as well as ranges that we have been to directly. This will also list out the named ranges.
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 hyperlinks. We can also use named ranges in hyperlinks. So, if you just hover over this cell which is called a hyperlink. We can see the word navigate. Navigate is one of the name ranges which we set up earlier. So, if I click on a hyperlink, we will go to navigate, and we have landed in cell c6.
Now we have added to this cell. Go back to a named range called hypertext. So, click on go back, and we will go back to the hyperlink. We can use named ranges to help us greatly with navigating around workbooks. This becomes invaluable as our workbooks become larger and more complex; using names in the formula instead of ranges does have some real advantages.
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 the insert function, we can see the argument another way, which can be helpful for editing functions. Next, we need to type in a standard sum function by using a normal range. Type the range references directly and go back.
Those formulas are relatively quick from standard ranges to name ranges. What we could do is go up to define names in the ribbon and apply names. We can then choose the one that we want to use it to and click ok.
Create A Formula To Sum Up The Cells
Next, we need to create a formula, to sum up, the cells above, typing this in and using the control center. To enter it across the range, so we have a range of conventional formulas. 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 the range, we have got the option to apply names.
Now excel is going to guess the names. It wants us to use, and it’s taking a good guess here. So, I will now click ok, and 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
We can either use 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 to 40, even we lose that, so just to wrap up.
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!
