Locate and reset the last cell on a worksheet

When you save a workbook, Microsoft Office 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.

What do you want to do?


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.

Top of Page Top of Page

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.

  1. On the Home tab, in the Editing group, click the arrow next to the Clear button Button image, and then click Clear All.
  2. Click the Microsoft Office Button Button image, and then click Save.
  3. Click the Microsoft Office Button Button image, and then click Close.

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

Top of Page Top of Page

 
 
Applies to:
Excel 2007