Quickly remove all blank rows from large data tables

image_pdf

If you delete blank rows in worksheet using Go To Special-> blanks, (Read more:Select and Delete all blank cells in Excel) There are chances that row with important data contains just one blank cell, the entire row will be deleted

There are two ways to quickly remove entire blank rows.

1. Quickly remove blank row using Key Column:

2. Delete Blank rows if you don't have any key column:

Quickly remove blank row using Key Column:

To delete entire blank rows, execute following steps.

1. For Example, we have data in which there are many blank cells, but we want to delete only entire blank rows.

2. To do this, find out the key column in your data. Key column helps to determine if it is an empty row or not. For example, Serial no or Customer IDs. It means that if Serial no is provided in column then that row will contain atleast some data, so that entire row will not be blank

In our example, we have column Sr. No. as key column.

3. Select data in which you want to remove blank rows. Press Ctrl + Shift + L. (Filter shortcut)

Note: We are assuming that first row will be column headers.

In filter menu of Sr. No., to quickly uncheck all data, clear the check box of "Select All" and then select the checkbox for Blanks. Click OK.

4. All Blank rows in selected data will be displayed. Select blank rows. Do not select Column headings. Right click and select Delete row. (Shortcut Ctrl + -)

5. Click Ok in Dialog box for Delete entire sheet row

6. Clear the applied filter: Press Ctrl + Shift + L.(Home-> Sort & Filter -> Clear)

Result:All blank rows are completely deleted and rows with few blanks are remains in data.

Delete Blank rows if you don't have any key column
 If your data is unstructured and you don't have any key column to identify entire blank rows, then use this method to remove blank rows.

We will count blanks in each row and then filter with maximum number.

Steps:

1. Add one extra column at the end of your data. Use formula COUNTBLANK to count the blanks in rows. It will count blank cells in specified range. Here we have provided range A2:D2.

Count the number of blanks in required data to determine if it is entire blank row or not. In this example, if 4 blanks are found then that row is blank and we can delete it

    

2. Drag the formula to all the cells.

3. Apply filter to column "Count Blanks" with maximum row, which will filter the data where entire row is blank.  

4. Select filtered row. And Delete rows using Ctrl + - shortcut. (Or right click and select Delete row)

Result: All blank rows are deleted. Now you can delete extra column of "Count Blanks".

5,614 total views, 7 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>