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.

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.

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.

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.

 

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

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

Click onto any other cell to deselect the range.

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

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.

• Press Tab to accept the range or select it with your mouse.

2. The end formula looks something like this:

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

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.

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

 

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