Solved Excell .xls File Growing Fast

Microsoft Office 2007 professional (aca...
September 18, 2015 at 06:40:47
Specs: Windows 7, Pentium 4 2GHz / 4 gigs.
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!

See More: Excell .xls File Growing Fast

Report •


✔ Best Answer
September 20, 2015 at 12:39:30
Visit this great source for additional information. Maybe will find something useful in that issue. https://answers.yahoo.com/question/...
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.


#1
September 18, 2015 at 06:51:38
Is the computer behaving normally in other respects? If there is a virus or malware around this can mess up all sorts of things.

Try running these three freebies in the order given as they often unearth things that anti-virus programs miss:

AdwCleaner:
http://www.bleepingcomputer.com/dow...
(blue Download button near top - not anything else on the page).
Download and "Save" the file somewhere. Go to the saved file then double click it to run the program. Use the "Scan" button, followed by the "Cleaning" button.

Junkware Removal Tool (JRT)
http://www.bleepingcomputer.com/dow...
(blue Download button near top - not anything else on the page).
Download and "Save" the file somewhere. Go to the saved file then double click it to run JRT. It might appear to have stopped at times or flash the screen but sit tight until it has finished.

MalwareBytes:
https://www.malwarebytes.org/downlo...
Download the free version.
Install and Run the program but before doing its Scan go to "Settings > Detection and Protection" and put a checkmark in "Scan for rootkits". Quarantine anything it finds.

If anything is found please copy/paste the associated log on here.

Always pop back and let us know the outcome - thanks


Report •

#2
September 18, 2015 at 07:20:05
Try deleting all of the unused Columns & Rows and see if it helps.

It is approximately 200 rows x 60 columns.

Go to Column BI, the 61st Column
Press CTRL-SHIFT-RIGHT ARROW
That should select all of the Columns from BI to XFD
Press CTRL-SHIFT-DOWN ARROW
That should select all the Rows from 1 to 1048576
Press the Delete Key

Next go to Row 201 and do the same.
Select all the Columns & Rows and Delete.

See if that helps.

MIKE

http://www.skeptic.com/


Report •

#3
September 18, 2015 at 08:22:52
Is the file local to your HDD, or is it on a network share somewhere?

Report •

Related Solutions

#4
September 20, 2015 at 12:39:30
✔ Best Answer
Visit this great source for additional information. Maybe will find something useful in that issue. https://answers.yahoo.com/question/...
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.

Report •

#5
September 20, 2015 at 12:53:09
In other respects, the computer behaves normally.

Report •

#6
September 20, 2015 at 12:54:48
file stored local to my HDD.

Report •

#7
September 20, 2015 at 12:55:27
Thank you all for your advice! The issue has been resolved.

Report •

Ask Question