You can automatically remove blank rows in Excel by first selecting your dataset, opening in the ribbon Find & Select > Go To Special, and then selecting "Blanks." Finally, in the ribbon click Delete > Delete Sheet Rows.
Want to remove all the blank rows from your dataset? If so, Microsoft Excel offers both automatic and manual methods to let you clear the blank rows and shift your data up. Here’s how to use those ways.
When you remove a blank row, Excel deletes that entire row and shifts your data up, so that you no longer have an empty line in your dataset. Remember that you can also simply hide rows.
Automatically Delete All Empty Rows in Excel
Excel offers an automatic method that finds and deletes all the blank rows in your worksheet. This way, you don’t have to manually find any empty rows and select them to delete—something that’s too time-consuming if you have a large dataset.
To use this method, first, launch your spreadsheet with Microsoft Excel. Then, select the dataset where you want to find and remove empty rows.
While your dataset is highlighted, in Excel’s ribbon at the top, select the “Home” tab.
On the “Home” tab, in the “Editing” section, choose Find & Select > Go To Special.
A “Go To Special” window will open. Here, enable the “Blanks” option, then choose “OK” at the bottom.
All the blank rows in your selected dataset are now highlighted.
To remove your blank rows, in the “Cells” section at the top, choose Delete > Delete Sheet Rows.
Excel has removed your empty rows and shifted your data up.
Tip: To bring your deleted rows back, press Ctrl+Z (Windows) or Command+Z (Mac).
Manually Delete Blank Rows in Excel
If you don’t have a large dataset, and you prefer manually cleaning the empty rows, you have the option to do that.
In your Excel worksheet, to the extreme left of the blank row you want to delete, click the row number. This selects that entire row.
To add more rows to your selection, press and hold down Ctrl (Windows) or Command (Mac) on your keyboard and click your row numbers.
When your rows are highlighted, right-click any one blank row’s number and choose “Delete” in the menu.
Excel will remove all your selected rows, and you’re all set.