Crafting a spreadsheet often involves creating a mess as you copy and paste data throughout your document. During this process, you might leave behind some blank rows that can mess up your data and make your sheet look unpolished.
Here, I'll show you how to delete blank rows in Excel individually and in bulk. I'm using Excel online, but you can use the exact same steps in the desktop app.
How to remove individual blank rows in Excel
As you shift data around, you might end up with a stray blank row here and there—or maybe a few. It's easy to delete these rows as you go.
Click a row's number to select it, and drag your mouse to nearby rows if you have more than one blank row side by side. Right-click the number(s) you selected, and choose Delete rows to get rid of them.
How to delete all blank rows at once in Excel
When you're dealing with massive amounts of data, right-clicking and deleting won't always do the trick. Thanks to the COUNTA formula, though, you can find completely blank rows in a spreadsheet (and then delete them yourself).
The COUNTA formula counts the number of cells in a range that have values on them, so when it returns a 0, that means all of the cells in the range are empty. You can then filter your spreadsheet to find only the rows that come back with a 0.
Follow these steps:
Add a new column at the end of your spreadsheet called "Blanks" (or whatever you'd like, as long as you remember it).
Enter the COUNTA formula in the first row by typing
=COUNTA(and highlighting the rest of the cells in the row. Finish off the formula with a). The final formula should look something like=COUNTA(A2:C2)but with your relevant cell numbers taking the place of A2 and C2.
Copy that formula down your entire column. Click the cell with your formula in it and drag the bottom-right corner down the cells you want to add it to. Or:
Click your formula cell.
Hold Shift, and click the last cell in the range you want to select.
Press F2 to go to edit mode for your formula cell.
Press
Ctrl + Enterorcommand + returnto copy the formula across the full range of cells.
The cells in your Blanks column should now have numbers corresponding to the number of cells with content in them per row.
Now it's time to filter your data to just the rows with 0 in the Blanks column. Include all of your data in the filter by clicking the square in the top-left corner between the 1 row title and the A column title to select all of your data.
Click Filter in the Data toolbar.
You should see buttons at the top of every column. Click on the one above the Blanks column. At the bottom of the menu that appears, click the Select All checkbox to unselect all filter options, then click the 0 checkbox to select only rows that contain 0 in the Blanks column.
You should see only blank rows now. Select those rows, right-click, and choose Delete Rows to remove them.
Enjoy your sparkling clean document!
Automate Microsoft Excel
Now that you're the proud owner of a pristine spreadsheet, keep it working for you. Connect Excel to Zapier, so you can automatically update data and take action on it. Learn more about how to automate Excel, or get started with one of these pre-made workflows.
Add new Typeform entries as rows on an Excel spreadsheet
Add or update Mailchimp subscribers from new rows on Excel
Zapier is a no-code automation tool that lets you connect your apps into automated workflows, so that every person and every business can move forward at growth speed. Learn more about how it works.
Related reading:
This article was originally published in May 2019 by Justin Pot. The most recent update was in December 2023.
