How To Remove Blank Rows In Excel

H

Learn how to remove blank rows in Excel in very simple steps. Read on to know.

Blank rows in Excel can come across as something of an eye sore for the users. They can make navigation difficult but Excel gives us several ways in which we can get rid of the blank rows in any worksheet. 

How To Remove Blank Rows In Excel

Let us know about the different ways it can be done.

Delete Blank Rows Manually

In case you only have a couple of rows then you can manually delete them and here is how to do it.

1. Select the blank rows you want to delete. Hold the CTRL key and click on a row to select it.

2. Once selected, right-click and choose Delete from the menu.

You can also delete rows using a ribbon command. Go to the Home tab> click on the Delete command > select Delete Sheet Rows.

Another very handy keyboard shortcut to delete rows (columns or cells) is: 

Press Ctrl + on the keyboard.

And done. Your blank rows are deleted. 

Also Read
How to freeze rows and columns in Excel
How to add row and column in Excel

Delete Blank Rows Using Find Command

1. select a column in our data.

2. Go to the Home tab > press the Find & Select > select Find

Another keyboard shortcut to open the Find menu. Press Ctrl + F on the keyboard.

This will open up the Find & Replace menu for us.

  1. Expand the Advanced options.
  2. Leave the Find what input box blank.
  3. Select the Match entire cell contents option.
  4. Search Within the Sheet.
  5. Look in the Values.
  6. Press the Find All button to return all the blank cells.

With this, a list of all the blank cells found in the selected range at the bottom of the Find menu will be out.

Select them by pressing Ctrl + A. Close the Find menu and delete all the blank cells like before.

Delete Blank Rows Using Advanced Filters

We are all familiar with the standard Filter option in Excel. However, the standard Filter option has some limitations. The Advanced Filter allows you to extract your records to a different location on the same worksheet or a different worksheet in your workbook.

Use the Advanced Filters option to get a copy of our data without any blank rows.

To use the Advanced Filters feature, you need to set up work a bit.

  1. Set up a filter criteria range. We are only going to filter based on one column, so we need one column heading from our data (in F1 in this example). Below the column heading, we need our criteria (in F2 in this example), we need to enter =” into this cell as our criteria.
  2. Select the range of data to filter.
  3. Go to the Data tab.
  4. Select Advanced in the Sort & Filter section.

Now configure the Advanced Filter menu.

  1. Select Copy to another location.
  2. Select the range of data to be filtered. This should already be populated.
  3. Add the criteria to the Criteria range (F1:F2 in this example).
  4. Select where in the sheet to copy the filtered data.
  5. Press the OK button.

We now get a copy of our data without the blanks in its new location.

Delete Blank Rows Using The Filter Function

In case you are using Excel online or Excel for Office 365, then use one of the new dynamic array functions to filter out our blank rows.

There is a dynamic array FILTER function and here is how to use it.

FILTER Function Syntax

= FILTER ( Array, Include, [Empty] )

  • The array is the range of data to filter.
  • Include is a logical expression indicating what to include in the filtered results.
  • Empty is the results to display if no results based on the Include argument are found.

FILTER Function To Filter Blanks

= FILTER ( CarData, CarData[Make]<>”” )

Enter the above-mentioned function in only one cell and the results will spill into the remaining cells. This will filter the CarData on the Make column and filter out all blanks.

Also Read
How to remove scroll lock in Excel
How to remove duplicates in Excel

Delete Blank Rows By Sorting

You can also sort the data to get all the blank rows and here is how to do it.

  1. Select the range of data
  2. Go to the Data tab.
  3. Press the sort command. Either the ascending or descending order will work.

All blank rows will appear at the bottom.

Delete Blank Rows Using Filters

Another quick way of finding empty rows is with the help of filters.

First, add filters to our data.

  1. Select the entire range of data including the blank rows.
  2. Go to the Data tab.
  3. Press the Filter button in the Sort & Filter section.

You can also add filters to a range by using the Ctrl + Shift + L keyboard shortcut.

This will add sort and filter toggles to each of the column headings and now you can use these to filter out the blank.

  1. Click on the filter toggle on one of the columns.
  2. Use the Select All toggle to de-select all items.
  3. Check the Blanks.
  4. Press the OK button.

When our data is filtered, the row numbers appear in blue and in filtered rows numbers are missing.

We can now select these blank rows with the blue row numbering and delete them using any of the manual methods.

We can then press the OK button when Excel asks us if we want to Delete the entire sheet row.

When we clear the filters, all our data will still be there but without the blank rows!

We can use filters in a slightly different way to get rid of the blank rows. This time we will filter out the blanks. Click on the filter toggle on one of the columns ➜ uncheck the Blanks ➜ press the OK button.

Now all our blank rows are hidden and we can copy and paste our data to a new location without all the blank rows.

conclusion

Microsoft Excel is a widely used software in the majority of companies and businesses around the world. It not only assists ordinary people with simple functions such as average determination and computations, but it also offers a variety of tools, functions, and features to make this software profitable for global companies in a variety of fields.

Despite this, it is also used for expense management and other data-driven tasks for household needs. To learn and properly organize data using Excel, we must first understand the definition and fundamentals of Microsoft Excel.

Easysheet is a tech blog that features incredible Excel hacks, tips, and tricks. Our goal is to help beginners learn Excel with EasySheet and find answers to all of their questions.

We are the best resource for anyone in need of assistance with Microsoft Office products, such as Excel, Word, PowerPoint, Publisher, and more.

About the author

Gaurav Singh Rawat

Add comment