How to Delete Empty Rows in MS Excel

Spreadsheets look disorganized when they contain blank cells and rows. Also, datasets containing empty values are difficult to analyze using formulas. Let’s assume you have a large spreadsheet with a lot of rows. How do you delete the blank rows? 

Well, you can manually right-click on every empty row and select Delete. Deleting each manually is easy with smaller datasets. However, manually deleting each blank row will be very difficult with larger datasets.

The 3 methods given below will help you delete all the blank rows from your spreadsheet in a few steps:

  • Delete blank rows manually
  • Use the Go To special function
  • Filter and delete empty rows

Method 1: Delete Blank Rows Manually

This is the easiest way to delete blank rows. However, it is only useful for small datasets.

Step 1: Right-click and delete

Click on the row number (at the extreme left) of the row you want to delete. 

Right-click on this row number and a pop-up menu will appear.

Select Delete.

Or

If you have to remove multiple blank rows:

To delete consecutive blank rows, click and drag the mouse cursor across the numbers of the empty rows, to the extreme left, to select all of them. 

Another quick way to select multiple rows is to select one row, press and hold in the Shift key on your keyboard, and then use the down (or up) arrow key on your keyboard to select consecutive blank rows.

Next, right-click on any row that has been selected.

On the pop-up menu that appears, select Delete


Method 2: Using the Go To Special Function

Step 1: Select the entire spreadsheet

Click on the uppermost left corner of the spreadsheet, between the number 1, and the letter A.

To access the Go To special function, press CTRL and G on your keyboard simultaneously.

You can also access the special function by pressing F5 on your keyboard. 

When you press either shortcut, a pop-up window will open. 

Step 2: Select the blanks option

Click on the Special button and select Blanks in the next window that opens. 

This function will highlight all the blank rows in the spreadsheet. 

Now click on OK to exit this window.

Step 3: Delete the empty rows

To delete the empty rows, press CTRL and(the minus key) on your keyboard simultaneously.

Or

Click on the Delete rows option in the top menu menu ribbon.

In some instances, a pop-up window will appear asking if you wish to delete rows or cells.

Select rows.


Method 3: Filtering and Deleting Empty Rows

This method uses a filter to find the empty rows and is useful for very large datasets.

Step 1: Create a new column

Create a new column and name it Blanks.

Step 2: Enter the formula

In the first cell of the Blanks column, enter the formula (=COUNTA) and include the range of the first row. 

For our example below, that would be =COUNTA(A1:AF)

The COUNTA formula counts all the cells in that row that are not blank. 

Copy the formula to all the cells in the Blanks column.

Since you’re using a table, the procedure will return 0 for every empty row.

Step 3: Filter the empty rows

Select the header row of your table.

Click on the little arrow by the Filter icon in the main menu bar.

On the drop down menu that appears, select Filter.

Next to every heading on your table, a triangle will appear.

Click on this triangle – (filter icon) at the top of the Blanks column. 

You will get a pop-up window. From the options, select only the 0 value. 

(To deselect all the options, before only selecting 0, first click on ‘(Select All)’ which will clear all the other boxes.)

Next, click on OK.

Step 4: Delete the empty rows

The table will show you only the empty rows. 

Select all the blank rows by clicking and dragging your mouse cursor over the numbers on the left, and delete them by clicking on the Delete rows icon in the top main menu ribbon.


Conclusion

Empty rows can make a mess of your spreadsheet and any formulas you wish to execute. You should remove them first to improve your work and visibility. The methods above will help you get rid of blank spaces in your spreadsheets.

Leave a Comment