Excel > Excel 2007 Intermediate > Named Ranges

Creating a Range Name

The ability to name ranges is a fantastic Excel tool that allows us to assign a more meaningful name to a cell or range of cells. This makes future tasks like selecting the range or referring to the range in a formula much easier.

There are 3 advantages to using named ranges:

  • Range names are more meaningful. The basic premise is that we would find it easier to use names like JanSales or ExchangeRate more than we would cell references like B2:B12 or C10.
  • You can refer to a named range across worksheets. Every worksheet has C10, but only one worksheet in that workbook has ExchangeRate. Therefore if we refer to ExchangeRate when writing a formula in Sheet4, Excel knows we mean cell C10 in Sheet1.
  • Named ranges use absolute cell references, so it will prevent the cell references changing when we copy or fill a formula across a range of cells.

To create a named range:

  1. Select the cell or cells that you wish to name
  2. Click on the Name Box
  3. Entering a range name

  4. Enter the name for the cell(s)
  5. Press Enter

The most common mistake when creating range names is forgetting to press Enter after entering the name. Don't forget that Enter key to confirm the cell(s) new name

There are some rules to naming a range:

  • You cannot use spaces. Common alternatives include using capital letters i.e. NamedRange or the underscore character i.e. named_range.
  • You cannot start the name with a number. So sales2009 is ok, but 2009sales is not.
  • It cannot be another cell reference. So you couldn't use BB10 as that is the name of a cell reference within the worksheet.
  • You cannot use special punctuation characters such as - and /.
  • If you enter a range name that is not valid, you will see the following message.

    Invalid name

Selecting a named range

A range name can be quickly and easily selected from any sheet of the workbook. This works great with large data lists

To select a named range:

  1. Click the drop list arrow on the Name Box
  2. Selecting a range name

  3. A list appears showing all the named ranges in the workbook. Select the name range you want
  4. Excel navigates to the worksheet and selects the range for you.

Follow us on

Facebook  Twitter  You Tube 

Excel 2010 Superhero Course