It is a well-known fact that if you delete a row or column, Excel dutifully does your bidding, removing whatever was in that row or column. This means that it is easy to delete rows or columns you think are blank, which in fact contain information you cannot see on the screen.
So how do you tell if there is any data without scrolling through the gazillion rows and columns in your worksheet? There is a quick way you can check for data in a row or column. To check a column, follow these steps:
- Click on the first cell of the column (A1, H1, etc.).
- Press the End key once. The phrase “End Mode” should appear near the left side of the status bar.
- Press the down arrow if checking out a column or the right arrow if checking out a row.
If you prefer, you can accomplish this same task using only two steps:
- Click on the first cell of the column (A1, H1, etc.)
- Hold down the Ctrl key as you press the down arrow or right arrow.
Performing these simple steps causes Excel to move to the next cell containing data. If there is no data, Excel selects the last cell in the column (at row 1,048,576) or the last cell in the row (at column XFD). You then know that the row or column is empty and you can safely delete it.
There is a “gotcha” to be aware of if you use have hidden rows or columns in your worksheet: The first approach (pressing the End key first) will detect and stop at hidden rows or columns, but the second approach (holding down the Ctrl key) will not. Thus, if you have hidden data in your worksheet, you’ll want to use the first approach instead of the second.
