95. Excel Tips - Cause Of Big Excel Files

Nick's picture



Cause Of Big Excel Files

 

We've all been there happily developing our spreadsheet, and all of a sudden, the file size balloons.

Q: How did that happen ??!!

A: Most likely, it's uneven formatting that's causing the problem

 

Here's a screen shot the tests I performed in Excel.. There are some unexpected results and these are highlighted in red:

cause-of-big-excel-files

So, starting from a new workbook, I investigated the impact on file size of the various actions performed on an entire column of data.

The findings are very interesting, but it's probably best to watch the video to see it in action.

The main conclusions are:

  • Formatting entire columns the same does not impact file size
  • If you are going to format a large range, format it the same if you can.
  • Formatting individual cells is not good for your file size.
  • If you have a sheet that is bloated, move the contents to a new sheet, and delete the existing sheet.
  • It's not just the formatting you see, but also the formatting you don't see that can bloat your file.
  • The bloating symptoms occur when you REMOVE the content from an unevenly formatted area. 

Download sheet to practise how to understand the Causes Of Big Excel Files

Training Video on Causes Of Big Excel Files:

Excellent tutorial

Thank you for this information. I had two sheets which were only 430KB by themselves but once I copied it into another large spreadsheet as two separate worksheets the size went up to 39MB. I simply kept the two files separate.

Excel Tips

Thank Sir, very use full tips give by You

Large file fix

Not sure if this is in the video, but you can highlight all the columns to the right of your data and then right click delete them. repeat for all rows below your data, and tada, you've gotten rid of all your extra formatting

Deleting empty columns and rows

This does not appear to remove formatting on the deleted cells. In my experience, it does not help in Excel 2003 and 2007.

check this

Thank you!

This site is a Godsend!

Thank you!

Thank you for the video. I managed to remove 3.5 MB from a highly bloated Excel file of mine after watching it!

Meeni N.

Nick's picture

big excel file

Nick's picture

Large Excel Files

this comes from my Excel contact at MS:

"We store formatting in large blocks if we can - when you remove formatting from one cell in a large range, you can sometimes cause that enormous range to be broken up into a squilion tiny ranges, or that sort of thing. Duplicating worksheets will help - also saving to different formats (maybe XLSX, but certainly .HTML) too."