Keep Track of Names in Excel Workbook

You probably use defined names in some of your Excel workbooks. We’ll look at a built-in way to list the names in a workbook, and see some Excel VBA code that creates a more detailed list of names.

Using Names in Excel

You can name a group of cells, and use that name as the source for a data validation drop down list.

For example, if you have entered a list of the products that you sell, you could select the list, and name the range reference as ProdList.

Then, that product list could be used in an order form.

NamesList00

Table Names

If you created Excel Tables, in Excel 2007 or Excel 2010, they are automatically named.

Later, you can change the names to something meaningful, such as ProdTable, for a list of products and their prices.

NamesList00a

Create a List of Names

If you’re working on a complex Excel workbook, it’s easy to lose track of what you’ve named, and where the named ranges are located.

For reference, you can print out a list of names, using a built-in feature in Excel.

To paste a list of workbook level names in Excel:

  • Insert a blank worksheet
  • On the Excel Ribbon, click the Formulas tab
  • In the Defined Names group, click Use in Formula, and click Paste Names (the keyboard shortcut is F3)
paste a list of workbook level names in Excel
paste a list of workbook level names in Excel

Paste Name Dialog Box

Next, in the Paste Name window, click the Paste List button.

Paste Name Dialog Box
Paste Name Dialog Box

Names List on Worksheet

A list of defined names and their formulas is pasted into the worksheet.

NamesList03

Note: To see worksheet level names, use the Paste List feature on the worksheet where those names are defined.

Create Names List with Excel VBA Macro

The built-in names list feature is helpful, but if you need more details, you can create your own list, by using Excel VBA.

This macro adds a new sheet to the active workbook, with a list of the non-hidden defined names, with details for each name, if available.

  • A – Name;
  • B – Refers To formula;
  • C – Number of cells in the range;
  • D – Sheet name where range is located;
  • E – Address on worksheet;
  • F – Scope (workbook or worksheet)
Sub ListAllNames()
Dim lRow As Long
Dim nm As Name
Dim wb As Workbook
Dim ws As Worksheet
Dim wsL As Worksheet
Dim wsName As String
Dim shName As String
Dim myName As String
Dim nmRef As String
Dim nmAddr As String
Dim nmRng As Range
Dim nmSc As String
Dim lCells As Long
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set wsL = Worksheets.Add

wsName = ws.Name

With wsL
    .Range("A1:F1").Value = Array("Name", _
        "Refers To", "Cells", "Sheet", "Address", "Scope")
    lRow = 2
End With

On Error Resume Next
  For Each nm In wb.Names
    If nm.Visible Then
      Set nmRng = nm.RefersToRange
      myName = nm.Name
      nmRef = "'" & nm.RefersTo
      lCells = nmRng.Cells.Count
      shName = nm.RefersToRange.Parent.Name
      nmAddr = nm.RefersToRange.Address
      If TypeOf nm.Parent Is Workbook Then
        nmSc = "Wb"
      Else
        nmSc = "Ws"
      End If
      wsL.Range(wsL.Cells(lRow, 1), wsL.Cells(lRow, 6)).Value _
         = Array(myName, nmRef, lCells, shName, nmAddr, nmSc)
      lRow = lRow + 1
      Set nmRng = Nothing
      myName = ""
      nmRef = ""
      lCells = 0
      shName = ""
      nmAddr = ""
      nmSc = ""
    End If
  Next nm
         
With wsL
  .Rows("1:1").Font.Bold = True
  .Columns("A:F").EntireColumn.AutoFit
End With

End Sub

Download the Names List Sample File

To get the sample workbook, and the Names List code, go to the Excel Names Macros page on my Contextures site.

The file is zipped, and in Excel xlsm file format, and it contains macros.
___________

6 thoughts on “Keep Track of Names in Excel Workbook”

  1. Just wanted to clarify that, even though this page talks about naming Excel Tables – that you can change the names to something meaningful, such as ProdTable – Table Names are not included in the built-in tool in Excel that lists Names (even though tables are listed in the Name Box to the left of the Insert Function and Formula Bar boxes). Neither are Table Names included in The Names List Code.
    This is not to imply that the page doesn’t do everything it’s advertised to. I was just hoping to find code about listing Table Names too. I’ll keep looking.

      1. @Debra That’s awesome – thanks for posting that! Similar to what I ended up writing, but I didn’t think to check if ListObjects existed in the worksheet. I’ll add this condition to my code:
        If ws.ListObjects.Count > 0 Then

  2. I was hoping you can help me, we are trying to find a way to track Parent and the child’s name for things like early dismissal, Tardy, Visiting, or Volunteering and then at the end of the month running a report to see how many times they were late, left early, visited or Volunteered. Is there away to do this in excel??

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.