95. Excel Tips - Cause Of Big Excel Files
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:
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:
»
- Nick's blog
- Login or register to post comments
- 101413 reads
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
http://excelexperts.com/Excel-Tips-Fix-Corrupt-Spreadsheet
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.
big excel file
Also check out these links:
Find out on which sheet the size is coming from
Fix corrupt spreadsheet
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."