Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Ensuring Rows and Columns are Empty.

Ensuring Rows and Columns are Empty

by Allen Wyatt
(last updated August 10, 2016)

1

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:

  1. Click on the first cell of the column (A1, H1, etc.).
  2. Press the End key once. The characters END should appear at the right side of the status bar.
  3. 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:

  1. Click on the first cell of the column (A1, H1, etc.)
  2. 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 66,536) or the last cell in the row (at column IV). You then know that the row or column is empty and you can safely delete it.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2111) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Ensuring Rows and Columns are Empty.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Remembering Workbook Position and Size

Want Excel to remember where your workbooks were located on the screen and then open them in the same position the next time ...

Discover More

Protecting Your Conditional Formatting Rules

If you have conditional formatting applied in a worksheet, the formulas in those formats may not be as secure as you would ...

Discover More

Converting a PDF to a Docs File

Need to get the text out of a PDF file so that you can edit it? Docs makes it easy by offering to convert the file for you as ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (MENU)

Ensuring Standard Units During Data Entry

Need to make sure that information entered in a worksheet is always in a given unit of measurement? It's not as easy of a ...

Discover More

Entering Numbers in Excel

Enter information into a cell, and Excel needs to figure out what type of information it is. Here's how Excel interprets ...

Discover More

Creating a Shortcut for Pasting Values

Excel's Paste Special command is used quite a bit. If you want to create some shortcuts for the command, here's some ways you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight more than 7?

2012-09-01 07:03:03

Thomas Papavasiliou

Attention, as hidden row or columns that may contain data are ignored and you may end up with some unpredictable results.


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share