Instructions to delete blank lines in Excel

Deleting rows in Excel is very simple, but deleting blank lines in Excel is unknown to many people. When you download an Excel file from the internet or edit someone’s Excel table, you often encounter a situation where there are many blank lines or alternating blank cells. The following article will show you how to delete blank lines in Excel without losing important data in the table, depending on how the Excel data table is.

1. Delete Excel blank row through main data column

In case your Excel data table is sorted data by column, define 1 main column and from there will filter the empty cells in that column and then delete the corresponding blank rows. For example, with the data table below, the main column is selected as No and needs to delete rows 1, 3, 4.

Step 1:

First you black out the entire data table and then click On the Data tab, select the Filter tool.

Step 2:

Now you click arrow at main column then display the table as shown below, uncheck Select All and select Blanks already press OK to proceed to filter the empty cells in the data table.

We will use this Filter feature to filter the lines, avoiding the case where the lines are shuffled.

Step 3:

Next select the first cell of the first blank line then press Ctrl + Shift + End. Then blank lines will be highlighted, except for the first header row.

Step 4:

Click next any blank cell and then select Delete Row in the displayed list.

Now display the message as shown below click OK to delete the blank lines selected that.

Step 5:

Finally click on Data tab and then select Clear to delete the used filter.

The data table results as below when filtered for empty rows. Empty cells in rows with data are not deleted from the table.

2. Delete Excel blank rows without main column using function COUNTBLANK

With data tables that do not have a main column, empty cells are scattered in different cells, in many different rows or columns, you can choose to create more columns to be the main column.

Step 1:

Add 1 column at the end of the row data and then enter formula =COUNTBLANK(C5:H5).

Now you will see how many empty cells in each line. Fill the first cell result down to the remaining cells.

Step 2:

Black out the entire spreadsheet Click Filter. From arrow at newly added column, friend attach the filter button to cell number 6 ie 6 blank cells line 1 line. Click OK to save.

Step 3:

Then also Click on the first cell of the blank line and select Ctrl + Shift + End then right click select Delete Row to delete the line.

Finally click Clear to delete the applied Filter feature. The resulting blank line has been filtered from the table as shown below.

3. Delete Excel blank rows without main column using function COUNTA

Instead of counting empty cells with the COUNTBLANK function, we can also replace it with the function to count cells containing data.

Step 1:

You also create a new column and then in the first cell enter the function =COUNTA(C5:H5). Later Lines that do not contain data will display 0 ..

Step 2:

Now you also black out all the data, Click Filter to create a filter. At the arrow in the new column added press 0 then press OK as shown below.

Finally, click on the first empty cell and then press the Ctrl + Shift + End key combination to select the empty columns and press delete as the above operation is done.

In general, all three methods above support deleting blank rows, but if the data table does not have many blank rows or empty cells are scattered in the columns, you should use method 1 to perform faster.