Delete blank rows in Excel by using a primary column

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.

    Thêm Bộ lọc Tự động vào bảng Excel

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

    Nút tự động thả xuống tự động

    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.

    Excel Tự động lọc: chỉ hiển thị các hàng rỗng

  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.

    Chọn tất cả các hàng được lọc

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

    Nhấp chuột phải vào bất kỳ ô được chọn và chọn Xóa hàng

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

    Xóa toàn bộ hộp thoại hộp hàng

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

    Xoá bộ lọc đã áp dụng

  8. Ok. All empty rows are entirely removed.

    Tất cả các hàng trống được loại bỏ hoàn toàn

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.

Bảng Excel với nhiều ô trống rải rác trên các cột khác nhau

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: 

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.

Công thức Excel đếm các ô trống trong phạm vi được chỉ định

2. Copy the formula in the entire column.

Sao chép công thức trên toàn bộ cột

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.

Hàng trống (hàng 5) đã bị xóa

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.

Xóa bỏ


Chỉ hiển thị những hàng có một hoặc nhiều ô trống

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 ” Quick tools   “And click the” Empty rows “button.

    Nhấn và nút Empty rows

  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!

    Tất cả các hàng trống rỗng đã biến mất và thứ tự hàng không bị méo mó