Smaller Excel spreadsheets by removing “blank” cells

OK… I haven’t lost my mind here… It is very possible that if you have a large spreadsheet that has very little data in it, you might have a large number of blank rows in one of your worksheets. Each of those “blank” cells in the “blank” rows takes up space, and it can add up rather quickly when it comes to the overall size of your spreadsheet… let me show you how to fix that.

Here was my situation. A customer had a spreadsheet that was 26 MB in size, but it had very little visible data. The one worksheet in question only had seven rows in it. But using the following technique, I discovered that the worksheet actually had over a million rows of “blank” data (that took Excel about 45 minutes to highlight!). Once I removed the blank cells, the spreadsheet slimmed down to only 356 KB. Nice savings!

Here’s my spreadsheet… to start the process, click F5:

excel-blankcells-20190325-1

That brings up the Go To dialog panel. Select Special:

excel-blankcells-20190325-2

Select the Blanks option to highlight all the blank cells in the blank rows, and then click OK:

excel-blankcells-20190325-3

If you have any rows of blank cells, they’ll be highlighted after the end of the data in the worksheet like this:

excel-blankcells-20190325-4

To delete the empty rows, select Home > Clear > Clear All. That will remove all the blank cells which effectively deletes all the blank rows:

excel-blankcells-20190325-5

Save your worksheet, and marvel at how much space you’ve saved (and how much more responsive the spreadsheet has become!)

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s