Have you ever had that sinking feeling when a colleague sends you a spreadsheet with the request to “just take a quick look at this”? And you find the spreadsheet takes ages to open, seems to have lots of strange links (although Excel lists only a few on the Data tab), and runs very slowly even though it’s not that complicated?
Well, the new Deep Clean function in version 12 is designed to help with exactly this kind of situation. So we were delighted recently when one of our users found that it was able to clean up a “spreadsheet from hell” that he had received from a colleague in another country.
You can see from the composite screenshot above that the spreadsheet was initially nearly 3MB in size, with over 3,000 custom formats and more than 2,000 defined names. And although it had only 3 visible links, it took about 5 minutes to open because of dozens of “phantom links” (not visible in Excel).
In fact nearly all the defined names and custom formats were nonsense, just the legacy of years of use and alteration by multiple different users. And as you can see from the screenshot above, Rainbow’s Deep Clean cleared out nearly 170KB of custom formats plus 698 defined names containing phantom links. And then the Names Toolbox function deleted a further 1,396 names containing #REF! errors.
The result of all this was a spreadsheet less than 1MB in size, with just 47 custom formats, 206 defined names, and 1 (genuine) link. And our user reported that it now opened instantly (instead of taking 5 minutes)! And in case you’re wondering about the “+” at the end of the filename in the second Summary Report, this is just because after Deep Clean, Rainbow saves the cleaned-up spreadsheet as a different file.
So if you’re wondering what to do with a problem spreadsheet like this, why not try Rainbow’s Deep Clean?