5 December 2021
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.
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.
These are the most common ways that Named Ranges will appear in your spreadsheets.
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!).
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?
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:
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:
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:
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:
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:
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:
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':
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
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!