How to Create Range Names

Naming your ranges in Excel has many benefits, not only for engineers but for everyone. But what is a Range Name? How do I name a range? How do I use it?

What is a Range?

Let’s start with ranges. What is a “Range” in Excel?

It’s just a group of cells. You’ve been using it for as long as you have used Excel, even if you never called it by its name.

When you use a formula, like =SUM(A1:A20), then the cell address “A1:A20” refers to a range.

A range can be a single cell: A1 or A1:A1 is also a range.

A range can also consist of several groups of cells, which do not have to be next to each other. “A1:A8,C1:C8” is also a range. We can use =SUM(A1:A8,C1:C8).

What are Range Names?

Now let’s talk about Range Names in Excel. We can give any range a name, and then use that name anywhere we could use a cell or range address.

For example, if we name the range A1:A8 as “Data1” then instead of =SUM(A1:A8) we can use =SUM(Data1).

If we then name the range C1:C8 as “Data2” then instead of =SUM(A1:A8, C1:C8) we can use =SUM(Data1, Data2).

However, if we name the range A1:A8,C1:C8 as “All_Data” then instead of =SUM(A1:A8,C1:C8) we can use =SUM(All_Data).

How do I Name a Range?

There are several ways:

A. The simplest:

1. Select (drag over) the range of cells you want to name.
2. In the Name Box (on the left of the Formula Bar), type the range name you want to use.
3. Press Enter.

To check: Click somewhere else in the worksheet. Click on the drop-down arrow at the right of the Name Box. Then click on the range name you just created. The range will be selected again.

With this method, you cannot make the name refer to a different range. If you select a different range, type “Data1” in the Name Box again, and press Enter, Excel will not name the new range as Data1. Instead, it will select the existing Data1 range.

B. Using the Formulas Ribbon:

In the Formulas ribbon, the second group, “Defined Names” is where we work with Range Names.

• Define Name lets you type in a name for the selected cell(s). If there is text in a cell above or left of the selection, Excel will offer that as a suggestion for the name.
• The Define Name dropdown has an extra item, Apply Names. This lets you put new range names into formulas you created before the range names.
• By contrast, Use in Formula helps you put range names into formulas as you create the formulas, using already-created range names.
• The bottom item on the Use in Formula dropdown, Paste Names, creates a range name table. This is documentation of the names and the ranges named. It is a static table: It does not update if the ranges or names change. Do it after all changes are done.
• Create From Selection is a massively useful feature to create range names from column or row headings. Select the range and the headings before choosing Create From Selection.
• Finally, Name Manager (Ctrl+F3) is your all-in-one toolkit. It lets you see all your named ranges (or filter for just some), create new ones, delete any, and edit (change the name and/or the range referenced).

Name Manager has a column named “Scope”. By default, a range name has the scope of “Workbook”. This means that you can refer to that range from any worksheet in the workbook. You can also have ranges with the scope of a particular sheet. This can happen when you copy a sheet that contains range names. The copy will have the same range names, but with a scope of that sheet only. Be careful! On that sheet, references to such range names will refer to the copies on that sheet, not to the ranges on the original sheet.

When you use Define Name to create a range name, you can explicitly define its scope.

What can I Name my Ranges?

A Range Name must start with a letter or underscore. It can contain letters, numbers, underscores, and a few special characters (best avoided).

A Range Name is not allowed if it:

• is a cell reference, such as A1, B123, or XFD2001, or
• has spaces
• has punctuation (comma, colon, semicolon –but full stop is allowed)
• contains mathematical symbols (-, /, *, +)
• has brackets [], parentheses (), or braces { }, or
• has the same name and scope as another range in the workbook.

Range Names are case-insensitive: Data1, data1 and dATA1 all refer to the same range. Excel will respect the case you used when you created the range.