Named ranges are one of these crusty old features in Excel that few users understand.

But named ranges are actually a pretty cool feature.

They can make formulas a lot easier to create, read, and maintain.

Simple named range called “data”

And as a bonus, they make formulas easier to reuse (more portable).

In fact, I use named ranges all the time when testing and prototyping formulas.

They help me get formulas working faster.

Simple named range called “data”

A named range is just a human-readable name for a range of cells in Excel.

after you snag a named range, just use it just like a cell reference.

Just select a range of cells, and bang out a name into the name box.

Simple named range called “data”

Excel will opt for range on the worksheet.

pick the name you want to work with, then change the reference directly (i.e.

edit “refers to”), or hit the button at right and select a new range.

Simple named range called “data”

There’s no need to opt for Edit button to update a reference.

When you click Close, the range name will be updated.

If you know a way to do this, chime in below!

Simple named range called “data”

Whenever you change the date in “updated”, the message will update wherever the formula is used.

Seethis pagefor more examples.

Named ranges are absolute by default

By default, named ranges behave like absolute references.

Create a named range fast with name box

As a result, relative named ranges are useful building generic formulas that work wherever they are moved.

Important: ensure the active cell is at the correct location before creating the name.

Start by selecting the cells that contain formulas you want to update.

Create names from selection command on ribbon

Then run Formulas > Define Names > Apply Names.

Excel will then replace references that have a corresponding named range with the name itself.

This is because they replace cryptic references with meaningful names.

Create names from selection with data and labels selected

For example, consider this worksheet with data on planets in our solar system.

This is a great way to quickly get a formula working.

As long as “data” contains numeric values, the formula will work straightway.

New names also appear in the name box drop down menu

This stops Excel from creating names on-the-fly and lets you to fully control the name creation process.

internet tool, text editor, etc.).

Named ranges can be used for navigation

Named ranges are great for quick navigation.

Updated named ranges with the Name Manager

Just opt for dropdown menu next to the name box, and choose a name.

When you release the mouse, the range will be selected.

When a named range exists on another sheet, you’ll be taken to that sheet automatically.

The name manager shows all newly created names

Named ranges make hyperlinks easy.

However, you might define a name equal to a table (i.e.

=Table1) and hyperlink to that.

Paste names dialog box

A range set up this way is referred to as a “dynamic named range”.

There are two ways to make a range dynamic: formulas and tables.

Table1), but you’re free to rename the table as you like.

After pasting named ranges into worksheet

when you land created a table, it will expand automatically when data is added.

The video below runs through this approach in detail.

Otherwise, you’ll see #NAME?

At zoom level <40%, Excel will show range names

errors in formulas that still refer to deleted names.

For example, the scope for a local name might be “Sheet2”.

For example, you might use a global named range a tax rate assumption used in several worksheets.

Using a named range inside a text formula

Local scope

Local scope means a name is works only on the sheet it was created on.

This might allow you to reuse the same formulas in different sheets.

For example, you could name a cell “last_update”, enter a date in the cell.

Named ranges appear when entering formulas

Then you might use the formula below to display the date last updated in any worksheet.

Global names must be unique within a workbook.

Locally scoped named ranges make sense for worksheets that use named ranges for local assumptions only.

Named ranges can work like constants, with no cell reference

However, as a workaround, you could delete and recreate a name with the desired scope.

You candownload the Name Manager utility here.

Using a named range like a constant in a formula

Standard formula with absolute address

Using a named range like a constant in a formula

The Apply Names dialog box

Applying names ranges with find and replace

Without named ranges formulas can be cryptic

With named ranges, formulas can be simple

Named ranges allow for simple navigation

Creating a hyperlink to a named range

Example of named range hyperlink on the worksheet

Using a named range for data validation with list

Data validation with named range example

Creating am Excel Table

Tables will expand automatically and can be renamed

Name Manager filter menu

Simple named range called “data”

Create a named range fast with name box

New names also appear in the name box drop down menu

Updated named ranges with the Name Manager

The name manager shows all newly created names

After pasting named ranges into worksheet

Using a named range inside a text formula

Named ranges can work like constants, with no cell reference

Using a named range like a constant in a formula

Standard formula with absolute address

Using a named range like a constant in a formula

Without named ranges formulas can be cryptic

With named ranges, formulas can be simple

Named ranges allow for simple navigation

Creating a hyperlink to a named range

Example of named range hyperlink on the worksheet

Data validation with named range example

Creating am Excel Table

Tables will expand automatically and can be renamed