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 is quick method you can use to identify the referenced ranges for each named range.

Method

One quick way to identify referenced ranges is to select Formula → Defined Names → Use in Formula → Paste Names (pre-2007, Insert → Name → Paste), or press F3. In the Paste Name dialog, click OK and Excel will list all your names in rows, starting from your active cell, with the names' corresponding references in the opposite column.

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.