Blank rows aren't bad, but in most sheets, they're definitely undesirable. Excel uses blanks to determine data ranges, and a blank row in the wrong place will inhibit many built-in features. Fortunately, there's an easy way to remove blank rows from a data range, but this easy technique has the potential to destroy data, so you must be careful.
SEE: Cost comparison calculator: G Suite vs. Office 365 (Tech Pro Research)
After selecting the data range, continue as follows:
- Press [F5].
- In the resulting Go To dialog box, click Special.
- Click the Blanks option and click OK. Doing so selects the blank cells (what you might think of as rows) in the selected range. In this case, that's A7:E7 and A10:E10.
- Now you're ready to delete the selected cells. On the Home tab, click the Delete dropdown in the Cells group and choose Delete Cells. Excel will display the Delete dialog box, with the Delete Cells Up option selected. Click OK. Or, press [Ctrl]+-. Excel will delete the blank cells from the selected data range.
Choosing Delete Cells deletes only the blank cells in the previously selected range. If you choose Delete Sheet Rows in step 4, you could potentially destroy data (often unseen) to the right. Choose carefully when using this option to delete blank rows when you really want to delete just the blank cells. It's easy to think in terms of rows and choose the wrong option!
- 10 Excel time-savers you might not know about (TechRepublic)
- Six clicks: Excel power tips to make you an instant expert (ZDNet)
- A cheatsheet of Excel shortcuts that make inserting data faster (TechRepublic)
- You've been using Excel wrong all along (and that's OK) (ZDNet)
- 10 advanced formatting tricks for Excel users (TechRepublic)
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.