6 Ways to Delete Blank Rows in Excel (Quick and Easy)

Deleting blank rows in Excel will help you clean up your Excel data table and avoid errors in calculations. In this article, we will guide you through 6 ways to delete blank rows in Excel quickly and easily.

1. Delete blank rows in Excel using the Go To Special feature

Manually selecting and deleting blank rows in Excel is convenient if the Excel data table has few blank rows. But what if there are many blank rows spread across our large Excel data table? Don’t worry, Excel has a built-in command that allows you to select all to delete blank rows in Excel quickly.

Step 1: First, we need to select a column of data that includes all the blank rows in Excel that we want to delete. The easiest way to do this is to select the first cell (Cell A1 in this example), then hold down the Shift key and select the last cell (Cell A14 in this example).

Step 2: Now we can use the Go To Special command to select only the blank cells. By going to the Home tab , click the Find & Select button in the Editing group and choose the Go To Special… option from the drop-down menu.

In addition, Microsoft Excel also has a handy shortcut that allows you to quickly access the Go To menu. That is, press Ctrl + G to open the Go To menu, then click the Special button to access the Go To Special menu .

Step 3: Whether we open the Go To menu and then click the Special button or go straight to the Go To Special menu, we go to the same Go To Special menu.

Now all we need to do is click on the Blanks option from the options at the bottom and click on the OK button . Immediately only the blank rows of the column selected in step 1 are selected.

Step 4: Now we need to delete the selected blank rows. Microsoft provides many ways to delete selected blank rows in Excel. You can choose to delete blank rows in Excel in one of the following ways:

• Right click and select Delete. 
• Go to the Home tab , click the Delete button in the Cells feature group and select the Delete Sheet Rows option from the drop-down menu.
• Press Ctrl + –

Or from the Delete menu that appears, click the Entire row option and click the OK button to delete blank rows in Excel .  With this solution, we can find and delete hundreds of blank rows in our Excel data table in just a few seconds. This is especially useful when we have many blank rows scattered across a long data set.

2. Delete blank Excel rows using the Find command

This method will be very similar to the method of deleting blank rows in Excel using the Go To Special feature above. The only difference is that we will select the blank cells using the Find command.

Step 1: Similar to the above method, the first step is that we need to select a column to find a way to delete blank rows in Excel.

Step 2: Go to the Home tab , click the Find & Select button in the Editing feature group and select the Find… option from the drop-down menu. There is also a shortcut that you can use to open the Find menu, which is the Ctrl + F key combination on the keyboard.

Step 3: Either way, you access the familiar Find & Replace window . Now to continue deleting blank rows in Excel, follow these steps:

• Click the Options button (1) to expand the options.
• Leave the Find what box (2) blank and do not enter anything to search for.
• Click the Match entire cell contents option (3).

• Under Within (4) , select the Sheet option . 
• Look in (5) section right below select Values ​​option.
• Then click the Find All button (6) . Excel will immediately search and display a list of all blank cells found in the selected range at the bottom of the Find menu .

Step 4: We can select all the blank cells found in Excel by clicking on a blank line in the search results, then pressing the Ctrl + A key combination (1) . Then we can close the Find menu by pressing the Close button (2) . Now we can delete the blank lines in Excel of all the selected cells in one of the above ways.

3. Delete blank rows in Excel using filter

In addition to the above methods, Microsoft Excel also provides another option to delete blank rows in Excel, which is to use the Filter function . Through the filter function, you can quickly find blank rows and delete them from the data table quickly. But before doing so, we need to add the filter feature to our data table.

Step 1: Select the entire data range including blank rows.

Step 2: Go to the Data tab and click the Filter button in the Sort & Filter  feature group . Alternatively, you can also add a filter to a selected range of data using the shortcut key Ctrl + Shift + L.

Step 3: Immediately the data filter buttons will be assigned to each column header of the data table and now we can use these buttons to filter blank rows by:

• Click the filter toggle button (1) on one of the column headers.
• Click the Select All option (2) to deselect all items by default.

• Scroll down to the bottom of the filter panel and click the (Blanks) option (3). 
• Then click the OK button (4).

Step 4: After the data is filtered, the order numbers of the selected blank rows in the order column will appear in blue and the filtered rows will not have any data. Now we can select these blank rows by holding the left mouse button on the first row and dragging down to the last row marked in blue. Then we can delete blank rows in Excel using any method we want.

Either way we can click the OK button when Excel asks us if we want to delete all rows of the worksheet. Finally, you click on the filter icon and choose the option (Select All) and the Excel data table will be fully displayed, now you will no longer see any blank rows left.

We can also use filters in a slightly different way to remove blank rows in Excel by. This time we will filter out blank rows, click on the filter toggle button on one of the column headers, from the drop down menu remove the (Blanks) option at the bottom of the filter details table and click the OK button .

Now all the blank rows in the Excel data table are hidden and you can copy, paste your data to a new location without all the blank rows as before.

4. Delete blank rows in Excel using advanced filter

Similar to the above filtering method, to remove blank rows in Excel we can use the Advanced Filters option to get a copy of our data minus any blank rows. To use the Advanced Filters feature in Excel, we need to do some pre-setup.

Step 1: First we need to set the filter criteria range. As in this case we will only filter based on one column, so we need to select a column header from our data table (in this example F1 ) (1). Below the column header we need our criteria (in F2 in this example), we need to enter =”” in this cell as the criteria.

Step 2: Select the data range to filter.

Step 3: Go to the Data tab on the ribbon toolbar.

Step 4: Click the Advanced button in the Sort & Filter feature group .

Next we need to configure the menu for the Advanced Filters feature .

Step 5: Click the Copy to another location option in the new dialog box.

Step 6: Select the data range to filter. This box will be pre-filled if the range was selected before opening the Advanced Filters menu . If not, click the arrow icon button to the right of List range to re-select.

Step 7: Add criteria to the Criteria range (like F1:F2 in this example).

Step 8: Select a location in the data table to copy the filtered data.

Step 9: Click the OK button to complete the setup. You will immediately get a copy of your data in the new location without any meaningless blank rows, which also means deleting blank rows in Excel completely.

5. Delete blank rows in Excel by sorting

Very few Microsoft Excel users know that the Sort data sorting function can also be used to filter out all the redundant blank rows and delete blank rows in Excel quickly.

Step 1: Select the data range to filter blank rows.

Step 2: Go to the Data tab on the ribbon toolbar.

Step 3: Click the Sort button and the feature to sort data in ascending (A-Z) or descending (ZA) order will work.

Immediately all blank rows in the selected data range will appear at the bottom and you can quickly delete that blank row in Excel without affecting the overall data table.

If we need the original sort order of the data, we can add an index column before sorting. We can then sort to get the blank rows at the bottom and delete them. Then sort our data back to the original order based on the index column.

6. Delete blank rows in Excel using Power Query

Power Query in Excel is an advanced tool that helps users create an automated process for organizing, processing, and restructuring data tables. However, you can also use this tool to find and delete blank rows in Excel easily. This method only works with Excel 2016 and Excel 2019 versions, for Excel 2010 and Excel 2013 users, it can be downloaded as an add-in.

Important Note: Power Query will convert the source data into an Excel table and change formatting such as fill color, borders, and some number formats. If the formatting of the original data is important to you, you’ll be better off choosing some other way to delete blank rows in Excel.

Step 1: Select the range where you want to delete blank rows in Excel.

Step 2: Go to the Data tab and click the From Table/Range button in the Get & Transform feature group . This will load your Excel data table into the Power Query Editor.

Step 3 : Click the Home tab of the Power Query Editor window , then click the Remove Rows > Remove Blank Rows button.

Step 4: Next, click the Close & Load button to load the result table into a new Worksheet and close the Query Editor window.

The result returned you will get a data table with blank rows completely removed in Excel. However as mentioned above, as in the result shown below the currency format is lost and the date is displayed in the default format instead of the custom format. So this is absolutely not a suitable solution to remove blank rows in important data.

Blank rows in Excel data tables can cause a lot of trouble for users. Therefore, deleting blank rows in Excel will help your data management and processing work become neat and avoid other unfortunate mistakes.

Conclusion

Depending on your needs, you can choose one of the above solutions to delete blank rows in Excel when needed. Among the above methods, I like the method of using the Go To feature to delete blank rows in Excel the most. This is a quick solution and extremely easy to implement. And you, which method do you choose to delete blank rows in Excel, please share with us.

Source: Quang Bao FPT

See more: Summary of 5 ways to number in Excel quickly and accurately