Locating and Resetting the Last Cell on a Worksheet
When you save a workbook, Microsoft Office Excel stores only the part of each worksheet that is in use, meaning the section that contains data or formatting. Empty cells may contain formatting that causes the last cell in a row or column to fall outside of the range of cells that contains data. This causes the file size of the workbook to be larger than necessary, you may print extra pages when you print the worksheet or workbook, you may receive “Out of Memory” error messages, or you may experience other unusual behavior. Clearing the excess rows and columns to reset the last cell can help to resolve these issues.
To avoid these issues, you can locate the last cell that contains data or formatting on a worksheet, and then reset that last cell by clearing all of the formatting that may be applied in empty rows or columns between the data and the last cell. Here is how:
Locating the last cell
To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press Ctrl+End.
Note: To select the very last cell in a row or column, press End, and then press the Right Arrow key or the Down Arrow key.
Clear all formatting between your data and the last cell
To select all columns to the right of the last column that contains data, click the first column heading, hold down Ctrl, and then click the column headings of the columns that you want to select.
Tip: You can also click the first column heading, and then press Ctrl+Shift+End.
To select all rows below the last row that contains data, click the first row heading, hold down Ctrl, and then click the row headings of the rows that you want to select.
Tip: You can also click the first row heading, and then press Ctrl+Shift+End.
On the Home tab, in the Editing group, click the arrow next to the Clear button Button image, and then click Clear All.
Save your workbook and close it.
Once you open the workbook again, the last cell of the data should be the last cell on the worksheet.
Please leave your comments below with your thoughts or alternative methods.