# 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.

## 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:

Or horizontal:

Ranges can be rectangular, too:

They can even include non-adjacent cells:

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

## 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.

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!

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:

## 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.

## Naming cells and ranges

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:

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

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.

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.

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

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).

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

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

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:

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

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

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.

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:

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

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.

Let’s select the first name, “Sales,” and click 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:

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.

## 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!

2018-12-14T16:47:52+00:00