Delete too many Named Ranges with Excel's Name Manager, VBA, or Epsillion's XL Integrity tool

Published: 26 March 2025. Updated 19 May 2025.

Executive summary

One of our most widely read blog posts is Fix Excel Files With Too Many Named Ranges, because they are simultaneously powerful and useful, spreadsheet corruption risks, and spreadsheet security risks.

Read on to learn what Named Ranges are, why you want to use them, what other actions and tools create them, and how to delete unwanted Named Ranges (including two free and one paid option).

What are Named Ranges?

Named Ranges, which are also called Defined Names, are names you give a cell or multiple cells in Microsoft Excel. These names allow you to reference those cells easily in Excel formulas. This formula, for example, is easy to read because named ranges are used instead of cell references (like E2*F2):

Why are Named Ranges bad?

Named Ranges are great in moderation, but when there are too many Named Ranges your Excel workbook can become corrupted, stop calculating properly, and even expose you to privacy risks.

In addition, hidden Named Ranges can expose your company to privacy risks. Hidden Named Ranges often contain forensic details of the history of the spreadsheet that you may not want to keep, like evidence of old calculations, names of people who worked on the spreadsheet, and filepaths where the spreadsheet has lived (including home computers and company server paths). Would you want to share a spreadsheet with a client that contains this information?

Where do Named Ranges come from?

These are the ways Named Ranges appear in Workbooks, some of them desirable, some of them undesirable:

1. You may have created Named Ranges using Excel's Name Manager.
2. Custom VBA code can create Named Ranges
3. Excel addins like Epsillion, CapIQ, and FRED use Named Ranges to mark specific cells or groups of cells. Those Named Ranges are later used in the code that makes those programs work.
4. Defined names are copied with each worksheet. So if your workbook has Named Ranges, they will be copied to a new workbook if you copy one worksheet.
5. Even if you delete the worksheet you copied, the Named Ranges stay. That means the Named Ranges keep accumulating!

How do I delete Named Ranges?

We'll show you four ways to delete Named Ranges: delete Named Rangtes using Excel's Name Manager (works well for a few Named Ranges), delete Named Ranges with VBA (works well for a few thousand Named Ranges), or delete Named Ranges using our propriety XL Integrity tool (works well for tens of thosuands of Named Ranges, including Hidden Named Ranges)

Method one to delete Named Ranges: use Excel's Name Manager (free)

The easiest way to Delete Named Ranges is to use Excel's Name Manager:

It's important that you begin deleting Named Ranges before you get past about 30,000. After that, Excel's built-in Name Manager may stop functioning!.

Method two to delete Named Ranges: use VBA (free)

Sometimes Named Ranges are hidden. You can't delete hidden Named Ranges in the Name Manager. To unhide your Named Ranges, use this VBA code:

IMPORTANT: Make a spare copy of your spreadsheet before running VBA code. There is no 'Undo'!

'VBA Code: Unhide All Named Ranges

	Sub unhideAllNames()

		'Unhide all names in the currently open Excel file
		For Each tempName In ActiveWorkbook.Names
		tempName.Visible = True
		Next

		'Print message for user
		MsgBox "Congratulations! You've unhidden all formerly hidden Named Ranges."

	End Sub

Hat tip to Professor Excel for that piece of VBA.

If you'd like to count all named ranges, including a subtotal for those that were created by Epsillion and Capital IQ:

'VBA Code: Count All Named Ranges

	Sub countNamedRanges()

		'Define variables'
		Dim nm As Name
		x = ""
		y = "epsVariable"
		z = "IQ_"


		'Count occurrences
		For Each nm In ActiveWorkbook.Names
		
			rngName = nm.NameLocal
			If InStr(rngName, x) Then h = h + 1
			If InStr(rngName, y) Then i = i + 1
			If InStr(rngName, z) Then j = j + 1
			If InStr(nm.Value, "#REF!") > 0 Then k = k + 1

		Next nm

		'Increment by one
		h = h +1
		i = i + 1
		j = j + 1
		k = k + 1

		'Print message for user
		MsgBox "This workbook contains " & h & " Named Ranges in total." & vbCrLf & vbCrLf & _
		"There are " & i & " Named Ranges containing the letters '" & y & "', which is associated with Epsillion." & vbCrLf & vbCrLf & _
		"There are " & j & " Named Ranges containing the letters '" & Z & "', which is associated with Capital IQ." & vbCrLf & vbCrLf & _
		"There are " & k & " Named Ranges with #REF errors. These are useless unless you manually reset their references." 

	End Sub

If many of your Named Ranges have #REF errors (which means they are useless unless you manually reset their references), you can delete them in one go:

'VBA Code: Delete All Named Ranges with #REF Errors

	Sub DeleteNamedRangesWithREF()

		MsgBox "Click Yes to delete all Named Ranges that have #REF errors. This could take several minutes.", vbYesNo

		Dim nm As Name

		'Find and delete Named Ranges that have #REF error
		For Each nm In ActiveWorkbook.Names
			If InStr(nm.Value, "#REF!") > 0 Then
				nm.Delete
			End If
		Next nm

		MsgBox "Congratulations! You've deleted all Named Ranges with #REF errors."
	End Sub

Hat tip to SpreadsheetWeb for the code above.

If you have a lot of Named Ranges, and you know most of them are useless, you may want to delete all of them except for the Epsillion Named Ranges. Here is the VBA code to do that:

'VBA Code: Delete All Named Ranges Except Epsillion's

	Sub DeleteNonEpsillionNamedRanges()

		MsgBox "Click Yes to delete all Named Ranges except Epsillion's. This could take several minutes.", vbYesNo

		Dim nm As Name
		Application.ScreenUpdating = False
		Application.Calculation = xlCalculationManual

		On Error Resume Next

		For Each nm In ActiveWorkbook.Names
			rngName = nm.NameLocal

			If Not ( InStr(rngName, ".epsVariablesRange") > 0 Or InStr(rngName, ".epsVariable") > 0  Or InStr(rngName, "Epsillion") > 0 ) Then
			nm.Delete
			End If
		Next nm

		Application.Calculation = xlCalculationAutomatic
		Application.ScreenUpdating = True

		'Print message for user
		MsgBox "Congratulations! You've deleted all Named Ranges except those associated with Epsillion."

	End Sub

If you'd like to delete all Named Ranges except those associated with Epsillion and Capital IQ:

'VBA Code: Delete All Named Ranges Except Epsillion's and Capital IQ's

	Sub DeleteNonEpsillionNonCapIQNamedRanges()

		MsgBox "Click Yes to delete all Named Ranges except Epsillion's and Capital IQ's. This could take several minutes.", vbYesNo

		Dim nm As Name
		Application.ScreenUpdating = False
		Application.Calculation = xlCalculationManual

		On Error Resume Next

		For Each nm In ActiveWorkbook.Names
			rngName = nm.NameLocal

			If Not (   InStr(rngName, ".epsVariablesRange") > 0 Or InStr(rngName, ".epsVariable") > 0 Or InStr(rngName, "Epsillion") > 0 Or InStr(rngName, "IQ_") > 0   ) Then
			nm.Delete
			End If

		Next nm

		Application.Calculation = xlCalculationAutomatic
		Application.ScreenUpdating = True

		'Print message for user
		MsgBox "Congratulations! You have successfully deleted all Named Ranges except those associated with Epsillion and Capital IQ."

	End Sub

If you'd like to delete all Named Ranges except those associated with Epsillion and Capital IQ and those called 'Print_Titles' and 'Print_Area':

'VBA Code: Delete All Named Ranges Except Epsillion's, Capital IQ's, and those called 'Print_Titles' and 'Print_Area':

	Sub DeleteNonEpsillionNonCapIQNamedRanges()

		MsgBox "Click Yes to delete all Named Ranges except Epsillion's and Capital IQ's. This could take several minutes.", vbYesNo

		Dim nm As Name
		Application.ScreenUpdating = False
		Application.Calculation = xlCalculationManual

		On Error Resume Next

		For Each nm In ActiveWorkbook.Names
			rngName = nm.NameLocal

			If Not (   InStr(rngName, ".epsVariablesRange") > 0 Or InStr(rngName, ".epsVariable") > 0 Or InStr(rngName, "Epsillion") > 0 Or InStr(rngName, "IQ_") > 0  Or InStr(rngName, "Print_Titles") > 0  Or InStr(rngName, "Print_Area") > 0 ) Then
			nm.Delete
			End If

		Next nm

		Application.Calculation = xlCalculationAutomatic
		Application.ScreenUpdating = True

		'Print message for user
		MsgBox "Congratulations! You have successfully deleted all Named Ranges except those associated with Epsillion, Capital IQ, and those called 'Print_Titles' and 'Print_Area'."

	End Sub

Method three to delete Named Ranges: use XL Integrity (paid tool)

Some spreadsheets have so many Named Ranges that Excel's Name Manager won't open, the Excel file itself has become corrupted, and VBA cannot run on a corrupted Workbook. This happens in the latest versions of Excel when you have more than 65,536 Named Ranges, a limit chosen by Excel.

If this happens, we recommend you use XL Integrity, a proprietary Excel Addin built by Epsillion, to delete excess excess Named Ranges. XL Integrity will show you a count of Named Ranges by category and allow you to delete only the ones you don't need. The categories are:

1. Hidden Named Ranges
2. Named Ranges with errors like #REF and #N/A
3. Named Ranges with path names in them (e.g., u:/mycompany/myprojects)
4. Named ranges created by Capital IQ
5. Named ranges created by Epsillion

To use XL Integrity, email us and we'll get back to you right away: