How to assign a name to a range in Microsoft Excel

Are you constantly referring to the same cell ranges in your workbook? Excel offers a named range, which is an excellent way to refer to a range of cells.

If, for example, you have sales data in cells C3:C14 that you constantly refer to, you can create a named range called SalesMonth and use that instead of the C3:C14 cell range. In this article, we will cover all the ways to add a name to ranges in Excel.

Why use named ranges in Microsoft Excel?

A named range will make Excel calculations easier to understand for you and others. A formula that contains a named range, such as =SUMM(SalesMonth), makes the calculation easier to understand than =SUMM(C3:C14).

A formula that contains a named range, such as =SUMM(SalesMonth), makes the calculation easier to understand than =SUMM(C3:C14).

The named range is also much easier to update if the cell range changes. If the named range of SalesMonth should point to cells D3:D14 instead of C3:C14, you would only have to update the named range in one place. You won’t have to update any formulas or code!”

What names can be used?”

There are a few rules when naming a range:

• The name cannot exceed 255 characters;
• Names must begin with a letter, underscore _ or backslash \;
• Avoid special characters other than underscore _ and period ;
• No spaces in the name;
• Connect words together and capitalize their first letters instead;
• You cannot use a name that can also be a reference to a cell. The name Hi5 can also refer to cell HI5, so you cannot use such names;
• Avoid creating a name that is already in use. Named ranges that have a worksheet scope can have the same name, but this can be confusing.

.

Range Naming on the Formulas tab

You can create a named range using the Excel ribbon. Suppose you want to create a named range for all monthly sales:

• Select the cells you want to include in the named range;
• Go to the Formulas tab and select the Assign Name command;
• Enter a name for the range in the Name field;
• Leave the Area parameter as Book so that it is available on every sheet;
• Leave an optional description in the Note field;
• The Range field should already be pre-filled with the selected cells;
• You have the option to select a different range of cells. Click OK to return to the worksheet.

Now you have a named range representing the selected cells!

Naming a range from the right-click menu

You can create a named range using the right-click menu:

• Select the cells to be included in the named range;
• Right-click and select the command «Assign Name…»;
• Repeat all steps from the instructions above.

.

Naming a range from the name manager

The Name Manager — is the centralized location of all named ranges. To create a named range using the Name Manager:

• Go to the Formulas ribbon tab and select Name Manager;
• Click New…;
• Define the named range as described in the article section above;
• See how the new named range now appears in the Name Manager list;
• Click Close to return to the sheet;

Naming multiple ranges with Create from Selection

Creating named ranges is especially fast if the data is arranged as a table with column headers. To create a named range for each column, follow these steps:

• Select the data and make sure it includes column headers;
• Go to the Formula ribbon tab and select Create from Selection.Check the checkbox In the row above to use the column headers as the names of the named ranges;
• Then click OK.

Now you have a named range for each column of data!

Naming a range with a keyboard shortcut

Excel provides two keyboard shortcuts to help you create named ranges:

• Press Ctrl + F3 on a worksheet to quickly open the Name Manager;
• Press Ctrl + Shift + F3 on the sheet, and the «Create from Selection» window opens without having to navigate.

Use these methods to create named ranges for any cells you’ll be referencing over and over again. You and your users will benefit from the workbook being easy to understand. Searching will also become easier, given all the options presented to help you find named ranges.

.