Excel has file big size

15 pts.
Tags:
Microsoft Excel
VBA
Hello, I have an .xls files I open with Excel 2007 every day. It is approximately 200 rows x 60 columns. Each day we create a copy of this file, and in the new copy, change the numeric value of 20 cells. No new columns or rows are added, there are no graphics/charts/etc. There is only one sheet. 10 days ago, the file size was 1.7MB. The next day it grew to 3 MB. The following day 7. Today the file is 20 MB and working with it is very slow. It takes approximately 10 minutes to open, 10 minutes to save, and clicking anywhere in the file has a 30 second delay. Sharing is not enabled on this file.  I'm not sure what the issue could be, any suggestions.  I've tried an "Open & Repair" but no luck. It also doesn't link to an external file, and it doesn't have any VBA code. Thanks!
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

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
the network.

Also you can try fix it via Excel
Recovery Toolbox

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.

https://social.technet.microsoft.com/Forums/office/en-US/35130e9a-4bae-45dd-bdb6-f71e0ac772f4/corrupted-excel-2007-file-assist?forum=excel

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Never seen that issue personally, does seem very strange if you say nothing was changed.

    Thanks for the tips CharlieBrown..
    129,440 pointsBadges:
    report
  • Subhendu Sen
    If possible, open the same file on another computer and see what happens.
    134,420 pointsBadges:
    report
  • RossHutton
    Thank you all for your advice! The issue has been resolved.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: