Remove Styles Add-in for Excel

When using a macro on a worksheet you may receive one of these error messages.

These are an indication that the worksheet is locked – it’s not possible to perform basic formatting operations like wrapping text or aligning text. If you select a range of cells and try to unlock them by right clicking, selecting Format Cells, and on the ‘Protection’ tab unchecking the ‘Locked’ box:

. . . .you may get this error message:

This is an indication that the worksheet has been set with more than 64,000 different combinations of cell formats (font size, text color border lines, protection., etc.). See the Microsoft posting on this problem here.

You can solve the problem by using the Microsoft recommended add-in ‘Remove Styles’ available for download here. To install the download close out of all spreadsheets, and then go to File . . .Options and click on the ‘Add-ins’ tab on the left. At the bottom choose Excel Add-ins from the Manage drop down menu and click ‘Go . . .’.

This dialog box will appear:

Click on Browse . . . and select the ‘RemoveStyles.xlam’ file posted to their Codeplex site.

The add-in should install on the Home tab next to the Styles section.

Now after you select a range of cells and then click ‘Remove Styles’ the styles will be removed but the text will remain. [Copying and pasting the values did not do the trick for me on a spreadsheet I was working on this evening.]

When the styles are being removed you’ll see a message like this one in at the bottom of the window for Excel.

The 65,000 plus styles on the worksheet get deleted. When the process is done, you run macros and unlock cells normally.