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
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.
Video Tutorial on Named Ranges
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
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.
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.