Excel: The Name Box and Named Ranges

The Name Box

Most of us don’t give a lot of thought to the humble Name Box in Excel. We glance up at it occasionally to see which cell we have selected, but few people realize that there is a lot more than meets the eye with this feature.

Excel Name Box

Did you know you that this little box allows you to name a cell or range of cells? I could even rename a cell after myself.

Name in Name Box

You will be shocked to hear how easy this is… all I did was type over the cell name in the name box and suddenly “A1” became “Ali”.

Exercise Download

Let’s see how this works with a range of cells. For this, follow along with this document: NamedRangesExercise .

This data will look familiar to those of you who have taken Excel Essentials. It is a list of fictional employees, departments and salaries.

…By the way, you might find it useful to resize the name box for today’s exercise. This can easily be done by hovering your mouse to the right of the name box until you see a double headed arrow. Then simply click and drag.

Double headed arrow to resize name box

Named Ranges

I would like to name the Salary data so I can easily find and select it.

  1. Select Cells E1 through E22
    • Remember: to select a cell in Excel you hover your mouse over the first cell, and you are looking for your cursor to turn into a white cross for Excel to select it. Then click, hold down the mouse and drag down to the bottom of the list.

Selected Salary column data

 

  1. In the Name Box click over the existing cell name (in this case, E1),

Name Box

  1. Then replace it with the word Salary and press enter.

Name Box Renamed

Click onto any other cell to deselect the range.

  1. Notice there is now a dropdown that appears in the name box now with your newly created Salary moniker.
    • Test it out! Select it from the dropdown… it should select your entire range.

Name Box Dropdown

Named ranges allow users to quickly find and select a range of cells. Guess what else named ranges help you do? Formulas!

Formulas with Named Ranges

I would like to quickly figure out a 10% bonus for my employees based on the Salary range.

  1.  In F2, type = Salary*.1
  • Before you even finish typing salary, you will probably see something like this.

Formula with named range

  • Press Tab to accept the range or select it with your mouse.
  1. The end formula looks something like this:

Formula complete

  1. Then use AutoFill to take that formula down to the bottom of the list.
    • Remember the trick we talked about in Advanced Formulas for a quick AutoFill? Hover your cursor in the lower right until you see a black plus sign, then double click to carry the formula down.

Auto Fill

Managing Named Ranges

Inevitably, someday you will want to manage your named ranges. They live in the Formulas tab, in a button titled Name Manager.

Name Manager

Select Name Manager to see your full list of named ranges, alter, or delete them.

Name Manager Menu

 

Have you thought of a way you will use Named Ranges? Tell me about it!