Microsoft Excel

Identify Named Ranges on a Worksheet

Excel enables users to give meaningful names to specific ranges in their worksheets. As the number of different named ranges on a worksheet grows, you will need tools for identifying the areas referenced by your named ranges.

Here are two quick methods you can use to identify the referenced ranges for each named range.

Method 1

One very quick way to identify referenced ranges is to select Insert » Name » Paste, or press F3. In the Paste Name dialog, click OK, as shown in the figure, and Excel will list all your names in rows, starting from your active cell, with the names' corresponding references in the opposite column.

Figure. The Paste Name dialog

This will give you a list of all your names in the active workbook.

Although this can be handy to help you identify specific ranges, it still requires that you either manually select the specific named range, or perhaps use the Go To... dialog. However, once you have a list of named ranges, you can remove all the referenced cell addresses corresponding to the names and replace them with a simple hyperlink function.

This will enable you to create a list of all named ranges. Clicking any item on the list will take you to the specified range. For instance, assume your list of names resides in column A, starting from cell A1. In cell B1, enter this formula:


Copy this formula down as far as you need to and replace Book1.xls with your workbook's name.

Method 2

All you need to do is set the zoom on your Excel worksheet to any percentage lower than 40-i.e., 39% or less. This will display all your named ranges on the sheet for easy identification, as shown in the figure.

Figure. Named range zoom providing easy identification of named ranges