This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

Using Range Names in Excel
Did you know that you don't have to use cell references in calculations in Microsoft Excel? You can use range names to create sensible, plain English names to refer to your cells and use those in your formulae instead. Read this blog to find out how.

Posted by Andrew Gould on 22 August 2011

Using Names to Refer to Cells

When you start using Excel you will most likely have been taught to refer to cells using their cell references, for example A1 or D2:D10 etc. Did you know that there's a way to give a name to a range of cells and then to use this name in your formulae? Read on to find out how!

Naming Cells by Typing in a Name

One of the easiest ways to give a name to a range of cells is to simply type a name into the Name Box. You can see the Name Box in the top left hand corner of your screen, as shown in the diagram below.

By default, the Name Box shows the cell reference of the active cell.

To give a single cell or block of cells a name:

Select the cell or cells you want to name.
Click into the Name Box.
Type in the name you want to use (see the hint below).
Press Enter on the keyboard.


Click into the Name Box...	...and type in a name for the cell.

You can't include spaces in your range names! There are many other disallowed punctuation characters, but you can still use the underscore ( _ ), the backslash ( \ ), the full stop ( . ) and the question mark (? ). You can include numbers in a range name as long as the name doesn't start with a number.

Using a Name in a Formula

Once you have created a name to refer to a cell, you can use the name in place of cell references in a calculation. The diagrams below show the same formula, one uses absolute cell references, the other uses a range name.


This works, but looks awkward.	This formula is much clearer.

In Excel 2007 and 2010 your range names will appear in a drop down list as you type in your formula.

When you have the range name highlighted in the list, press Tab on the keyboard or double-click on it with the mouse to insert the name into your formula.

In any version of Excel you can press F3 on the keyboard while typing a formula to insert a range name.


Press F3 at this point...	...to see a list of range names that you can insert into the formula.

Creating Range Names Using Row and Column Headings

If you have sensible row and column headings in a spreadsheet you can tell Excel to use these headings to create range names.

Creating range names using column headings

In this example we can create two range names at the same time: one called Loan to refer to cells A4:A8, and one called Interest to refer to cells B4:B8. To do this we need to select cells A3:B8 first.

Once you have selected the relevant cells (making sure you have included the headings you want to use!), choose one of the following options:

In any version of Excel press CTRL + Shift + F3
In Excel 2003, from the menu choose: Insert -> Name -> Create...
In Excel 2007 or 2010, choose: Formulas -> Create from Selection

This will open a dialog box asking for the location of the row or column headings you want to use to create your range name(s).

In our example, the headings we want to use are in the top row of the selected cells, so we tick the Top row box and click OK

You can check that all of the range names have been created by clicking on the drop down arrow to the right of the Name Box at the top left of the screen.

Click the drop arrow next to the Name Box to see a full list of range names that you have created.

We can now use the range names in more calculations.


You can use range names in simple formulae like this...	...and in functions like this.

Editing and Deleting Range Names

If you need to change or remove a range name, unfortunately you can't use the Name Box. To modify anything about a range name, choose one of the following options:

In any version of Excel, press CTRL + F3 on the keyboard.
In Excel 2003, choose: Insert -> Name -> Define...
In Excel 2007 or 2010, choose: Formulas -> Name Manager

You can now change any of your range names using the sensibly labelled buttons on the dialog box.

This is the Name Manager in Excel 2007. The dialog box is slightly different in Excel 2003 but you can still add, edit or delete range names using it.

Conclusion

You can use range names in any formula you create in place of the cell references that you would normally use. They make your calculations much easier to read and avoid the need to consider whether you need to use relative or absolute cell references. They're well worth the effort of learning how to use them.

Some other pages relevant to the above blog include:

Microsoft Excel training

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR