Have you ever opened a spreadsheet and noticed that there seems to be thousands of rows (and/or columns - we will only discuss rows in this blog but the principles are the same for columns) after the last row where data has been entered?
The tell-tale sign that this is the case is where the bar in the scrollbar is near the top of the page and yet you are at the end of your data input.
Normally the bar in the scrollbar should be at the bottom of the page indicating that you are at the end of your data.
When this occurs that most common cause of the additional thousands of rows is because of excessive formatting.
For example you may have selected a whole row (or column) and applied certain types of formatting that cause the last cell to be set at the end of the spreadsheet, far below the actual range of data that is in use.
This could result in over 1 million unused/unwanted rows in Excel.
There are several problems that you may encounter, which include:
Make navigation around the spreadsheet more difficult
Having a larger file size than is necessary
you may print extra, unwanted, pages,
Excel may start to crash and you may start to get "Out of Memory" error messages, or you may experience other unusual behaviour.
To resolve the issue simply clear the excess rows (and columns).
This can be performed by highlighting all the rows after your data (highlight the row after your data and then press Ctrl, End and the down arrow key (right arrow key for columns)) and then pressing delete.
Alternatively, instead of delete you can right click the mouse and Clear Contents.