Friday, March 23, 2007

Clear Excess Formatting in Excel Files

Microsoft have a very interesting tool on their website.

The Excel Excess Formatting Cleaner Add-in program removes all formatting that has been applied beyond the cell range that contains data, or beyond the cell range that is covered by shapes, in each worksheet in the workbook. This reduces the file size of a converted Lotus 1-2-3 file or of a file that has formatting applied beyond the data range.

You can also use this add-in program to resolve various printing and viewing problems that are caused by extra formatting that is applied outside the last cell (as defined by the UsedRange property to determine which cells in the worksheet contain data).
Once installed you can find the application in the File Menu under Close.
This worked brilliantly for the files I tried it on with significant file size decrements of 25%-60%.
As far as I can see the only forseeable problem with this is that sheets that are preformatted to take pasted values (eg. via a macro) could lose their formatting.

Labels: , , ,


Posted by: Jethro | 2:59 PM |