Microsoft Excel How-to: Define and Name Ranges
Once 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
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:
This will name this range 'range_01'. A couple of quick points about using the Name Box:
- 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.
- 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:
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:
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:
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.
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:
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.