Why is my Excel spreadsheet so large?

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!

So what is it? The charts? The calculations?

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

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!

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.

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.

What did the file size drop to?

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