Named Ranges in Excel 2010

Learn how to create a descriptive name to refer to a range of cells on an Excel worksheet with this guide for Excel 2010 users…


What is a Named Range?

A Named Range is a text label that refers to a range of Excel cells.  For example the name could be Sales and the range of cells it refers to could be A1 to C10.  Naming a range of cells allows you to use that text name in formulas rather than referring to the actual cells, for example:

=SUM(A1:C10)

Would instead be written as:

=SUM(Sales)

This is where you have set cells A1:C10 as the named range “Sales”.

Why use a Named Range?

Naming ranges is not a necessary practice to use but it is a good habit to get into because of how easy it makes writing complicated formula, along with making those formulas easier to edit and easier to handover to other people if needed.

It is a very useful practice in Excel reports and Excel Dashboards as often you will have summary tables that contain some kind of calculation against a dataset on another worksheet and it is much cleaner to refer to columns of data or different ranges using a name like “Sales Date” or “Employee Name” that looking up a range of cells all the time.

When you start to create lengthy and complicated formula that refers to multiple ranges, VLOOKUPs or multiple IF statements come to mind, the practice of naming those ranges is very beneficial from a testing and checking perspective and time-saving in the long run.

How to create a Named Range

Creating a named range in Excel is quick and easy to learn.  To start with you need to select your range of cells that you want to give a name to, in the example below cells B8:B14 are highlighted:

The next step is to define the name, to do this you enter the name in the name box, which is located below the ribbon, next to the formula bar:

In this case seeing as Division A is highlighted we will call the cell range “DivisionA”.

Tip: Spaces are not allowed in the names, you can use underscores in their place.

Editing the Named Range

You might find you need to review the named ranges on a worksheet, or perhaps edit the ranges to include more cells.  In this case it is useful to know where the “Name Manager” is within Excel.  To find the “Name Manager” left-click on “Formulas” on the Ribbon and you will see “Name Manager” located centrally:

Click on the name manager to bring up the list of all the defined named ranges within the workbook:

From the Name Manager you can Edit or Delete a range, or even use this way to define a new named range.  A key point to note here is if you edit the range in the “Refers to:” box at the bottom of the Name Manager remember to left-click the check (tick) mark to the left.  If you just click “Close” any changes will not have been saved.

More uses for Named Ranges

The use of named ranges in Excel does not stop here.  In a future post we will look at creating dynamic named ranges to assist in report automation and show you how to create a dynamic chart using named ranges.

In the meantime start putting those named ranges to use and implementing them into your Excel workbooks today!

Keep Excelling,

Leave a Comment