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.
by Allen Wyatt
(last updated June 4, 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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Referring to a range of cells on the current worksheet is easy. But what if you want to refer to a range on a number of ...Discover More
When you copy a worksheet and then need to make changes to information in that worksheet (such as changing month names), the ...Discover More
The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.