Excel Training – Named Cells & Ranges

excel trainingNaming Cells and Ranges in Microsoft Excel

A Named Range is a descriptive name applied to a range of cells. When creating complex formulae a named range can be used instead of selecting the cells or entering the cell reference. When working with Data Consolidation, Pivot Tables or any long lists of data, a named range can make it easier to manage. Range Names cannot contain any spaces or punctuation. The Name options are on the Formulas tab of the ribbon.

Using The Create Method To Name Cells

  1. Select the cells to be named and the cells containing the headings to be used as names.
  2. Click on the Create from Selection button and tick the box to choose which cells contain the value to be used as the name. The cells will now be named with underscores in place of any spaces.

excel training named ranges

Using The Names Box To Name Cells

  1. Select the cells to be named.
  2. Click into the name box (to the left of the formula bar), type the name and press enter.

excel training named ranges

Using Insert Name Define

  1. Select the cells to be named.
  2. Click on the Define Name button and enter a name for the range.
  3. Enter the Scope for the range, which allows you specify whether the named range is to be recognised in the whole workbook or within a specific worksheet.
  4. If required, edit the range in the ‘refers to’ box.
  5. Click on OK.

excel training named ranges

Using the Name Manager

  1. Click on the Name Manager button.
  2. Select the named range you wish to edit and click on the edit button to re-enter the Define Name box to make a change, or click on Delete to remove the range name.

excel training named ranges

Using Paste Names

  1. To get a list of names in the worksheet, click on the cell in which to start the list of named ranges.
  2. Click on the Use in Formula button, and click Paste Names and Paste List.

excel training named ranges

Selecting a Named Range

  1. Click on the drop down list next to the Name Box and click on the range to be selected.

excel training named ranges

Using a Named Range in a Formula

  1. Create your formula in the usual way, but instead of selecting the range of cells, type the Range Name. For example, a formula using a cell with the Name “VAT”, multiplied by B1, would look like this:

=VAT*B1

Tip: a list of range names can be displayed by pressing F3 on the keyboard when creating the formula.

Important Rules of Names for Cells and Ranges

Names must conform to the following rules:

  • SPACES are NOT allowed
  • First character must be a letter or an Underscore _
  • Names cannot be the same as cell references
  • Names cannot be more than 255 characters long
  • Names are NOT case sensitive

Other important considerations

  • All named ranges are absolute references
  • A named range can be referenced from any sheet within a work book.

Tags

, , , , , , , , , , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

Leave a Comment