List All Defined Names and Values in the Worksheet in Excel

Add to Favorites

Quickly list all Defined Names and their Values in the worksheet without using a macro.

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...
Easily Compare Duplicate Values in Excel
Tutorial: Here, I'll show you a simple technique to quickly and easily compare large lists of duplic...
List all Conditional Formatting Formulas in Excel
Tutorial: List all conditional formatting formulas in a worksheet in Excel. This allows you to quick...
Loop through a Range of Cells in a UDF in Excel
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...
Remove All Filtering From a Worksheet in Excel
Macro: This Excel macro removes all filtering from a worksheet in Excel. This allows you to ...