Fix Excel Files With Too Many Named Ranges

5 December 2021

Who Is This Article For?

Is your spreadsheet feeling clunky? Does it take a long time to save? Do you have to wait several minutes for Epsillion to process your spreadsheets? Has Excel's Name Manager stopped working? Have you noticed your Excel spreadsheet is no longer calculating properly?

If so, you may have an excessive number of Named Ranges in your spreadsheet. We consider 10,000 Named Ranges to be a lot. 20,000 and up is excessive. 30,000 and up is the extreme danger zone: your spreadsheet is at risk of corruption.

Read on to find out what Named Ranges are, where they come from, what the dangers of excessive Named Ranges are, and how to delete the excess Named Ranges.

What Are Named Ranges?

Named Ranges (also called Defined Names) are names you give to cells or groups of cells in Microsoft Excel. This then allows you to reference those cells easily in Excel formulas. Look how readable this formula is:

Software programs and Excel Addins also use Named Ranges. They make it possible to automatically perform actions, like formatting cells and updating data.

Named Ranges are therefore incredibly useful! We use them all the time at Epsillion. Unfortunately, there is also a darker side to Named Ranges. Read on to learn more.

How Did These Named Ranges Get Into My Spreadsheet?

These are the most common ways that Named Ranges will appear in your spreadsheets.

You may have created Named Ranges using Excel's Name Manager.
Custom VBA code can create Named Ranges
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.
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.
Even if you delete the worksheet you copied, the Named Ranges stay. That means the Named Ranges keep accumulating!

Named Ranges you create yourself are typically fine. Named Ranges created by well-programmed VBA or well-programmed Microsoft Excel Addins (like Epsillion) are also typically fine, because they generate a small number of well-targeted Named Ranges.

Unfortunately, some Excel Addins do not respect Microsoft Excel's natural limits. These Addins create tens of thousands of Named Ranges, and that breaks Excel. The problem is compounded when sheets containing tens of thousands of Named Ranges are copied from one workbook to another, because the Named Ranges accumulate unchecked.

In other words, the most common culprits of excess Named Ranges are importing sheets from clients’ workbooks (which tend not to be as well cared-for as your own), custom VBA (not ours!), and Excel Addins (not ours!).

How Many Named Ranges Can I Have?

Short answer: Stick to 10,000 or fewer Named Ranges. 1,000 or fewer is even better!

Longer answer: Epsillion will noticeably slow down after 10,000 Named Ranges. Excel may stop working properly at 32,768 Named Ranges.

Microsoft doesn't officially define a limit to the number of Named Ranges you can have. However, some power users have found that Microsoft Excel supports between 32,768 and 65,536 named ranges. In our own tests with Microsoft Office 365, we've found that 65,536 Named Ranges is likely to be the upper limit for most modern versions of Microsoft Excel. Still, that many named ranges is unlikely to be needed, and is generally a sign that a rogue Excel Addin (not ours!) has created Named Ranges with reckless abandon. Why risk it?

What Happens When You Have Too Many Named Ranges?

Named Ranges in Excel are wonderful. However, problems arise Named Ranges accumulate without restriction. While a few thousand is okay, some Excel Addins create Named Ranges in seemingly unlimited quantities.

When you have too many Named Ranges, several bad things are likely to happen:

Excel files become corrupt
Excel’s built-in Name Manager stops working (you will no longer be able to open the Name Manager)
Some of Microsoft’s Application Programming Interfaces (APIs) stop working
Some VBA scripts stop working
There is a risk that Microsoft Excel will stop calculating correctly (even though it may appear all is working well); and
Epsillion cannot process these corrupted files.

How Can I Delete Named Ranges?

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!.

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

Post Script

In case you're wondering, 32,768 is 32 bits raised to the power of three, and 65,536 is 32,768 is 32 bits raised to the power of three and multiplied by two. Excel's built in Named Range limitations therefore appear to be relics of the 32 bit computing era, and seem not to have been updated for a while!