Naming Cells: Discover the 3 Big Advantages of Using Named Cells in Excel Formulas

AUTHOR:  JASON MORRELL 

Jason Morrell

In this post you'll discover why naming cells or call ranges in Excel is so powerful. It adds a whole new level to your formulas and makes them easier to read and easier to maintain. There are lots of other reasons too.

You will see 2 methods to name cells and manage them. You can even apply your new cell names to existing formulas.

There's plenty to cover. Let's dive in.

1. Why should you name cells?

You can assign a name to a cell or a cell range. This introduces a number of benefits:

  • The names act as bookmarks, making navigation of a workbook easier and faster.
  • In a formula, a good name in place of a cell reference makes the formula easier to read, especially when the cell is on a different worksheet or workbook.
  • Named cells are absolute by default, which means they point to fixed cell.

2. Naming cells using the Name box

The Name Box is located to the left of the formula bar and above the column labels. It displays the currently selected cell or the first cell of a cell range.

To name the cell or cell range:

1.  Select the cell(s) to be named.

Naming a cell using the name box

2.  Click in the middle of the Name Box, not the drop-down arrow. The cell reference will now be highlighted.

3.  Overtype the cell reference with a name. Spaces are not allowed but underscores ( _ ) are.

4.  Press Enter to register the name. If you forget to press Enter, the name will not be registered, and you won’t be able to use it.

3. Naming cells using the Name Manager

To name a cell using the Name Manager:

1.  Click the Name Manager icon on the Formulas ribbon

The keyboard shortcut to display the Name Manager  is Ctrl F3.

In the Name Manager dialog, all existing named cells or ranges are listed together with their current values and what cell or cell range the name refers to.

2.  Click New.

3.  In the New Name dialog, type a name (remember, no spaces but underscores are allowed).

4.  Place the cursor in the Refers to box, then using the mouse, select the cell(s) to be named.

5.  Add a comment if you want. Comments are useful in reminding you what the named cell(s) are used for and where they fit in the big picture.

6.  Click Ok to create the Name.

Naming a cell using the Name Manager

4. How to create a constant

Named cell can be used as constants.

Constants are values that will never change such as the number of days in a year or mathematical PI.

To create a constant:

1.  Click the Name Manager icon on the Formulas ribbon.

2.  Type a name

3.  Type your constant’s value into the Refers to box.

Excel will add an equals sign for you. Percentages will also convert to decimals, e.g. 10% = 0.1

Creating a constant

Constants are not listed in the Name Box or the Go To box but do appear in the Paste Name dialog box when you are constructing a formula.

5. How to use a named cell in a formula

When a named cell is used in a formula, the name substitutes the cell reference. There are three ways the name can be inserted into a formula.

While editing or entering the formula:

  • Type the name in directly (best option for short names like GST).
  • Click on the named cell (if it is close at hand). The name will be inserted into the formula.
  • Press F4 to display a list of named cells. The Paste Name dialog box is displayed. Choose a name from the list and click Ok. This option is useful for longer names.
Using a named cell within a formula

6. Using named cells for navigation

There are 2 ways to use named cell(s) to navigate to a different location in the same workbook:

TECHNIQUE 1:

1.  Click the drop-down arrow on the Name Box.

2.  Select a name from the list. This will take you directly to the sheet and cell.

Using a named cell as a navigation tool

TECHNIQUE 2:

1.  Press F5 or Ctrl G to display the Go To dialog.

2.  Select a name from the list.

3.  Click Ok

Using a named cell as a navigation tool

7. Changing the cell(s) that a name refers to

To change the cell reference or cell range that a name refers to

1.  Select the Name Manager icon on the Formulas ribbon.

2.  Highlight the name you wish to change the details for.

3.  Click the Edit button.

4.  Either place the cursor in the Refers To box and carefully type your changes or delete the entire current entry and reselect from the worksheet. The second method is often easier, because when you place the cursor in the box, then use the cursor keys, a cell reference is placed in the middle of your existing entry.

5.  Click Ok and then Close.

8. Removing a name from a cell

To un-name a cell (remove the name):

1.  Select the Name Manager icon on the Formulas ribbon.

2.  Highlight the name.

3.  Click the Delete button.

9. Using table headings for naming cell ranges

To use existing column headings or row labels as the names for new named ranges:

1.  Select the data, including the headings.

2.  Click Create from Selection on the Formulas ribbon.

3.  Specify which parts of the selected range you wish to use as names. This is normally the top row or the left column or both.

4.  Click Ok

Using existing headings to name ranges

10. Applying new names to existing formulas

If you have created formulas which refer to cell(s) that have since been named, you can apply the names automatically to your existing formulas.

1.  Select the formula cell(s) that currently refer to a cell reference or cell range that has since been named.

2.  Click the drop-down arrow on the Define Name button on the Formulas ribbon.

3.  Choose Apply Names. Excel selects any relevant cell names. Do not change these.

4.  Click Ok

5.  Check one of the original formulas and note that the name has replaced the original cell/range.

Applying names to existing formulas

11. Displaying a summary of named cells

To display a list of named cells, ranges, constants and formulas as a printable reference:

1.  Select a blank area of the worksheet where the list will be inserted.

2.  Click the drop-down arrow on the Use in Formula button (Formulas ribbon). The Paste Names dialog box is displayed.

3.  Click Paste List. Each name is pasted onto the worksheet with its associated cell, range, constant or formula.

Displaying a summary of named cells

12. Key Takeaways

  • To name a cell (or range of cells) select the cell/range, type a name into the name box and press Enter
  • To modify or delete a name, modify a range that a name refers to or add constant, use the Name Manager, by going to the Formulas ribbon.
  • To use a named cell/range with a formula, press F3 to display a list of named cells/ranges. Select a name.
  • To use a named cell/range for navigation, press F5 or Ctrl G to display the Go To dialog box. Choose a name from the list and click Ok.
  • To use existing headings to create named ranges, select the data, including the headings, click the Create from selection icon on the Formulas ribbon, tick the boxes that indicate where the labels are and click Ok.
  • To apply new names to existing formulas, select the formula cell(s), click the drop-down arrow on the Define Names icon on the Formulas ribbon, choose Apply names, accept the suggested list and click Ok.
  • To display a static list of named cells/ranges, select a blank area of the worksheet, click the drop-down arrow on the Use in Formula button on the Formulas ribbon and choose Paste Names. When the Paste Names dialog box is displayed, click Paste All.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
10 Steps to a Perfect Print Page Setup in Excel With Auto-Repeat Headings and Controlled Page Breaks
Read More
Excel Auditing Checklist: 2 Auto Features and 4 Tools to Quickly Identify and Rectify Errors BEFORE You Publish
Read More
Supercharge Your Cut and Paste: Popular Methods, Shortcuts and 2 Smarter Approaches
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Office Mastery
>