Step-by-Step Tutorial on Revealing All Custom Cell References Across an Excel File

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”.

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.

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.

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”.

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.

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.

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

Also read: