Use Named Ranges to Simplify your Formulas

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.

Name manager with some named ranges
Name Manager

The named range makes the formula easier to read and more flexible as the definition of LemonVariety can be adjusted without changing the formula.

Formula using a named range
Named Range in a 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

YouTube player

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.

Using the Name Box

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.

Name manager showing all named ranges
All the 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.

Editing the name of a range
Editing a Named Range

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.