# Microsoft Office Application Help - Excel Help forum > Excel General >  > [SOLVED] Why is my Excel spreadsheet so large?

## USNA91

The spreadsheet I have created for work currently consists of 9 worksheets and about 200 charts, with each chart having its own tab. 

I use it to capture and calculate trend data that is then transferred automatically to the charts which, in turn, are linked to a small handful of PowerPoint Presentations and Word files that I use to publish the results. This happens monthly and quarterly, so automating the process has saved me far more headaches than the year it's taken me to create and "perfect" the current system would suggest.

The structure and function of the spreadsheet is as follows:

- Eight of the worksheets are used to capture monthly data. Each month, my direct reports and others send me the numbers and I simply plug them into the row for that month. These sheets also have a series of calculation tables that the charts use.

- The ninth worksheet has a very small handful of cells that the spreadsheet uses as variables for calculations. Chief among them is the "as of" month, which I use to generate the charts and trends "as of" the desired month. Since the historic data in the other eight worksheets goes back from two to three years, and the charts only trend back a year or two, this cell allows me to generate historic data at the flick of a switch. This switch (selecting the month) drives the calculation tables mentioned above, which means the charts all update as soon as I enter the new monthly data and enter the "as of" month. It's a beautiful thing, really.

The problem is, the file is now 40MB in size, and takes upwards of 5 MINUTES to load!  :EEK!: 

So what is it? The charts? The calculations?  :Confused: 

At one point I saved a version that had all the charts removed. No improvement.  :Confused: 

While the delay is far from crippling, it is a tad annoying. Was there a better way to have done what I did to avoid this, or is it simply time for me to ask my boss for a new computer?

Speaking of computers, I am on a Dell Latitude D620 laptop running an Intel Core 2 at 998 Mhz with 2 GB of RAM. Windows XP Pro Version 2000, SP2, and Excel 2003 SP3. My options for playing with software are limited since this is a work PC. Unless I can get a hardware update, my options are limited to the spreadsheet itself.

Thanks!  :Smilie:

----------


## JBeaucaire

This happens frequently when cell formatting goes out of control. Try this...

On each sheet, find the last column with data in it. Select ALL the columns after that and press *EDIT > CLEAR > ALL*. Then find the last ROW with data in it, select ALL the rows after it and repeat the CLEAR.

Do this on all sheets.

----------


## USNA91

Interesting...

I have to admit that I am a Format Nazi. While I suppose that makes sense on the input tables (which one can see), I suppose it's overkill on the tables used strictly for charting?

If that's the case, I'll wipe all by the data formatting (colors, gridlines, etc.) from the lookups and leave only data formats (decimal places, date formats, etc.)

Do you think that would work?

In the meantime, I am doing what you said to ensure no excess data/formatting is around.

----------


## JBeaucaire

No...there's no need to remove formatting that you ARE using. These megabyte expansions usually come from formatting and conditional formatting applying themselves far outside of your actual work area.

Just use the CLEAR ALL trick above on the section outside of the data zones on your sheets, see if that solves your issue.

----------


## USNA91

Well, uh.....  :Confused: 

I'm trying to do this, and I'm running into the little issue that many of my rows are not un-hiding themselves when I tell them to.  :Confused:

----------


## USNA91

I finally figured out the hidden-cell glitch...... sort of.  :Confused: 

Anyway, JB was right! Apparently, some time back a copy-and-paste snafu occurred and it resulted in three or four columns of copied formulas and formats to go ALL THE WAY to the bottom of one of the worksheets (Yes, 65,000+ rows of it).

I deleted the needless formulae and formatting, and now the file loads in about 10 seconds!  :Cool: 

Thanks!  :Smilie:  :Smilie:

----------


## JBeaucaire

What did the file size drop to?

----------


## USNA91

A little over 7MB.  :EEK!:

----------


## merkity

VERY helpful - thank you!

----------


## Sherwin Tieng

I got a simple solution.

I copied a sheet then delete the original sheet saved.  boom !

----------


## benmort

> This happens frequently when cell formatting goes out of control. Try this...
> 
> On each sheet, find the last column with data in it. Select ALL the columns after that and press *EDIT > CLEAR > ALL*. Then find the last ROW with data in it, select ALL the rows after it and repeat the CLEAR.
> 
> Do this on all sheets.



Just another "Thank you" for this help.  My file reduced from 18Mb to 180Kb!  Cheers!

----------


## ExcelKnut

I had the same problem.  My file was over 17Mb.  After doing a Edit>Clear>Clear All it was 93Kb to "unused" rows and columns.  Thanks for the post....I almost went crazy!  It would have hour-glassed for hours I think.

----------


## TomCLondon

I just had a similar problem - a workbook with three sheets, and no complicated lookups, was taking up over 3Mb of space. I finally discovered that the reason the file was so big was that it had a lot of legacy named formulae in it, some of which dated back to 2004..... 

Deleting these in Name Manager reduced the file size to 128kb.

----------


## Bremaria

I had a 5mb spreadsheet and after clearing all of the unused rows/columns it shot up to 50mb!

When I was clearing the empty cells on one of the tabs I noticed the computer took a few seconds to complete the task. I deleted the empty rows in this tab and I am now at 435kb!!!

Bottom line, if "clear all" doesn't work, might try deleting the unused rows and columns instead.

Cheers,
Brett

----------


## srivastava.hrsh1

1) If you want to speed up the processing & saving of excel sheets, update your office to 64-bit version (although be mindful that some macros and other types of functions do not yet work on the 64-bit version). I work with 400 MB excel files and they take ~20-30 seconds to save/load/process formula [read a little bit online to find out if the kind of work you do on excel is supported in 64-bit format; I'd say 99% of our daily tasks are supported].

2) 1 way to decrease your excel file size would be to select all empty columns and rows, typically to the right and bottom, and delete them. Then save your file. The file size will be updated. I just did this today for a 37 MB excel file, and the size decreased to 1.9 MB :-D

----------

