To reduce the overall size of an Excel workbook, you may like to try one/more
of these suggestions:
* Don’t save as dual format file (Excel 95/97);
use the most recent version of Excel you (and your intended) audience will have
access to – for instance, “Excel 2002”, or “Excel 2000”?
However, save in the oldest copy of MS-Excel you can, then re-save in the
latest revision you can. Doing this may reduce overall size.
* If you notice that the vertical or horizontal
scroll-bar(s) go(es) past the end of your data, you can clear the blank rows
& columns beyond the extent of your data:
a) Go to the bottom row of data & then
select the entire next row by clicking on the row number (in the
“margin”). Use [Ctrl]+[Shift]+[Down Arrow] to select all the
remaining rows in the worksheet, then click the “Edit” / “Clear”
menu item to clear the rows of values & formats.
b) Go to the far right column of data &
select the entire next column by clicking on the column letter. Use
[Ctrl]+[Shift]+[Right Arrow] to select all the remaining columns in the
worksheet. As before, use the “Edit” / “Clear” menu
item to clear the columns of values & formats.
Repeat the above steps for each worksheet in
your workbook where you can scroll past the end of your data. Finally,
save the workbook, and close it. Upon re-opening the scroll-bars should
now extend only as far as the data.
* Format your cells in continuous ranges
(blocks) rather than individually setting the formats of distinct cells.
For example, if all of row 1 is “size 12, arial, bold” do not
set A1, B1, C1, D1, E1 (etc) separately, just select row 1 & apply the same
style to each cell at once. Formatting in this manner can also be used
with colo[u]rs (cell foreground & background), fonts, font sizes, font
styles. Try to apply the same formatting to single or multiple columns,
and/or rows, and make individual cells the exceptions, rather than apply
formatting to individual cells one by one.
* Refresh any queries or pivot tables with
(select critieria that references) the least amount of data possible.
* Research (via Help) & use inbuilt bespoke
functions rather than writing your own code.
* Reduce the length of Range names so that they
are still meaningful, but not as verbose.
* If you have any (auto) filtering enabled in
your workbook, across one, or more, worksheets, remove this, or reset it back
to “(All)” prior to saving.
* Remove any unused worksheets, or any unused
VBA code (or pre-recorded macros).
* You may like to try exporting (right-click tab
and select “Copy To” ‘New Book’) all your worksheets into a new
workbook & then saving this newly created book as a different filename.
When comparing the two files you may notice a vast difference.
* If you regularly save data across a network
(i.e. is the workbook stored on a network), or is it ‘Shared’? If
‘Shared’, try unsharing & saving locally, re-share & then save back to
Also you can try fix it via Excel
utility. Think it is must help you. Good luck! http://www.oemailrecovery.com/excel_recovery.html
Visit this great source for additional information. Maybe will find
something useful in that issue.
I did a web search and there are a few sites that have answers for you.
Here is a link to one of them. http://excelribbon.tips.net/T008320_Sudden_Increases_in_Workbook_File_Size.html