I recently had a weird problem with an Excel doc that jumped in size after making some changes and saving a new revision. It was a kit list/pricing spreadsheet I'd been working on for a while so couldn't pin down the exact action that caused it to increase in size.
In this instance I was using Excel 2007 and saving the doc as an Excel 97-2003 Workbook.
After some Googling I tried the following fixes, the last of which solved my problem:
Inspect the document
Prepare > Inspect Document > Inspect
This shows a list of hidden content including rows, columns and worksheets and if it find any, allows you to delete them.
Force everything on a sheet to be deleted
Press ctrl-a, right click anywhere in the selected worksheet and click 'Delete'
This was overkill for what I was trying to do as I needed to keep the content I could actually see, but it was really useful to go through a copy of my dodgy spreadsheet and clear every tab and once done the file was still huge, so there was still work to do...
Manually delete components in the file
Warning, this may destroy your data so make sure you have a backup of the file first!!!
Save the spreadsheet as an .xslx file. Rename the extension to .zip. Open the file in WinZIP. Look for an xl\externalLinks folder and delete any files in this. Close WinZIP and rename file back to .xlsx, open in Excel and allow Excel to repair the file.
This is great as it shows you exactly where bulky data is stored in the file. In my case WinZIP showed the xl\externalLinks folder contained some bulky data. I dropped one of the .XML files into Notepad and could immediately see that it the entire contents of a Cisco pricelist I'd pasted some content from! I would have only pasted a couple lines from this other spreadsheet so Excel must have brought the whole lot across for an unknown reason but decided not to acknowledge this anywhere. Once I delete the files in the xl\externalLinks folder and saved it as per my instructions above my file was back to a sensible size.
Wednesday, 28 July 2010
Subscribe to:
Posts (Atom)