Microsoft Excel How-to: Define and Name Ranges

excel logoOnce you start getting into some of the more advanced functions in Excel you're going to want to start defining and naming ranges. Doing so makes your functions much easier to write, and much easier for someone else to understand. All that you're doing when you define or name a range in Excel is to select a cell, or a series of cells, and assign a meaningful name to those cells.

The easiest way to do this is by looking at an example. Open Excel and select a range of cells, for this example I have selected the range of cells B3:D14

 

excel name range 01

 

Now, the fastest way to name this range is to just type a valid name directly into the 'Name Box' in the upper left corner of the active sheet:

 

excel name range 02

 

This will name this range 'range_01'. A couple of quick points about using the Name Box:

  1. After you type in a name you must hit 'enter'. If you just click on something else with your mouse after typing in the range name it will not get saved.
  2. You can only use the Name Box to enter in range names for new ranges, and for viewing any existing ranges. If you attempt to over-write an existing name by typing a new name over an existing name Excel will simply add the new name to the list. This will give you a range with 2 names, which can be very confusing.

Valid names for ranges can include any alpha-numeric value and the underscore. If you try to use any special characters you will get an error message, and if you try to use an existing cell number or range of cells Excel will simply take you to those cells instead. In other words, you can not select cell B10 and enter the text 'A13' into the Name Box, instead of assigning that name to the cell it will take you to cell A13.

The Name Box is fairly limited in its capabilities. It comes in very handy for quickly naming a range, and for selecting any existing ranges. However, if you want to delete, re-name or assign a different set of cells to a range you must use the Define Name dialog box. To get to this select Insert -> Name -> Define... from the main menu:

 

excel name range 03

 

This will give you a dialog box from which you can fully manipulate existing ranges, and create new ones. For example, let's say that after you created this range you decided that you wanted to add an additional row to it. To do this you would first click on the range name, then click on the collapse dialog button:

 

excel name range 04

 

This will collapse the dialog so that you can see the sheet, and will place the 'dancing ants' border around the existing range. Simply select the new range by highlighting the cells you want included, then press the enter key:

 

excel name range 05

 

This will modify the existing range to include the new cells that you just selected. You can also delete ranges, and create new ones from this same dialog box. I've added another range below named 'range_02' by selecting another range of cells using the collapse dialog button, and typing the new name into the top text box. After you're done click on OK and your changes will be saved.

 

excel name range 06

 

Now to see the ranges you have named all you need to do is to click on the drop-down arrow next to the Name Box:

 

excel name range 07

 

Clicking on one of the named ranges will highlight that range. Naming ranges is a very handy trick that will greatly reduce the amount of time you spend typing in cell values, and if you put some thought into the names you select for your ranges they will make your formulas much easier to understand.

Bytes: