As you are no doubt aware, each cell in Excel is identified by its column/row coordinates, referred to as a cell reference. The active cell is always surrounded by a border and its reference (for example, B5) is displayed in the Name Box to the left of the Formula Bar.
But did you know you can assign a special name to a single cell or to a range of cells? You can then use that name, rather than its cell reference, to quickly jump to it. Named ranges are especially useful in large worksheets, as well as in multiple-sheet workbooks.
Although you can use the Name Manager button in the Defined Names group on the Formula tab, I prefer to use the Name Box itself.
Here’s how:
1. Select the cell(s) that is/are to be named. (The selection can include adjoining, as well as non-contiguous cells.)
2. Click in the Name Box to the left end of the formula bar, which indicates the address of the current cell.
3. Type a name. (The name can include letters, numbers, periods and underscores. The first character must be a letter or an underscore. Spaces are not allowed. )
4. Press ENTER.
You can now quickly jump from any cell in any of the workbook’s sheets to the relevant cell or range of cells. Simply select its name from the drop-down arrow to the right side of the Name Box. You can also use the names in a formula, making them easier to understand. Furthermore, a named range is “absolute”, which means that when building your formula you don’t have to use dollar signs before each coordinate, such as $B$5.
Related tips
Switching between relative and absolute cell references
Our current course schedule can be found online. Book more than one person from your organisation on the same course, on the same date, and you get 10% off.
17 February 2020
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk