Datasheet

Resetting the Used Area
of a Worksheet
When you press Ctrl+End, Excel activates the lower-right cell in the used area of the work-
sheet. In some cases, you find that the lower-right cell in the worksheet is an empty cell —
not the real last cell. In other words, Excel sometimes loses track of the used area of your
worksheet. For example, you may find that a workbook’s file size seems much too large.
It’s possible that Excel has misidentified the used area, and is storing a large number of
empty cells.
First, try saving the workbook. After the workbook is saved, Excel may correctly identify
the last cell. If Excel still doesn’t identify the last cell, it’s probably because you deleted
data but left the formatting in the cells. To force Excel to identify the real used area, you
need to delete the columns to the right of your data and then delete the rows below your
data.
For example, assume that the real last cell in your workbook is G25 but pressing Ctrl+End
takes you to some other cell — M50, for example.
To delete those formatted cells, follow these steps:
1. Select all columns to the right of column G. To do this, activate any cell in column H,
and then press Ctrl+spacebar followed by Shift+End and then the right-arrow key.
2. Select Home ➪ Cells ➪ Delete ➪ Delete Sheet Columns. Or, right-click any column
header and choose Delete.
3. Select all rows below row 25. To do this, activate any cell in row 26, and then press
Shift+spacebar followed by Ctrl+End and then the down-arrow key.
4. Select Home ➪ Cells ➪ Delete ➪ Delete Sheet Row. Or, right-click any column header
and choose Delete.
5. Save your workbook, and Excel resets the last cell.
After performing these steps, pressing Ctrl+End takes you to the real last cell.
Tip 8: Resetting the Used Area of a Worksheet
28
04_137666 ch01.qxp 5/22/07 7:31 PM Page 28