3 How to Delete Blank Rows in Excel quickly

This method works if there is a column in your table that helps determine if it is an empty row or not (a primary column). For example, it could be a customer ID or sequence number, or something similar.

It’s essential to save the row orders, so we can’t just sort the table by that column to move the empty rows down.

1. Select the entire table, from top to bottom (press Ctrl + Home, then press Ctrl + Shift + End   ).

   

2. Filter blank rows in the excel sheet: go to the DATA tab and click the Filter button.

   

3. Apply the filter to the Cust ”column   “: Click the arrow.

   

   In the column header, uncheck the Select All box, scroll to the bottom of the list and select the Blanks checkbox at the end. Click OK.

   

4. Select all filtered rows: Press Ctrl + Home, then press the down arrow key to go to the first row of data, then press   Ctrl + Shift + End.

   

5. Right-click on any selected cell and select ” Delete row  “. From the context menu, or press Ctrl + – (minus sign).

   

6. Click OK  in the ‘ Delete entire sheet row? “.

   

7. Delete the active filter: go to the Data tab and press the Clear button.

   

8. Ok. All empty rows are entirely removed.

   

Delete blank rows if your table doesn’t have primary columns

Use this method if you have a table with many blank cells scattered across different columns, and you need to delete the rows without a cell with data in any column.

In this case, we don’t have a primary column that can help us determine if the row is empty. So we add a helper column to the table:

1. Add the ” Blanks ” column to the end of the table and insert the following formula in the column’s first cell: 

=COUNTBLANK(A2:C2).
As its name suggests, this formula counts blank cells in the specified range; А2 and C2 are the first and last cells of the current row, respectively.

2. Copy the formula in the entire column.

3. We now have the central column in our table :). Apply a filter to the ” Blanks ” column to display only rows with the maximum value (3). The number 3 means all cells in a given row are empty.

4. Then select all filtered rows and delete the entire row as described above.
As a result, the blank row (row 5) is deleted, all other rows (with and without empty cells) remain in place.

5. Now, you can delete the helper column. Or you can also apply a new filter to a column to display only rows with one or more blank cells.
To do this, uncheck box ” 0   ”And click OK.

 

The fastest way to delete all blank rows in Excel – Use Quick Tools for Excel tool

The fastest and perfect way to get rid of blank lines is to use our add-on for Excel.

We are tired of wasting time on everyday Excel actions that look pretty simple but take 5-10 clicks. We created the Quick Tools for Excel, a one-click suite of utilities that help with Excel spreadsheets’ everyday work.

Quick Tools for Excel is a utility that will help you move columns by drag-and-drop; delete all blank cells, rows, and columns; filter by selected value, calculate a percentage, apply any of the basic maths to a range. Copy the addresses of cells to the clipboard, synchronize selections across different worksheets, and more.

How to delete blank rows in 3 simple clicks

1. Download and install Quick Tools for Excel.
2. Click any cell in your table.
3. Go to the tab ” Ablebits.com Quick tools   “And click the” Empty rows “button.

   

4. Click OK   to confirm that you want to remove the blank rows.

   

   Just three clicks, and you have a clean table, all empty rows have been gone, and row order is not distorted!