Quickly delete blank rows from a long list using Excel

if you want to delete all blank rows from a long list of data in Excel spreadsheet, several options are there. You could choose each row and then delete. This step is easy but if you have 10 million rows and there are more than 1 million blank rows, what do you do? I think it is much tedious and not easy. Using Excel program, this work could be done automatically by following some simple steps and within a few seconds all the blank rows will be complete removed!

Get yourself ready!

The following Excel spreadsheet has several bank rows. Using an automated method of Excel, all the blank rows would be completely removed form this Excel spreadsheet.


Steps to quickly delete blank rows from a long list using Excel

Step 1: Open the spreadsheet in Excel that you need to delete all the blank rows.

Step 2: Click Home tab. Under the Home tab, there is an option Find & Select and just click on that option Find & Select and then choose Go To Special and click on that option. This will brings the Go to Special dialog box.


Step 3: From Go To Special dialog box, select the option Blanks and click  on OK.


Step 4: Now, you will see that all of the blanks within the data spreadsheet have been highlighted.


Step 5: From Home tab, click on Delete / from the Delete drop-down menu select Delete Sheet Rows.


After doing the above steps, you will see that all the blank rows are deleted.


How this method works?

The Go To Special is a built-in find feature of Excel and provides several criteria to find data within the spreadsheet. When the Blanks are selected from Go To Special dialog box,  Excel quickly parsed through the data and select all those cells that contained no data i.e. the blank spaces. After that, with all of the blank cells selected, we simply delete the entire rows using the Delete option.

Think more!

Here, we just delete all the rows which are blank in the spreadsheet. However, you could do other formatting also. To format all the blank cells in Excel, browse the tutorial Highlights all the blanks in a list of data in Excel!

No comments:

Post a Comment