How to Delete Blank Rows in Excel: Top 3 Methods
If you’re reading this article, chances are that you also work with large Excel tables and every now and then, come across blank rows in your worksheets. These rows not only prohibit many Excel built-in tools from correctly recognizing your data range but also force you to specify the table range manually.
These are some common reasons you want your excel tables to be free from unnecessary blank rows. If you want to know how to delete blank rows in Excel, you’ve landed on the right page. After watching the video and finishing this article, you’ll learn some best methods to delete blank rows in Excel.
1. How to Delete Blank Rows in Excel: Use Manual Method
Sometimes, you’ve just a couple of rows to delete. In such cases, applying the manual method is the best way.
- Select all the blank rows you want to delete. Make sure to press and hold the Ctrl key while selecting the rows.
- After the selection is done, right-click on any selected cell, and from the options, choose Delete.
- You can also apply the same command from the ribbon. Select the Home tab, choose Delete and then select Delete Sheet Rows.
- Instead of these two methods, you can use a keyboard shortcut as well. While the rows are selected, press Ctrl+minus (-) on your keyboard to delete the rows.
2. How to Delete Blank Rows in Excel: Use Find Command
This method lets you get rid of the blank rows by selecting blank cells using the Find command.
- Select a column in your worksheet that contains blank rows.
- Press Ctrl+F on the keyboard to open the Find & Replace box.
- Select the Find tab and click on Options to open the advanced menu.
- Don’t input anything in the Find what box.
- Check the box beside Match entire cell contents.
- Select Sheet from the drop-down menu of Within.
- Select Values from the drop-down menu of Look in.
- Finally, click on Find All button to get all the blank cells in the selected range.
- Select all these rows by Ctrl+A. Close the Find box.
- Delete the rows by clicking on the right button, and selecting the Delete option.
3. How to Delete Blank Rows: Use Go To Special Option
- To apply this method, you’ve to select the data range that includes all the blank rows. You can do it by selecting the first cell and then, holding the Shift key to select the last cell.
- Open the Home tab from the ribbon and click on Find & Select drop-down arrow.
- Select Go To Special… option from the menu.
- When the menu appears, click on the radio button beside Blanks and click on OK.
- As all blank rows are selected now, click on Ctrl+minus (-) to delete all the rows in a few seconds.
Concluding Words
Blanks rows are not always bad or do any direct harm to your calculation. But you may not want them to be present in your worksheet. Then, you would want to know how to delete blank rows in Excel.
In this blog, we described some best manual and automated methods of deleting Excel rows. You can go through the detailed steps and choose the approach suitable for you.