Excel Guides

Finding Unused Names in Excel

There are a few ways to find unused names in Excel. One way is to use the Name Manager. To do this, go to the Formulas tab and click Name Manager. This will open the Name Manager dialog box. From here, you can see all of the named ranges in your workbook. If there is a named range that you no longer need, you can delete it by selecting it and clicking the Delete button.

Another way to find unused names is to use the Go To feature. To do this, go to the Home tab and click Find & Select. Then, click Go To. This will open the Go To dialog box. In the Reference field, type the name of the named range that you want to find. If the named range is not used anywhere in your workbook, Excel will tell you that it cannot be found.

You can also use formulas to find unused names. One formula that you can use is =COUNTIF(INDIRECT("A1:A"&COUNTA(A:A)),A1). This formula will count how many times a named range is used in your workbook. Another formula that you can use is =COUNTIF(INDIRECT("B1:B"&COUNTA(B:B)),B1). This formula will count how many times a named range is used in your workbook.

If you have a lot of named ranges in your workbook, you may want to consider using VBA to find unused names. To do this, press Alt+F11 to open the Visual Basic Editor. Then, insert a new module and paste the following code into it:

Sub FindUnusedNames()
  Dim i As Long
  Dim j As Long
  Dim k As Long
  Dim l As Long

  For i = 1 To Sheets.Count
    For j = 1 To Range("IV1").Value
      If Not Application.Intersect(Sheets(i).UsedRange, Range(Cells(1, j), Cells(1, j))) Is Nothing Then
        k = k + 1
        l = l + 1

        If k = 1 Then
          MsgBox "The following unused names were found:" & vbNewLine & vbNewLine & _
                 Cells(1, j).Value & " (" & i & ", " & j & ")", , "Unused Names"

        ElseIf k > 1 Then

          MsgBox Cells(1, j).Value & " (" & i & ", " & j & ")", , "Unused Names"

        End If

      End If

    Next j

    k = 0

  Next i

  If l = 0 Then MsgBox "No unused names were found.", , "Unused Names"

  Range("A1").Select
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.