Lots of blank rows in your Excel spreadsheet? Here a couple of simple ways to delete all blank rows in an Excel spreadsheet at once.
When working on an Excel spreadsheet, it is common to delete a few rows here and there. There may be any number of reasons for this like the data being out of date, invalid data, corrections, etc. For example, when I was working on a spreadsheet recently, I had to delete some rows because I no longer need them. So, I selected all those rows and deleted them. However, after deleting, the Excel spreadsheet looked funny with all the blank rows between other filled in rows and the data is off too. So, I followed the steps listed here to delete blank rows in my Excel spreadsheet.
Though empty rows are often harmless, depending on your spreadsheet and how it is configured, it can cause conflicts with your overall data. This is especially true if you created your own custom formulas or scripts that depend on cell numbers.
The good thing is, Excel has a ton of different ways to delete blank rows. I will show two different ways to remove empty rows in an Excel spreadsheet. Just follow the one you like and you will be done in no time.
How to delete blank rows in Excel
If you have multiple blank rows in different places of a worksheet, follow the below method. We are going to use the “Go to special” option to filter out the blank rows and then delete those filtered blank rows.
1. First, open the Excel spreadsheet that has blank rows among its data.
2. Next, select the complete or a range of “A” column. Since the column has blank cells, we can filter them and then delete the full rows.
3. After selecting the column, click on the “Home” tab
4. Select the “Find and select → Go to special” option. You will find this option on the rightmost side of the Home tab.
5. Select the “Blanks” radio option in the “Go to special” window. Click on the “Ok” button to continue.
6. The above action will select all the cells in the selected range. Since we want to delete all blank rows, click on the “Home → Delete → Delete sheet rows” option.
That is all. As soon as you select the delete sheet rows option, Excel will delete all empty rows in the selected range.
Use ‘Find & Replace’ to delete empty rows
You can also use the good old Find & Replace option to find and delete blank rows in an Excel sheet. Here’s how.
Open the Excel sheet with empty rows.
Click on the “Home” tab in the Excel window.
Next, click on “Find & replace” and select the “Find” option.
In the Find & Replace window, click on the “Options” button.
Here, leave the “Find what” field empty and select the “Match entire cell contents” checkbox.
After that select the following options as shown below.
- Within: Sheet
- Search: By Rows
- Look in: Values
After selecting all the options as instructed, click on the “Find All” button. This action will select all the empty cells.
Next, select all the cells that Excel found in the Find and Replace window. To select all the cells, either drag the mouse or select the first cell, hold down the Shift key, and select the last cell.
While the cells are selected, click on the “Close” button. This action will close the Find and Replace window. However, the empty cell will stay selected.
Since we want to delete blank rows, click on the “Home” tab and select the “Delete → Delete sheet rows” option.
As soon as you do that, Excel will delete empty rows immediately.
That is all. I hope that helps. If you are stuck or need some help, comment below and I will try to help as much as possible.
