Three methods to display a workbook's named ranges

Sometimes, when your workbook has many named ranges, you'd like to have them as a list, in a separate worksheet.

There are at least three methods to accomplish this task:

1.   The shortest method

1)    Press: the F3 key.
the following pane appears:

Pic 1: Press F3

 

2)    Now, press: ALT+L (or just click the Paste List button)
All the named ranges will be pasted into the worksheet.

 

2.   Using a defined name in the Name Manager

 

1)      Press: CTRL+F3 to get to the Name Manager

2)    Click: New (or press: ALT+n) to define a new name

3)    In the Name textbox enter: List (or any name you like)

4)    In the Refers to textbox enter: =NAMES()

Pic 2: define a named range that refers to the NAMES() command

 

5)    In an empty cell of the worksheet, type:
=TRANSPOSE(List) 

All the workbook names will be pasted into the worksheet.

 

3.   VBA

Add the following code to the VBA editor and run it:

Pic 3: Add this code to the VBA editor and run it

 

After running the code you'll see the list of all the workbook's names in a new worksheet.