List All Defined Names and Values in the Worksheet in Excel

This allows you to view and analyze all of your Defined Names and that will help you manage and troubleshoot any potential issues.

Steps to List all Defined Names and Values in the Worksheet

1. Select a cell that has enough space below it and to the side to display the list of names and values.
   
2. Go to the Formulas tab and click the button Use in Formula and then click the option Paste Names
   
   
3. A small window will open.  Click the button Paste List
   
   
4. That's it!  Now you will see a list of names along with the values associated with each name.
   

You will see that there is an equal sign in front of each number and that is just because of the way the values are stored for Defined Names when they are hard-coded.  If there were cell references or formulas or functions stored as the value for the Name, those would show in Column B.

Notes

This is a simple tip but it is quite helpful when you have a large worksheet with a lot of Defined Names or when you inherit a spreadsheet from someone else and want to quickly see what is going on within it.

Make sure to download the sample file attached to this tutorial so you can see this feature in action.