The simplest and most general definition of a cell range in Excel is a range is a group of one or more cells. A group of adjacent cells can be a range, but a range can also include scattered cells.
Our Example Worksheet
Create named range using the name box
One way to create a named range in Excel is to select the cells that you want in the range, then enter the range name in the name box. You can select sells using your mouse:
- For a continuous range of cells, click on the top left cell in the desired range and then drag to the lower left cell while holding down the left mouse button
- For discontinuous range of cells, click on each desired cell and holding down the Ctrl key (or Cmd key on a Mac) click on each other desired cell
- Select a cell or group of cells to be included in the named range. In this example, I’ve selected cells D4:D7
- Enter the range name in the name box. Name is not case sensitive, but must not include any spaces. For this example, I’d enter Apples in the name box.
Create a named range using Name Manager
Another way to create (or edit) a range name is using the Name Manager. Name Manager is actually a powerful tool to create named objects to use in your workbook.
- Click “Formulas” on the ribbon
- Click “Name Manager”
- The Name Manager dialog box appears (not shown). Select New
- The New Name dialog box will appear: enter your range name and select the cell or cells to include in the range. In this example, I’ve selected cells E4:E7 and given them the name grapes
- The cells can be selected using your mouse, as above, or you can just type in the cells using your keyboard. For a discontinuous range, separate groups of cells with a comma.
Using Name Manager to Review Named Ranges
You can also use Name Manager to see what named ranges exist in a workbook:
To the left you can see the Name Manager dialog box after creating the apples and grapes ranges.
Again, you can access the Name Manager dialog box by clicking “Formulas” on the ribbon, then clicking “Name Manager”
Using a range name in a formula
Most formulas in Excel refer to a cell or a range of cells. Using a named range instead of the cell references can improve your spreadsheets by:
- Make the formulas easier to understand and maintain
- Changing the definition of a name range (adding or deleting cells) will automatically update all formulas that refer to that named range.
In the example below, I’ve completed naming ranges from bananas and melons, and also named a range named fruit containing cells in the range D4:G7. I can then use fruit in a sum formula in cell D11 (the actual formula appears in cell E11).
