Why multiple monitors might be killing your spreadsheets

Ever get the warning that Excel has “run out of resources” or “too many formats” or you simply can’t paste into it anymore. There’s a glitch in Excel 2010 and 2007 (fixed in Excel 2013)

Am I affected?

Here’s how to check. Open your Excel Files (your important ones), go to the Home Ribbon and click on the drop down for Cell Styles.

 

These “corrupted” styles often begin when you paste data into Excel from other systems, however it usually doesn’t cause much of an issue.

So what makes it a problem and what does this have to do with multiple monitors?

When you have two monitors it often makes sense to have two “sessions” of Excel open so that you can compare them on separate screens. i.e. you have started Excel twice. The temptation is then to copy and paste between sessions, and this is where the problem starts.

Each time you copy paste between sessions it also brings across ALL of the corrupted cell styles. This happens even if you bring across just one blank cell. If you use the same spreadsheet on a regular basis and copy data into it daily, weekly, monthly then this issue gets compounded each time. Eventually you will get performance issues and finally you will no longer be able to paste into that file.

So what do I do?

Prevention:

Never copy between “sessions” in Excel 2007 or 2010 (all fine with Excel 2013). Instead open up the files you want to use from within the same session if you are copying and pasting.

Cure:

1. Macro
If your sheet isn’t too corrupted we have a macro that can restore cell styles to the default setting.

Download it below and then follow the instructions. ALWAYS make a back-up of your file before running anything like this.

2. EXE file
If our macro can’t help you (i.e. your file is “too” corrupted) then I’ve used this tool successfully many times in the past. XLCleanerDotNET4.0

However, we strongly advise you consult your IT department before installing this.