Do you need to name a range in Excel?

Do you keep referencing the same cell ranges over and over again in your workbook? Excel provides you with something called a named range which is a superior way of referencing a cell range.

If you have sales data in cells C3:C14 that you’re constantly referring to, you could create a named range called MonthlySales and use it in place of the actual cell range C3:C14.

This post is going to show you all the ways you can add a name to your ranges in Excel.

Why Use Named Ranges?

A named range will make your Excel calculations more understandable to both yourself and others.

A formula containing a named range such as =SUM(MonthlySales) gives you a better indication of the calculation compared to =SUM(C3:C14).

A named range is also much easier to update if the underlying cell range changes. If your MonthlySales named range needs to point to cells D3:D14 instead of C3:C14, you only have to update the named range in one place. You wouldn’t need to update the formulas or code!

What Names Can be Used?

There are a few rules when naming a range.

• Your name can’t exceed 255 characters.
• Names must begin with a letter, underscore _, or backslash \.
• Avoid special characters other than underscores _ and periods ..
• You can’t use a space in the name. Join the words together and capitalize their first letters instead.
• You can’t use a name that could also be a cell reference. The name Hi5 could also refer to cell HI5 so you can’t use names like that.
• Avoid creating a name that’s already being used. Named ranges having a worksheet scope can have the same name but this can be confusing.
• Names are not case sensitive, so the names myName and MYNAME are considered the same by Excel.
• Certain names such as R and C are reserved as shortcuts to select a row or column.

Name a Range from the Formulas Tab

You can create a named range by going through the Excel ribbon.

Suppose you’d like to create a named range for all of your monthly sales:

1. Select all the cells you’d like to include in your named range.

2. Go to the Formulas ribbon tab and select Define Name.

3. Type a descriptive name for your named range in the Name field.

Leave Scope as Workbook so that it will be available on every sheet.

Leave an optional description in the Comment field.

The Refers to field should already be pre-filled with your selected cells. You have the option to highlight the Refers to contents and select a different cell range.

4. Click OK to return to your sheet.

You now have a named range representing your selected cells!

Name a Range from the Namebox

Another easy way to name a range is with the name box. This is the area on the left side just below the ribbon.

This will usually display the name of the selected cell or range, but it can also be used to name cells and ranges.

Here is how to use the namebox to name a range.

1. Select the cell or range you want to name.
2. Type the desired name into the namebox.
3. Press the Enter key to create the named range.

Simple and effective. This will likely become your go-to method for naming ranges.

Name a Range from the Right Click Menu

You can bypass the Excel ribbon and create a named range through the right click menu:

1. Select the cells to be included in your named range.

2. Right-click and select Define Name…
3. Define your named range as described in the Formulas Tab section of this article.

Name a Range from the Name Manager

The Name Manager is the centralized location of all your named ranges.

To create a named range through the Name Manager:

1. Go to the Formulas ribbon tab and select Name Manager.

2. Click New….
3. Define your named range as described in the Formulas Tab section of this article.

4. See how your new named range now appears in the Name Manager list!
5. Click Close to return to your sheet.

Name Multiple Ranges with Create from Selection

Creating named ranges is particularly quick if your data is arranged like a table with column headers.

To create a named range for each of your columns:

1. Select all of your data and ensure you include your column headers.

2. Go to the Formulas ribbon tab and select Create from Selection.

3. Check only Top Row to use your column headers as the names of your named ranges.
4. Click OK when done.

You now have a named range for each column of data!

Name a Range from a Keyboard Shortcut

Excel provides two keyboard shortcut options to help you create named ranges:

• Press Ctrl + F3 from your sheet as a quick way to open the Name Manager.
• Press Ctrl + Shift + F3 from your sheet and Create from Selection will open without any navigation required.

Name a Range with VBA

VBA includes language that allows you to create a named range in a macro:

ThisWorkbook.Names.Add Name:="MonthlySales", RefersTo:=Range("SalesSheet!C3:C14")

The above VBA code creates a named range called MonthlySales pointing to cells C3:C14 on the SalesSheet sheet.

Name a Range with Office Scripts

Office Scripts is the latest automation language available within Microsoft Excel.

You can use Office Scripts to automate the creation of a named range:

workbook.addNamedItem("MonthlySales","=SalesSheet!C3:C14");

The above script creates a named range called MonthlySales pointing to cells C3:C14 on the SalesSheet sheet.

How to Find All Ranges

Once you have your named ranges created, here are 5 different ways to find them:

Name Box

The Name Box in the upper-left corner of the sheet contains all named ranges available to the active sheet:

Select a named range from the Name Box dropdown and you’ll be taken directly to its cell range.

Name Manager

The Name Manager is the most comprehensive list of named ranges within the workbook:

Press Ctrl + F3 to open the Name Manager and view all named ranges.

Intellisense

Named ranges within the scope of the active sheet appear in Intellisense:

Start typing in the formula bar and you’ll be presented with a list of relevant named ranges.

💡 Tip: Intellisense will show the named ranges in alphabetical order. Prefixing all your named ranges with the same value such as rng will group all named ranges together and make them easier to find. For example, use rngMonthlySales instead of MonthlySales.

Navigation Pane

The Navigation Pane is a great way to visualize the layout of your named ranges:

1. Go to the View ribbon tab and select Navigation.

2. The named ranges appear within the sheet they reference.

Zoom Out on Sheet

If you zoom out to less than 40% on a sheet, Excel will show your named ranges directly on the range they’re referring to:

1. Go to the bottom-right corner of your sheet and move the zoom slider to the left until it is less than 40%.

2. Your named range appears over its cell range.

Conclusions

This article touched on 7 different methods for creating named ranges in Excel.

Use these methods to create named ranges for any cells you think you’ll be referencing over and over again.

You and your users will benefit from having a workbook that is easy to understand.

Maintenance will also be easier given all of the options presented to help you find your named ranges.