Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Ensuring Rows and Columns are Empty.
Written by Allen Wyatt (last updated August 13, 2022)
This tip applies to Excel 2007, 2010, 2013, and 2016
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:
If you prefer, you can accomplish this same task using only two steps:
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12079) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Ensuring Rows and Columns are Empty.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2019 For Dummies today!
Want to add a bunch of blank rows to your data and have those rows interspersed among your existing rows? Here's a quick ...
Discover MoreSometimes the data in a worksheet isn't in the exact format desired. If you want to divide your values by 1,000, there ...
Discover MoreNeed to figure out if a cell contains a number so that your formula makes sense? (Perhaps it would return an error if the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-08-18 20:48:11
Henry
I do Select All, auto filter and click the drop down.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments