Clean up problem spreadsheets with Deep Clean

Clean up problem spreadsheets with Deep Clean

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?

Rainbow 12.0 brings new functionality

Rainbow 12.0 brings new functionality

We’re proud to announce the release of Rainbow version 12.0, which brings more new features than we can cover in a single post. Some of the highlights are: Analysis options in Risk Map and Logic Analysis increased from 36 to 48. Recognition of new features in Excel like dynamic arrays and threaded comments. New functions… Continue Reading

Usability improvements in Rainbow 11.4

Usability improvements in Rainbow 11.4

With Rainbow 11.4 our main focus has been on usability rather than new functions. We’ve gone behind the scenes to optimise some of the user interface code, and you’ll now find that Rainbow loads more quickly, and starts individual functions more smoothly and with less screen flicker. You’ll also notice that you can scroll up… Continue Reading

Corporate integration in Rainbow 11.3.1

Corporate integration in Rainbow 11.3.1

With Rainbow 11.3.1 we’ve added some further features that will help Rainbow integrate smoothly into a large-corporate environment. In particular, we’ve given you control over two of Rainbow’s functions (Check For Updates and Uninstall) that could potentially cause problems in a corporate IT environment by (a) running a VBScript file and (b) modifying the corporate… Continue Reading

Quickly undo changes with Rainbow 11.2

Quickly undo changes with Rainbow 11.2

Rainbow has offered an Undo Changes function for many years, and we made it much more visible in the recently redesigned menu (in version 11.0). However, as you probably know, it was only able to operate if you had already used the Save Format function to create a “format restore point” to which Rainbow could… Continue Reading

Fix merged cell problems with Rainbow 11.1

Fix merged cell problems with Rainbow 11.1

If you ask any professional Excel developer about merged cells, they will almost certainly say something that can’t be repeated here! Merged cells can produce incorrect calculation results, prevent sorting and filtering in data tables, cause errors in macros, and waste large amounts of time when spreadsheets have to be updated. Of course if your… Continue Reading

Manage all your spreadsheets with Rainbow 11.0

Manage all your spreadsheets with Rainbow 11.0

You may have already noticed that with the release of Rainbow version 11.0 there are now two editions of Rainbow that you can download from our Home page: Rainbow Analyst Professional (Rainbow Pro) and Rainbow Governance. And if you download Rainbow Governance after having used an earlier version of Rainbow Pro, you will immediately see… Continue Reading

Find more hidden errors with Rainbow 10.1

Find more hidden errors with Rainbow 10.1

Rainbow has always been about finding hidden errors, and version 10.1 introduces a new analysis option (in Risk Map and Formula Scan) that detects error values lurking hidden in your IF() statements. A simple example would be where cells B3 and B4 contain the value 0, and B5 contains the formula “=IF(B3>0,B3/B4,0)”. In this case… Continue Reading

Rainbow 10.0 adds perpetual licence

Rainbow 10.0 adds perpetual licence

For Rainbow 10, we’ve responded to requests from a number of users for Rainbow to include a perpetual licence. So all Rainbow version 10 licences will now allow you to continue using the same version of Rainbow even after the licence period has expired (but you will not be able to install any updates released… Continue Reading

Back to topAdministration