The style reduction tool for Microsoft® Excel® analyzes and processes Excel spreadsheets in order to fix the hardcoded limit of formatting styles per spreadsheet, several thousands. If you use formatting intensively, then you have probably bumped on this blocking "too many different cell formats" error :

Excel can only store a limited number of formatting styles, hence the error

...also, Excel incorrectly keeps growing the amount of named styles.

The style reduction tool does two things. It removes duplicates in existing formatting styles. And, alternatively, it performs a drastic compression of similar formatting styles. By arbitrarily reducing the number of formatting styles you can freely work with those spreadsheets again.

Note : if you are using a French version of Excel, the corresponding error message is : "Nombre de formats de cellule différents trop élevé".

The user interface of the style reduction tool.

System requirements

• The product works with Microsoft® Windows® 98 / NT4.0 / 2000 / XP / 2003 / Vista / 7. Operating system language : does not matter.
• The product works with Excel : 97 / 2000 / XP / 2003 / 2007 / 2010. Excel product language : does not matter.
• Supports XLS and XLSX files.

Once downloaded, double-click on the .exe file to start the install process. If you are running Windows Vista or Windows 7, please right-click on the .exe file and choose "Run as administrator".

An example of style reduction is provided in the installed package. File example.xls has 4037 different formatting styles and thus if you open it in Excel, click on cell Adam county and try to make it italic, you'll get an error message. In other words, it is virtually impossible to edit this spreadsheet. File example_removedduplicates.xls is a processed instance of that file in which 568 duplicate formatting styles have been removed. The style reduction tool lets you know, from any spreadsheet, how many formatting styles are stored (4037), and how much of these are duplicates (568). Duplicate formatting styles are unnecessary and can be removed. File example_compressedsimilar_20.xls is a processed instance where instead of simply removing duplicates, we want to go further and compress 20% of similar formatting styles. This makes a lot more room and we end up with only 3234 formatting styles. With file example_compressedsimilar_50.xls we go even further and compress 50% of similar formatting styles to end up with only 2053 styles. Note that compression can be up to 95%. The more compressed the spreadsheet is, the more destructive is the processing in regards to the original formatting.

If you are using the trial version, the tool performs only a fraction of formatting style reduction. To get the full work done, a license must be purchased.

Step 1 : simply click the Analyze button, this analyzes the corresponding Excel file and produces a quick report. This listing tells, for each file, how many different formatting styles there are, and how many of these are duplicates.

Step 2, remove duplicates : if there are duplicate formatting styles, then you probably want to remove these. Just click on Remove Duplicates in order to process all of the files. Note that, if you are running a trial version, the removal is only partial. After the files are processed, you get a message telling how many of these were processed, and then you can individually view those processed files by either double-clicking on these on the list, or right-clicking and choose the appropriate option :

Processing results

• Open processed workbook in Excel : this opens the file once it is processed. You can open the original and processed files in Excel and view them side by side, compare them (in Excel, click on Windows in the menu bar and select the Arrange... option.
• Open original workbook in Excel : this reopens the original file.
• Open working folder : this opens an explorer window where you can grab the file once it is processed. Note that the file is prefixed with Processed. If the original file is called myfile.xls, then the processed file is called Processed.myfile.xls

Note that, if the Work on local copies option is checked, which is the case unless you change it, you'll be able to view either the original spreadsheet, or the processed spreadsheet, alternatively. Whereas if the option is unchecked, the processing does really occur on the original spreadsheet, and thus you can only see the processed spreadsheet.

Or Step 2, remove similar styles : if you'd rather go beyond the simple removal of duplicate formatting styles, you can really ask the tool to do a compression of similar formatting styles. When you click Remove similar styles, the tool uses the compression level (2% by default) in order to decide how to group similar formatting styles. The higher the compression level, the more drastic the processing and you'll end up with fewer formatting styles. Just like the simple removal of duplicate formatting styles, as soon as the processing is finished, you get a message with how many of the files were processed, and you can then right-click on any file to view the processed or original spreadsheet.

If you'd like to retain a processed spreadsheet, just right-click on it and choose Open working folder, then it should be easy to take the file from the explorer window that opens.

Because the trial version is such that the tool will only do a fraction of its work, you probably want to purchase a license. The price of the license is $40.90 USD (the price varies according to the EUR/USD rate). Just click the Purchase button on the top-right corner of the user interface, then proceed. Plimus is a payment processing partner which does online payment processing. You can purchase a license of the style reduction tool from the product order page, which will return a registration number. Just enter this registration number in the registration box and restart the product. If you are properly registered, the purchase button should go away and you should see registered license instead. If you are having troubles, you can contact ARsT Design at . Consider two business days for an answer. If you cannot reach ARsT Design by email, you can post in ARsT Design's forum, alternatively.

The license is an end-user license. It does not include right to redistribution, leasing, ownership transfer in any form or fashion.

• July 25, 2006 - first release
• Oct 17, 2006 - update : fixed a problem with number formats neutrality when using the "Remove Duplicates" functionality. Also made it simpler to make side by side comparisons in Excel now that processed files are prefixed with "Processed." in order to disambiguate file pairs.
• Nov 2, 2006 - update : fixed the remaining lack of neutrality of the "Remove Duplicates" feature.
• Nov 30, 2006 - update : improved memory consumption by 66%. Also improves speed.
• Jan 2, 2007 - update : fixed preservation of any Auto_Open() VBA macro.
• Jan 4, 2007 - update : fixed preservation of ptgArray in defined names.
• Jan 5, 2007 - update : a lot less memory consumption when the tool is used intensely.
• Jan 5, 2007 - update : fixed the behavior that would cause Excel to recreate a number of styles removed by the tool. This was caused by a number of old style references that were left in custom named styles.
• Mar 2, 2007 - update : fixed preservation of an undocumented flag in defined names that caused defined names to duplicate themselves many times.
• Jun 7, 2007 - update : added French user interface (for French Windows version)
• Jun 19, 2007 - update : fixed the preservation of internal SUPBOOK records.
• Jun 25, 2007 - update : slightly more verbose if input file raises an error.
• Jul 6, 2007 - update : added support for *.xlt, *.xlm, *.xla files
• Jul 8, 2007 - update : make sure to report appropriate error if an old Excel 95 file is being used
• Aug 12, 2007 - update : preserve some more internal flags in Excel files
• Oct 3, 2007 - update : preserve a case of data validation object with no rule
• Nov 28, 2007 - update : case with a large number of external sheet references
• Feb 23, 2008 - update : fix for incorrect handling of far east strings (Japanese)
• Sep 15, 2008 - update : fix for malformed spreadsheet footers
• Oct 12, 2008 - update : three additional compression ratios, 2%, 7% and 15%
• Oct 12, 2008 - update : compression algorithm gives bigger weight to background color in cells, to avoid too obvious coupling by color
• Dec 18, 2008 - update : fix for preserving default conditional formats
• Jan 8, 2009 - update : fix for preserving custom views
• April 17, 2009 - update : advanced tool options are not in the front dialog anymore to avoid disturbance
• July 1, 2009 - update : fix for incorrect font records
• July 21, 2009 - update : added experimental support for XLSX files
• Aug 4, 2009 - update : fix to make sure to preserve style-type XFs
• Aug 7, 2009 - update : improved performance for large XLSX files (with many defined names)
• Aug 15, 2009 - update : additional error message when the output file is already opened in Excel
• Aug 24, 2009 - update : fix for internal shared formulas in XLSX files
• Oct 9, 2009 - check : successful tests on Windows Vista and Windows 7
• Nov 25, 2009 - update : better preserve tab colors
• Dec 5, 2009 - update : better preserve defined names with limited scope
• Dec 10, 2009 - update : fix for internal hyperlink records
• Dec 12, 2009 - update : user interface made easier by adding respective columns for input and output file names.
• Dec 17, 2009 - update : better preserve an internal flag of conditinal formattings
• Mar 26, 2010 - update : better preserve header/footer pictures
• Apr 28, 2010 - update : compatibility with Office 2010
• May 15, 2010 - update : fix for null strings in XLSX files
• June 25, 2010 - update : fix for preserving distributed alignments in XLSX files
• Aug 2, 2010 - update : fix for preserving internal thickTop/Bottom flags in XLS files
• Aug 2, 2010 - update : improved col width preserving in XLSX files
• Aug 2, 2010 - update : improved customized built-in number formats in XLSX files
• Aug 5, 2010 - update : improved preservation of local user defined functions in XLSX files
• Sept 22, 2010 - update : improved preservation of data validation pick lists
• Oct 14, 2010 - update : improved preservation of zoom and formula flags in XLSX files
• Oct 22, 2010 - update : improved handling of [STYLEEXT] records in XLS files created by Excel 2007/2010
• Oct 25, 2010 - update : additional option related to include or not records from transitional XLS/XLSX. Checked by default.
• Nov 4, 2010 - update : user interface option "Compress similars" becomes "Remove similar styles"
• Nov 11, 2010 - update : 20% speed performance improvements for processing XLSX files.
• Nov 24, 2010 - update : fix for the compression ratio option which was not always taken into account.
• Mar 12, 2011 - update : better handling of [XFEXT] records in XLS files created by Excel 2007/2010
• Apr 3, 2011 - update : exposure of unused styles. At this point, this exposure is purely informative. In the future, unused styles will allow to further reduce the amount of styles.
• July 21, 2011 - update : better handling of embedded OLE objects
• July 27, 2011 - update : better handling of internal calculation flags to avoid recalculations on open
• Aug 6 27, 2011 - update : better handling of internal workbook flags
• Sept 11, 2011 - update : better error handling of license activation. Friendlier error message and better control of what actually occurs when it fails.
• Sept 13, 2011 - update : new option for reducing duplicate named styles in XLSX files. The option is checked by default.
• Sept 29, 2011 - license : introduction of a Site-wide license meant to make the tool's licensing more manageable for large companies.
• Oct 11, 2011 - update : performance improvements for processing XLSX files. 30% less memory used and 40% faster.
• Dec 20, 2011 - update : bug fix for overlapping shared formulas in XLSX files.
• Jan 7, 2012 - update : drag and drop support. Users can now drag and drop their files onto the user interface.

The style reduction tool is a product from ARsT Design, an independent software developer. ARsT Design can be contacted at the above email address.

This tool can be found in the Office marketplace here. If you think it solves your problem, please vote for it.