How to See All of the Named Cell Ranges in an Excel Workbook

00_lead_image_named_ranges_list

Naming a range of cells in Excel provide an easy way to reference those cells in a formula. If you have a workbook with a lot of data on the worksheets, naming ranges of cells can make your formulas easier to read and less confusing.

RELATED: How to Assign a Name to a Range of Cells in Excel

But if you have a particularly big spreadsheet, you may not remember which names refer to which ranges. We’ll show you how to generate a list of names and their associated cell ranges you can reference as you make formulas for that spreadsheet.

Depending on how many names you have in your workbook, you may want to use a new worksheet to store the list. Our list is not very long, but we still want to keep it separate from the rest of our data. So, right-click on the worksheet tabs at the bottom of the Excel window and select “Insert” from the popup menu. When the “Insert” dialog box displays, make sure the “General” tab is active and “Worksheet” is selected in the right box. Then, click “OK”.

01_selecting_insert_on_tab_menu

Select the cell on your new worksheet where you want the list of names to start and click the Formulas tab. You can add some headings above your list if you want, like we did below.

03_clicking_formulas_tab

In the Defined Names section, click “Use In Formula” and select “Paste Names” from the drop-down menu. You can also press “F3”.

NOTE: If there are no named cell ranges in your workbook, the “Use In Formula” button is not available.

04_selecting_paste_names

On the Paste Name dialog box, all the named cell ranges display in the Paste name list. To insert the entire list into the worksheet, click “Paste List”.

05_clicking_paste_list

The list is inserted starting in the selected cell. You might want to widen the columns so the names don’t get cut off. Simply put the cursor over the right edge of the column you want to widen until it becomes a double arrow and then double-click.

06_widening_column

Your list of names and the corresponding cell ranges display in your worksheet. You can save your workbook like this so you have a list of your names and you can also print the worksheet if you want.

07_names_list_generated

If you add names to or remove names from the workbook, delete the generated list and generate it again to obtain an updated list.

Lori Kaufman is a writer who likes to write geeky how-to articles to help make people's lives easier through the use of technology. She loves watching and reading mysteries and is an avid Doctor Who fan.