Range Names

Range names can be used to refer to an individual cell or a range of cells.  They can be used in formulas and can also be used to help you move around your workbook.  Range names can help you with accuracy in formulas, you are less likely to refer to the wrong cell address, and will save time in finding areas and highlighting them.

The easiest way to create a range name is to highlight the range of cells you want to name and then click in the Name Box to the left of the formula bar and type a name for the range.  The diagram below shows the range name Rain1990 in the Name box, the range name refers to the highlighted cells (C2:C13).

Alternatively, you can click on a cell or highlight a range of cells and then click on the Insert drop-down menu, choose Name and then Define.  Enter a meaningful name in the box at the top of the Define Name window and then click the Add button to add it to the list of names in the workbook.  The diagram below shows a highlighted range and the name for that range being defined.  Notice the reference to the highlighted range at the bottom of the window =?1990?!$C$2:$C$13, this is selecting the worksheet labelled 1990 and the range of cells C2:C13.


Range names must start with a character but may then contain a mixture of letters and numbers.  Spaces are not allowed in range names but you can use the underscore character or full stop instead, e.g. East_Team.  Range names must not be the same as cell references, e.g. Q3, M54.

When you have created some range names they are available in then name box beside your formula bar shown below .  You can select a range name from this box to move to that range or cell.

When creating a formula you can use a range name to save entering references.  When you start creating your formula by typing = in a cell, press F3 to display a list of the available names in your spreadsheet.  Select the name of the range you want to use and click OK, the name will now be entered in your formula and the range name box will disappear.  If you need another range name in your formula, just press F3 again to display the names window and select another one.  A formula using names is shown below:


If you want a list of all the names used in your spreadsheet, put your cursor in a blank area of a worksheet and click on the Insert drop-down menu, choose Names and then Paste.  A window appears as shown below , click on the Paste List button and a list of range names will be pasted into your worksheet.

Range names are very useful when carrying out consolidation and using database functions.

Click here to return to the Excel index.