Message:Coronavirus (COVID-19)

Blog

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

April 21st, 2016 by Mark Daly in Industry News No Comments »
How to See All of the Named Cell Ranges in an Excel Workbook ilicomm Technology Solutions

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.

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

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

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.

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

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.

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

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

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

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.

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

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.

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

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

Leave a Reply

You must be logged in to post a comment.

NEED MORE INFORMATION?Contact us to see how we can help your business

Call our Sales Team on:

+44 (0)121 289 3434

or email us at:

hello@ilicomm.com