Categories
Excel

Find and List Named Ranges

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

  1. Go TO
  2. Use in Formula
  3. Paste Names: List All Named Ranges
  4. Name Manager

Go To

To quickly find a named range you can use the Go To dialog box. This dialog box allows you to navigate to specific named ranges on a worksheet.

To access the Go To dialog box:

  • Press the F5 key or use the keyboard shortcut Ctrl+G.
  • OR, go to the Home tab in the ribbon, and click on the Find & Select button in the Editing group. From the dropdown menu, select Go To….

In the Go To dialog box, you can scroll through the list to locate the desired named range.

Once you have selected the named range, click on the OK button. Excel will automatically navigate to the cell or range associated with the named range.

Use in Formula

The Use in Formula command allows you to quickly and easily insert named ranges into your formulas:

  1. Select the cell where you want to insert the named range.
  2. Navigate to the Formulas tab in the ribbon.
  3. Click on the Use in Formula button.
  4. A drop-down menu will appear, displaying a list of all the named ranges in your workbook.

From the drop-down menu, select the named range you want to insert into your formula. Excel will automatically insert the range reference into the formula, saving you the time and effort of manually typing it.

Use Paste Names to List All Names

One very quick way to identify referenced ranges is to use the Paste Name dialog box:

  • Press F3 or
  • Navigate to the Formulas tab in the ribbon, click on the Use in Formula button and choose the Paste Names.

In the Paste Name dialog, click Paste List.

Click Paste List button to display all named ranges

Excel will list all names in rows, starting from your active cell, with the names’ corresponding references in the opposite column.

Manage Names using Name Manager

The Name Manager allows you to view and manage all the named ranges in your workbook, not just in a specific worksheet. The Name Manager allows you to perform various actions on named ranges. You can edit their references, change their names, delete them, or modify their properties.

To access the Name Manager dialog box:

  • Press CTRL+F3 or
  • Navigate to the Formulas tab in the ribbon and click on the Name Manager button.

This will open a dialog box that displays the list of all the named ranges in the workbook. You can scroll through the list to locate the named ranges you are interested in. The names of the ranges, their respective references, and additional information such as the scope and comments will be displayed.