Named ranges give an alternate name to a cell or range of cells. You can then use them in your formulas to make the formulas easier to read. As you can see in the image below, the range of Sheet1!F5:F1007
has been named LemonVariety
.
The named range makes the formula easier to read and more flexible as the definition of LemonVariety
can be adjusted without changing the formula.
Before we get too far along, let’s take a step back before naming the range to walk through the steps.
Follow along with your copy of the template with these examples.
Contents
Video Tutorial on Named Ranges
Requirements
Before deciding on a name, there are a few rules to consider.
- Named ranges can
- only have letters, numbers, and underscores
- Named ranges cannot
- start with a number
- have the boolean values of “true” or “false”
- contain punctuation or spaces
- be over 250 characters
- have the same syntax as a cell reference such as A1:C2 or R3C2:R4C5
Creating a Named Range
Name Box
There are two ways to create a named range. This first method is the quickest, mainly if you only use one or two ranges. First, highlight the range you want to name. Then, type the name into the name box. We’ll use the name Variety
for now, but we’ll improve the name after looking at it alongside the other named ranges in the next step.
Named Ranges Menu
Secondly, you can add, modify, or delete your ranges using the Named ranges menu. This menu shows all of the named ranges in the spreadsheet. You can find the menu by going to the Data menu then choosing Named ranges.
By looking at this example, you can see that the name Variety
is not descriptive enough. Is it the variety of oranges, lemons, or grapefruit? We will update the name to something more informative such as LemonVariety
. In this way, you have differentiated it from the other Variety
ranges in the spreadsheet.
Named Ranges in Formulas
Once you have created your named ranges, using them in a formula is relatively straightforward.
To count the varieties of lemons, you could use the formula =COUNTA(Sheet1!F5:F14)
. That formula works, but if change the formula to =COUNTA(LemonVariety)
isn’t easier to read? Hint: The answer is yes if you weren’t sure!
You can use named ranges in any formula that accepts a cell reference. Further, they act like fixed cell references. If you move a formula that contains them, the column letters and row numbers stay the same. As a result, the range called LemonVariety
acts like Sheet1!$F$5:$F$14
with the dollar signs to fix the references. However, you do not need to use the dollar signs when creating the named range.
Future-Proofing your Named Ranges
We’ve made a lot of progress so far. Your formulas are now easier to read. But let’s discuss how to make the ranges expandable. Making the ranges expandable will help them adapt to adding new data to your tables. For example, if you add a row below the named range LemonVariety
, making the data expand to row 15
, the named range of Sheet1!F5:F14
will not expand to Sheet1!F5:F15
. However, there is a workaround to deal with this.
When working inside formulas, you could use the range of F5:F
, intentionally dropping the ending row number. Dropping the ending row number would allow your range to extend to the bottom of the spreadsheet therefore including any new rows. However, named ranges will not accept this format. But, you can specify the entire column by dropping the beginning and ending row numbers. The name manager would accept F:F
. Just be careful when using this technique. A formula like COUNTA
would count the header in cell F4
as 1, but the SUM
formula would ignore the text and therefore sum correctly. See the video for more discussion on designating entire columns as ranges.
Conclusion
Now that you have learned to use named ranges, you can make your spreadsheet formulas easier to read and more resilient when adding new data.