The more your data is clear in Excel, the better it will be understood. This is even good and if you keep cleaning your sheet will also help you to analyze the data, eliminate any errors, and also help to reach the result. If you are reading this article, then you are constantly working with large tables in Excel. You may always notice that every so often blank rows appear in your worksheets, which prevents most built-in Excel table tools (sorts, remove duplicates, subtotal, etc.) from recognizing your data range correctly.
Deleting blank rows isn’t difficult. Here’s a guide you can follow to know “how to delete blank rows in Excel”. Read on.
How to Delete Blank Rows Manually
If you have a couple of blank rows in Excel, you can perform this method to remove the blank rows in excels.
1. Select the blank rows we want to delete. Hold the Ctrl key and click on a row to select it. When the rows we want to delete are selected then we right-click and choose delete from the menu.
2. We can also delete rows using a ribbon command, Go to Home tap-click on the Delete command-then select Delete Sheet Rows.
You can also delete the rows using your keyboard. Press Ctrl +(-) on the keyboard.
That’s it! Your blank rows now are gone.
Delete Multiple Blank Rows
If you have multiple blank rows in your excel then manual deleting rows won’t work here, it takes too much time. No problem, we will guide you on the best way to delete the cup of blank rows with one touch.
1. First, choose the column of your data including blank rows. The easiest way to do this will be to select the first cell (A1) then hold the Shift key and choose the last cell.
2. Next, in the Home tab, click on Find & Select, and click Go to Special.
You can also use shortcuts for the Go To menu. To do so, press Ctrl +G to open up the Go To menu then click on the Special button to open the Go To Special menu.
3. Now all you need to do is select the Blanks from the option and press the OK button. This will choose the only blank cells from your initial column selection.
4. Now, you can see that blank rows are selected. Under delete, select the Entire row and press the OK button.
Magic! Your hundreds of blank rows deleted now.
Delete Blank Rows using Filter
You can use the filter in excel to delete the blank rows easily. This is also an easy method to perform. Let’s see how to do so:
1. Select the data range including the blank rows. Go to Data Tab and press the Filter button in the Sort & Filter section. Doing so will add sort and filter toggles to each of the columns heading and we can now use these to filter out the blanks.
You can also do it by using the Ctrl +Shift +L.
2. Now, click on the filter toggle on one of the columns. Use the select All toggle to de-select all items. Now check the Blanks. Hit the OK button.
3. Now you can see when the data is filtered, you can see that the row numbers appear in blue and filtered rows are numbers are missing. We need to permanently remove the blank rows. We can now choose these blank rows with the blue row numbering and delete them using any of the mutual methods.
4. To do so, press the OK button when Excel asks you if you want to Delete the entire sheet row. When you clear the filter, all the data will still be there but without the blank rows.
You can use a filter with a slightly different way to get rid of the blank rows. At the time we will filter out the blanks, click on the filter toggle on one of the columns>uncheck the Blanks> press the OK button.
Note: Alternative method won’t delete the blank rows, it just disappears the blank rows and once you cleared the filter, the data will show with the blank rows.