4 Effective Methods to Delete Blank Rows in Your Excel Worksheet

There will sometimes be many blank rows in your Excel worksheet. Here we will present 4 methods to delete blank rows in your worksheet.

In the image below, you will find that there are many blank rows in the range. Because you need to do some calculations for the range, you have inserted many blank rows into the range.An Example to Delete Blank Rows

And now it’s time to delete those rows. Below we have 4 methods to delete those blank rows in your Excel worksheet.

Method 1: Delete Manually

This is the most ordinary and simple method. If there are a small number of blank rows in the range, you can apply this method quickly.

  1. Right click at the header of the blank row in the worksheet.
  2. And then choose the option “Delete” in the menu. Therefore, the target row will be deleted quickly.Delete
  3. And the repeated the steps and delete other blank rows in the range.

However, if cells in the same row in other range contain values, you cannot delete the whole row. Hence, instead of deleting the whole row, you can also delete the blank cells in the current range.

  1. Select the blank cells in a row.
  2. And then right click the mouse.
  3. After that, you will also see a menu. Choose the option “Delete” in the menu.Choose Delete
  4. Next you will see a new window. Here choose the option “Shift cells up”.Shift Cells Up
  5. And then click the button “OK” in the window. Thus, the blank row in the sheet will also be deleted.

In your actual worksheet, you can use one of the two different processes according to your need.

Method 2: Use Find Feature

In this method, you will use the find feature in Excel.

  1. Place the cursor in the range. Or you can also choose the whole range in the worksheet.
  2. And then press the shortcut keys button “Ctrl + F” on the keyboard. You will then see the window “Find and Replace” window pop up. Here do not input and value into the “Fins what” text box.
  3. Next in the “Find and Replace” window, click the button “Options”.
  4. After that, choose the “Values” for the “Look in” criterion.Find
  5. And then click the button “Find All” in the bottom. Thus, all the blank cells in the range will be listed in the window.Blank Cells
  6. In the list, press the button “Ctrl +A” to select all the blank cells.
  7. And then close this window.
  8. After that, right click in the selected area.
  9. And then choose the option “Delete” in the menu.Click Delete
  10. You will see the “Delete” window again. Here also choose the “Shift cells up” in the window.Shift Cells Up

In addition, you can also click the small arrow of the button “Delete” in the toolbar. In the drop-down list, choose “Delete Cells” or “Delete Sheet Rows” according to your need.Delete in Toolbar

Actually if you directly click the button “Delete”, you will delete the selected cells. And deleting cells will not affected cells in the same rows in other range in this worksheet.

Method 3: Use Go To Special Feature

Except for using the “Find” feature to select all the blank cells, you can also use the “Go To Special” feature.

  1. Place the cursor in the range. Besides, you can also choose the whole range in the worksheet.
  2. And then click the button “Find & Select” in the toolbar.
  3. After that, choose the option “Go To Special” in the menu. Thus, the “Go To Special” window will appear.Go To Special
  4. And then in this window, choose the option “Blanks”.Blanks
  5. Next click the button “OK” in the window. Thus, all the blank cells will be selected in the range.All Blank Cells
  6. Next, you can apply the same steps in the former part to delete the blank rows in the range.

Method 4: Use Filter Feature

Now it comes to the last method. And this method is also very easy to manipulate.

  1. Select the whole target range in the worksheet.
  2. And then click the button “Sort & Filter” in the toolbar.
  3. Next choose the option “Filter” in the drop-down menu.Filter
  4. Now click the small arrow in any of the first cell in the range.
  5. In the list, only check the option “Blanks”. If there are many options, you can first clear the option “Check All” and then check the “Blanks”.Check Blanks
  6. Next click the button “OK” on the window.
  7. Now the blank rows will leave in the worksheet. Select those rows in the range.
  8. And then right click the range.
  9. In the menu, choose the option “Delete Row”.Delete Row
  10. In the pop-up window, click “OK” to delete those rows.OK

Thus, those blank rows will also be deleted from the worksheet. Here you need to pay attention that you cannot only delete blank cells in the range. You will delete entire sheet rows. Even if you click the option “Delete Cells” in the toolbar, you will see the same dialog in step 10. Thus, if you need to use this method, make sure that there is no value in the same rows in other cells. Otherwise, you will lose those data and information.

A Comparison of the 4 Methods

In the table below, we list the advantages and disadvantages of the 4 different methods.

Method 4: Use Filter Feature

Comparison Method 1: Delete Manually Method 2: Use Find Feature

Method 3: Use Go To Special Feature

Advantage Most of the Excel users, including new hands know how to use this method. When there are many blank rows in the range, you can select all the blank rows quickly. You can quickly sort out the blank rows through this method.
Disadvantage If there are many non-adjacent blank rows in the range, this method can be time-consuming. When there are few blank rows or even one row, this method will cost more time than the time of deleting manually. Using this method, you will delete the whole rows instead of rows in a certain range. Thus, if there are values in the same row in other range, you will lose those data.

Take Care of Your Mission-Critical Excel Files

The Excel files in your computer will store many important data and information. Therefore, it is important for you to take good care of your files. Because once your file corrupts, you will face with series result. At this moment, you need to stop all your current work and fix Excel. You can use a third party tool to fix your Excel. With the most advanced technology, this software can repair xls file error and recover your files. Thus, you can get back all your precious data easily.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc file corruption and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.