If you have a problem with your Excel spreadsheet being extremely slow, you may have an issue with hidden name ranges. So if you find that a simple move from one cell to another with the arrow key takes longer then you want, consider this solution below.
This is particularly likely where the spreadsheet you are working with is a Frankenstein model i.e. bits of pieces of other models that have been moved/ copied in. Unbeknownst to you, it pulls all sorts of other information out of the source spreadsheet other than just the copied sheet. Especially if it seems there are some links to websites, often finance related.
Finding the hidden name ranges
This can be difficult. We recently had a client where the spreadsheet was extremely slow for no obvious reason. It was only about 5MB. It contained some data tables but the slowness persisted even when these were deleted.
At that stage we ran ExcelAnalyser and the ‘Names’ report showed thousands of named ranges as shown below.
When we went to the Name Manager this is all that we saw which didn’t make sense.
Solution to extremely slow Excel spreadsheet with hidden names
A quick search on the internet discovered this great site below which addresses the issue of hidden named ranges and provides some simple VBA code to either
- immediately delete the hidden named ranges or
- make the named ranges visible in Excel’s name manager.
We used the second option of making them visible first so we could see what they were. It was then easy to delete and immediately the spreadsheet was more responsive.