Thinking about it
|
| posted on 20/3/12 at 09:46 PM |
|
|
Excel file size
I have just done what seems a simple purchase ledger in Excel 2010.
It contains 14 worksheets, simple caculations, totals and vat. I have protected some cells and has a simple tint fill.
It has ended up as a 34MB file WTF????????????????????
Is that normal?
|
|
|
|
|
britishtrident
|
| posted on 20/3/12 at 10:30 PM |
|
|
Known problem do a search on Excel File Growth
[I] “ What use our work, Bennet, if we cannot care for those we love? .”
― From BBC TV/Amazon's Ripper Street.
[/I]
|
|
|
jamesbond007ltk
|
| posted on 20/3/12 at 10:33 PM |
|
|
In a word no!!
It is however all too common, especially with Excel 2007 and it would appear with 2010.
It is normally due to formatting that has become corrupt. Your first step towards fixing it is to identify which worksheet is responsible for the huge
filesize. It could be more than one but normally it is only one.
Please try the following: Note I am writing this as an Excel 2007 procedure but it should be similar for 2010.
1) Save as - Other Formats - Webpage (*.htm; *.html) - Make sure the radio button next to "Entire Sheet" is marked, navigate somewhere
sensible, call is something equally sensible then click save. You may well see an error message regarding parts of the workbook not being compatible
with a webpage, just click Yes.
2) Navigate to where you saved the webpage and you will see an .htm file and a folder with a "_files" suffix. Open the folder.
3) In the folder you are looking for the .htm files called sheet001.htm, sheet002.htm. Set the explorer view to details and then sort by size. You
should notice one or more files has a massive file size compared to the others.
4) Assuming you are able to identify the offending sheet using the above method you now need to work out which sheet is which. If you have not renamed
your sheets then its simple, sheet 1 is sheet 1. However, if you have renamed and rearranged the sheets it may not be very easy to identify.
5) If you need to identify which tab the offending "Sheet00#.htm" file corresponds to press Alt+F11 to open Visual Basic. On the left hand
side of the visual basic window you should see the project explorer, if not click view then project explorer, which lists all sheets by name and
number.
6) Now what to do with the offending sheet. If it is a fairly simple sheet I would be tempted to delete the the sheet all together and start on
another sheet, by adding a sheet and not reusing. If it is more complicated you may have some luck at clearing all formatting on the sheet, then
adding it back in. You could also try making a copy of the sheet, deleting the original and renaming the new to suit.
Hope that is of help and works for you
Rich
|
|
|
