File size suddenly increased from 0.5mb to 20mb | Excel forum

Resolved · Urgent Priority · Version 2007

Mon 19 Dec 2016, 08:25 Delegate Francesca said...

Francesca has attended:
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course

File size suddenly increased from 0.5mb to 20mb

My file had lots of validation, vba code and a macro, formatting, a hyperlink, a few images, hidden tabs with lots of formulas in them, (no charts of pivots) , and was 0.5mb. Two hours later I had made a few small changes at saved again and it was 20mb.

I have undone all the changes I can remember doing and tried all the soultions I've found when googling the problem (save as non xlsm, remove formatting, delete hyperlink, deleting unused cells and columns).

Is there any way I can fix the file size, or find out what caused it to increase dramtically?

Thanks

Mon 19 Dec 2016, 12:17 Trainer Doug said...

RE: File size suddenly increased from 0.5mb to 20mb

Hi Francesca

Thanks for your question.
Did you manage to reduce the file size in the end?
It's difficult to identify without seeing the file.
Can you check what is the last cell in each of the worksheets by pressing Ctrl=End key?

Regards
Doug
STL

Mon 19 Dec 2016, 12:52 Delegate Francesca said...

RE: File size suddenly increased from 0.5mb to 20mb

Hi Doug, thanks

I had already checked the last cell using Ctrl+End and it always took me to the expected cell.

I think I have somewhat figured it out. I have ~20 columns in one tab with conditional formatting and validation, which differs for each column. Dragging the formats/validation down to the last cell (row 300 in my sheet), one column at a time, seems to have increased the file size. However, if I select the entire top row and drag down (i.e. all columns at once, instead), the file size does not increase.

My main issue is that seemingly, once a file size increases, very little can be done to reduce the size again, bar deleting absolutely everything. However, I am building up and earlier version of my document to match what my most recent version was, and am having no trouble.

Seems almost to be something of a bug in excel rather than something I did.

Thanks again for your quick response

Mon 19 Dec 2016, 14:48 Trainer Doug said...

RE: File size suddenly increased from 0.5mb to 20mb

Well done for finding a way to sort it.
Sounds like Conditional Formatting is the problem causing the file to slow down.

Doug

Mon 26 Dec 2016: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Change the Print button so it brings up the Print dialogue box

If you want to bring up the Print dialogue box to check your print settings when you hit the Print button, do the following:

1. Right-click on the toolbar that displays the Print button.

2. Select Customise.

3. Click on the Print button on the toolbar to select it, then hold the left mouse button down and drag the button towards the screen below. The button should come off the toolbar.

4. In the Customise dialogue box on your screen, select the Commands tab.

5. Select File from the Categories list, and then locate the Print... icon (looks like the normal Print button, but the word Print has three dots following it).

6. Click on the Print... icon to select it, then use your left mouse to drag and drop the icon onto the toolbar at the top of the screen.

7. Close the Customise dialogue box.

View all Excel hints and tips