Excel file size

Here is a copy of an answer I gave in a different forum.

If you Hit Ctrl & End and the cell selected is way past you actual worksheet, then you probably have formats in unused cells.

Just for an example exercise:

Open a new Workbook.

Make a small Excel Table 3 rows down and 3 columns accross. Select A1 and hit Ctrl & End keys. This will select C3.

Select E5 and click the Align text left icon, but do not enter anything into that cell.

Select A1 and then hit Ctrl & End Keys. E5 is now selected, even though it is empty.

Do the same for F100, you will see that F100 is now the end of the sheet.

Another indication of a sheet being to large is the size of the scroll bars. If they are smaller than you think they should be.

Select the very last Cell on the sheet and set the format but leave it empty.

Select A1, you see you only have a 3 x 3 table but look how small your scroll bars are.

Save the workbook as Test or anything you want.

Make it small again.

Select Column D by clicking on the D Use the keys Shift & End & Right Arrow key to select all the way to the last column. Select the Clear Icon, in xl'07 it is located on the Home Ribbon. Select "Clear All"

Note: The delete key will not work for this excercise, the delete key only clears the contents

Select Row 4 by clicking on the 4, Use the keys Shift & End & Down Arrow key to select all the way to the last row. Clear the rows the same way as you cleared the columns.

You will see that the scroll bars are still small. Hit the save button and they should instantly become the proper size again.

Select A1 and hit Ctrl & End, C3 should be selected.

So in a nutsheel.

"Clear All" the Empty Columns

"Clear All" the empty Rows

Resave sheet.

You mentioned that you have formulae in the file. Maybe you can turn your calculation mode to Manual instead of automatic. So that everytime a change is done to your data, you can trigger the calculations using the F9 key on your keyboard. This will not reduce the file size but will reduce the time taken to perform any action on the file.

The biggest size bloaters I have seen over time are excessive formatting, use of formulas, and unused space. If the file is saved as .xls then considering converting to .xlsx or .xlsm depening the need for macros because these file types are compressed. One last thing I have found that works is to create a completely new workbook, copy and paste the used ranges to it, not the worksheets and create a new file. Files sometimes seem to grow just by using them regularly for no apparant reason.

Hope this helps.

abousetta

Do you have pivots in the file? They do take up sizeable space.

I agree with abousetta

Excessive formatting should be reduced or eliminated to reduce file size.