Named Ranges in Excel
When you’ve got a lot of rows of data in your worksheet it can be quite tedious to use the mouse to highlight or select them every time you want to use a formula or make a chart. Using named ranges can take some of the tedium out of this process (keyboard shortcuts can also help but I’ll deal with them another time).
You can make any selection into a named range. There are two main ways:
- Select some cells and type the name into the Name Box.
- Use the Defined Names section of the Formulas
The Name Box usually shows you the row and column reference of the currently active cell, you can type a cell reference into the box to jump directly to that location.
To define a named range, you can select some cells then type a name into the Name Box. Excel doesn’t like spaces, which will convert to underscore. The trick is to keep names short, but meaningful. Any defined names will appear when you click the triangle icon in the Name Box.
If you click a name, you’ll select the cells linked to that named range. You’ll probably have your data arranged so that each column is a separate variable. The easy way to define names for all the columns is to use the Formulas > Create from Selection button.
First select all the data, click once anywhere in the data then hit Ctrl+A on the keyboard. Then go to the Formulasmenu and click the Create from Selection button. You can now decide where the names are, usually they’ll be the top row but there are other options.
Now the names are defined you’ll see the names in the Name Box. The names can be used any time you need a cell range and are not case sensitive. In fact, if you start to type a name in a formula you’ll see any matching named ranges appear in a pop-up (along with function names).
You can also use names in defining charts but you’ll generally have to give the worksheet name too, e.g. Data!Ozone selects the named range Ozone from the Dataworksheet.
The Formulas > Name Manager button allows you to manage the named ranges, you can edit or delete items.
Comments are closed.