Locate and reset the last cell on a worksheet

When you save a workbook, Excel stores only the part of each worksheet 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 and may result in more printed pages when you print the worksheet or workbook.

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.

Locate the last cell that contains data or formatting on a worksheet

• 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 the last cell and the data

1. Do one of the following:

   • 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.

2. On the Home tab, in the Editing group, click the arrow next to the Clear button , and then click Clear All.

3. Save the worksheet.

4. Close the worksheet.

   When you open the workbook again, the last cell of the data should be the last cell on the worksheet.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Top of Page