# 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`.

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.

## 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
• 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.

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.

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.