Use formulas and functions

Name cells and ranges

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

In Excel, you can name cells and cell ranges, and use those names in formulas. This is a useful feature that makes the formulas easier to understand and maintain.

Name a cell or cell range
  1. Select a cell or a cell range.

  2. In the Name Box, type a name.
    Name Box
    You can click the drop-down arrow to see the list of existing range names, if any.
    Names:

    • Cannot begin with a number.

    • Cannot contain spaces.

    • Can contain Upper and lowercase letters.

    • Can include underscores.

Press F3 to use the name
  1. Select a cell.

  2. Press the F3 function key.

  3. In the Paste Name box, select the name, and then select OK.

Use names in formulas
  1. Select a cell and enter a formula.

  2. Place the cursor where you want to use the name in that formula.

  3. Type the first letter of the name, and select the name from the list that appears.

  4. Press Enter.

Delete a name
  1. Select Formulas > Name Manager.

  2. Or, press Ctrl + F3.

    Note:  With Name Manager, you can create New names, Edit names, or Delete names.

  3. Select the name, and then select Delete.

    Note: If you delete a name that is used in formulas on your worksheet, it will create errors in your worksheet.

  4. Select Close.

View and paste all names into your workbook
  1. Select an empty cell.

  2. Select Formulas > Use in Formula.
    Defined Names

  3. Select Paste Names from the drop-down.
    This pastes the names, sheet name, and cells in that range, into your workbook.

Want more?

Discover more courses like this at LinkedIn Learning

Define and use names in formulas

Use structured references in Excel table formulas

Overview of formulas in Excel

Create or change a cell reference

I'm about to write a formula in cell I2, to calculate a new salary, and everybody in the list here, moving down column I is going to get a two point nine one percent increase.

So that's in cell K1. All these cells will be referring to that, and I'll start off with just a simple formula.

We could do this in two or three different ways certainly, one way to write this formula, equal G2 times K1.

We want this to be an absolute address, so I will press the Function Key F4, and then plus G2.

We have our answer, we can copy it down the column. However, what if we were to name that cell?

Then we could use the name in the formula, it's going to be a little bit clearer, and the cell, by the way, if it's farther away, possibly this cell might be moved off to the right or maybe it's way off to the right to begin with.

Having a name here that makes some sense, we don't have to worry as much about the address then. So, how do we name a cell?

Select the cell we want to give a name too, and to the left of the Formula bar, we've got a box called the Name Box.

Click the drop arrow.

Any of the range names, if they exist, will be displayed there. Range names cannot begin with a number, they cannot contain spaces.

I'm going to call this PercentIncrease, I used a capital I there, so we can certainly switch between upper and lower case, to clarify PercentIncrease.

If I want to use this name in the formula, I can highlight this data, and go over instead and click on cell K1, now it has a name.

That's one way to pop it into place. Another way is to use the function key F3.

This will give us a list of range names, right now we have only one.

We can click that, double-click it, or click that and click OK, either way.

That also pops it into place. A third way is simply type the first letter of the name, P, we will see other function names but we will see range names that begin with the letter P, and there it is right there, we could tab it into place. So, different ways to get this in there.

Enter, and that by its very nature is an absolute address, so we can copy this down the column, we don't have to worry about the absolute addressing issue.

Everyone of these is referring to cell K2 by way of its name, PercentIncrease.

Here's another list off here to the right. I might want to give this a name. This is a range of cells.

I want to highlight all of these, and I am going to call these First Half Sales, once again, after the data has been highlighted we'll go to the left of the Formula Bar, into the Name Box, click the drop arrow, we see the existing range name.

There is only one right now, but I am going to type in FirstHalfSales.

You can use underscore here to separate words, I could have used that.

I am simply shifting between lower and upper case, Enter.

So, I am about to write a total here, maybe I'll do an AutoSum.

I'll press Alt-equal, and ignore what I am saying, and I'll type for example the letter F, that's one way to get to here, and we see FirstHalfSales on the list right there, and press Tab, we're all done, and we've got a total.

If I were doing this at a different time, or even now of course I can do it, average, maybe I forgot the range name.

I'll press F3 to see the range names. There it is, FirstHalfSales, looks good. Double-click, it's to there, Enter, and we see it that way.

And of course I could also, although not any better, after starting AutoSum with Alt equal, I could highlight the cells, and the range name pops in.

So, all of these different methods work. Range names are good in many respects.

There's a down side to them.

I got a Worksheet once from someone who was having some issues, some problems with the Worksheet, and as I began to look at different formulas I would see range names everywhere.

As it turns out, the Worksheet had about 100 range names in it, and I was forever trying to figure out what each range name meant.

Now you do have a couple of options here.

If you go the the Formulas tab and go to the Name Manager button, and you can also get there by way of Control + F3, you will get a list of range names, and by the way this is where you come when you want to delete a range name.

If I don't want to use PercentIcrease anymore, I click it and then press Delete.

Now, I've got formulas over in column I that use that, and they're all going to be errors if I do this right now.

So, I'm not going to do that there, although this is where we do come to get rid of names that we no longer need. You also have the option here of displaying range names.

Suppose I had five or ten or 20 range names here, and maybe I didn't create them, or even if I did and spent a long time forgetting which ones are which, I might click in an empty part of the Worksheet, and go to the button Use In Formula on the Formulas tab.

Click here, and Paste Names, and paste a list right there, and that pastes all the range names.

We probably want to make the columns whiter and so on.

This lists all the range names, and the Worksheet that I am currently on is called Use Range Names, so we see that there and it looks a little odd maybe at first, but it shows us the Sheet name and the location of the cells in question.

So, this potentially is a large list depending upon how many range names you might have used.

It's a viable tool to be sure.

Creating names for cells, or ranges of cells as we've seen in the examples in this Worksheet.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×