Hi. In this Excel tutorial, we’ll go over how to make Named Ranges in Excel. So, creating named ranges in Excel can help you save
time in several ways, like letting you quickly jump to the named range, typing your named range in formulas instead of looking for and
and selecting cells, and creating hyperlinks in your spreadsheet to the Named Range.
Before we start, some important naming rules to keep in mind:
While you can use both capital and lower-case letters, these will be so that it’s easier for users read the name range. Excel can’t read whether
any letters are capitalized or not. While it’s possible to assign multiple names to the same range, you can’t duplicate the name. Each
name should be unique. You can’t use spaces in your names. You can’t use cell references.
You can only begin name ranges with an underscore, a backslash, or letters, except for R or C (which are also used by
Excel for Row and Column). You can use numbers or other symbols after, just not for the first symbol.
And you’re limited to 255 characters.
And, by the way, if you enjoy this tutorial, don’t forget to Like the video and subscribe to the channel for even more tips. The easiest way to
create a name a range is by selecting the data that you want in your named range and then coming up here, to this box to the left of the
Formula Bar. It's conveniently called the Name Box. And just type the name that you want to use. So, we'll call this Sales and click Enter.
And now, in the drop down, you see that we have a named range called Sales.
Let's undo that. You can also right-click and select Define Name.
Here, you'll notice we have an option for Scope. What this refers to is, essentially, where our Name Range can be easily recognized or
accessed from. So, if I were to set my Scope to Workbook, I can see and use my named range directly from other tabs in the workbook.
If I select Sheet, then I won’t be able to easily see the named range from Sheet2, for example. So,
let's do that. We'll do Sheet1 and we'll call this SalesSheet1 - so we can see the difference.
Select OK. And now, in our drop down here, we see SalesSheet1. If we come to Sheet2, that's no longer available to us.
So, let's undo this.
And, this time
we'll call it SalesWorkbook and leave this as Workbook. Select OK.
Now if I come to Sheet2, I can still use that to jump to my range. You can also create name ranges from the Formulas tab in the Defined
Name section. Click on Define Name, and it gives you the same options that we had when we right-clicked.
And you can create named ranges by selecting the Create From Selection. So, take a look - with my data already highlighted,
we have Top Row and Left Column already checked off. So, that's where my labels are and Excel detected this. If I click OK
and we go into Name Manager, you'll see that a named range has been created for each row and each column in my range.
Once your named ranges are set, you can come back to the Name Box and select a range. So, let's say we select February 2018,
and jump to that range.
You can also select Ctrl+G to go to a specific named range. To use named ranges in formulas, simply type your formula and,
instead of selecting the cells, type the name of your range.
You can also create a link referencing your range by - let's say we type Sales here - we can insert
a link to a "Place in this Document" and select any of these Defined Names to link to. So, let's say choose Emerson. Click OK.
Clicking our Sales will highlight Emerson's sales results. So, I hope you enjoyed this tutorial on how to make a Named Range
in Microsoft Excel. If you did, please make sure to Like the video and don’t forget to subscribe to this channel. Thanks!