How To Create A Named Range In Excel
– And Save Tons Of Time!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Most of the time, we use cell references in our formulas. And most of the time, that works well.

But Excel’s ability to name cells and ranges can save you huge amounts of time when you’re entering formulas.

And they can help you keep track of data that might otherwise be confusing.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s start with the basics.

What is a range in Excel?

We talk about ranges all the time, but what, exactly, is a range in Excel?

It’s simple: a range is a collection of two or more cells.

They could be vertical:

vertical-range

Or horizontal:

horizontal-range

Ranges can be rectangular, too:

rectangular-range

They can even include non-adjacent cells:

non-adjacent-range

Now that we have that down, we can start talking about names.

Kasper Langmann, Co-founder of Spreadsheeto

What is a name in Excel?

Before we get into the specifics of how to take advantage of Excel names, let’s talk about what they are.

The definition is simple: a name is a word or series of characters that’s applied to a cell or a range of cells.

Here’s a simple example.

names-display

In the image above, a group of cells is selected, and the Name box displays the name I previously assigned to that group (“Sales”).

The Name box is a useful tool—we’ll be using it quite a bit, so make sure you know where it is!

Kasper Langmann, Co-founder of Spreadsheeto

Note that the name only applies to the full group of cells. If I select a single cell within the group, the Name box displays the cell’s default name:

default-cell-name

Get your FREE exercise file

We’ll go through a few different cases where using names in Excel can be useful.

Grab the exercise file below and follow along! You’ll learn much faster.

Download the FREE Exercise File

Download exercise file
Download free exercise file

Naming cells and ranges

We’ll start with naming a cell or a range.

On the first sheet of our example workbook, you’ll see a column labeled “Tax” with a single value listed. Let’s name that value.

First, click on the cell you want to name:

select-cell

Then, click into the Name box and type the name you want the cell to have:

name-single-cell

We’ve called this cell “TaxValue” to help us remember what it is.

You may want to use shorter or more detailed names, depending on your spreadsheet.

Kasper Langmann, Co-founder of Spreadsheeto

Click to a different cell, then click back to B2.

You’ll see “TaxValue” appear in the Name box.

Naming a range is just as easy. Select the cells you want to name, and type a new name in the box.

Try it with the numbers in the “Total” column.

named-range

Now, as in the example above, we have a “Sales” range.

Kasper Langmann, Co-founder of Spreadsheeto

Another neat trick is to type the name of a known range into the Name box. It will take you directly to the cell or range specified by that name.

Try typing “Sales” into the Name box to see how it works.

Using names in formulas

Here’s where using names gets really useful.

Instead of inputting a cell or a range of cells into a formula, you can simply use a name. Here’s an example.

Click into an empty cell and type =SUM(Sales).

sum-name

Hit Enter, and you’ll get the total of the numbers in the “Sales” range:

sum-name-total

Wasn’t that faster than typing “=SUM(A2:A51)”?

Kasper Langmann, Co-founder of Spreadsheeto

You can use any name in any formula you want (as long as the cell or range is an acceptable argument in that formula). Let’s try another one.

I’ve added a column called “Final Tax.” We’ll use a simple multiplication to find 7% of each value in the “Total” column:

multiply-name

By hitting Enter and dragging the result down with the fill handle, we get the tax amount on each value in the “Total” column.

name-absolute-reference

You’ll notice here that names are absolute references, and not relative ones.

Kasper Langmann, Co-founder of Spreadsheeto

Try creating some other named cells and ranges and using them in formulas!

The Name Manager

If you’re working with a large spreadsheet, using names can save you a lot of time.

But it can also get very confusing.

What did you call that range? Which cells does this name refer to? It’s easy to forget.

That’s where the Name Manager comes in.

Kasper Langmann, Co-founder of Spreadsheeto

The Name Manager is exactly what it sounds like: a central place for managing the named cells and ranges in your spreadsheet.

Let’s take a look at the Name Manager for the example spreadsheet.

Click to the Formula tab, then click Name Manager:

name-manager-button

You’ll see a simple pop-up window that has, in our case, two names listed.

name-manager

If you have more than a handful of names, you can find them all right here. And that’s extremely convenient.

This is more valuable than it seems at first.

It’s easy to forget names and ranges, because you have to select exactly the right group of cells to make it appear in the Name box.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s select the first name, “Sales,” and click Edit.

name-edit

This brings up a small window that lets you change the name and referent cells of any named range.

For example, we could rename our range to “SalesTotal”.

And if we added more sales totals, we could change the Refers to field to include them by editing the range.

You can also save yourself a click by editing the range directly from the Name Manager main window:

name-manager-edit

The Name Manager also lets you create new ranges and delete ones you don’t need anymore.

You’ll notice here that names are absolute references, and not relative ones.

Kasper Langmann, Co-founder of Spreadsheeto

Speed up your work in Excel

If you find yourself clicking around to find specific cells or ranges regularly, names can save you a lot of time.

Just name your most commonly used cells and ranges, use the Name Manager to keep track of them, and start using them in formulas.

It might seem like a lot of work at first, but it’s absolutely worth it!

Kasper Langmann, Co-founder of Spreadsheeto
2018-12-14T16:47:52+00:00