«

»

Aug
25
2013

How to Fix Excel2010 Large File Size/Scrollbar Issues

Another day with another issue, this time with Microsoft Excel. All the excel files I have received today for various reasons came in unusually large file sizes against little information stored in them. Working with them was extremely slower and the entire excel application crashed multiple times when processing them. The vertical scrollbar was scrollable all the way down to 1 million rows (the maximum rows supported in an excel sheet) whereas many of the sheets had data for only few rows. Some small cleanup tricks I knew did not help. The references on the web were mostly related to excel 2003 or using small VBA snippets which weren’t helping me to resolve the issue.

But after some trial and error I have managed to optimize the file size and decided to add a post about it here. I am covering only about excel 2010 spreadsheets here.

So here are some useful troubleshooting steps.

 

Step 1 – Make sure the rows or columns are not formatted (like border, color, even white color) beyond the required data range. A lot of people select the row or column headers while adding colors or borders. This often makes excel to think the entire (e.g. from row 1 to row 1 million) rows/columns are not empty and adds additional storage for them (so each sheet could contribute 3 – 5 MB to the file size due to this).

This would also mess the vertical/horizontal scrolling. If you try to scroll slightly, you will end up moving 1000s of rows at a time.

 

Excel Filesize, Scroll Issues

 

So check for such unnecessary formatting in your spreadsheet, clear them and reformat only up to your used range (e.g. 1-200 rows only).

Now save the excel sheet and check if it reduces file size and resets the scrolling.

 

Step 2 - If the step 1 does not help then consider deleting all the unused rows and columns in the sheet. Refer this video for the steps:

 

 

As you can see in this video, the scroll bar is reset after deleting unused rows/columns. The file size also should be optimized after this.

 

Step 3 - For any reason, if the step 1 and step 2 does not helps you, then you can consider rebuilding the entire excel sheet. For this, just copy and paste (only the required range of cells) the data into a newly created excel spreadsheet. This way the scrolling issue as well the file size issue should be fixed.

 

Did you find this post useful? Feel free to share your feedback in the comments area.

You can find more interesting posts from us on the posts index section

If you are interested to be notified for the new posts from us, then you can make use of the subscribe option available on the right sidebar.

Help us to improve our search rating by recommending this post to Google using the button below

 

1 comment

  1. Louise says:

    Many thanks – step 3 worked perfectly.

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>