How to delete blank rows in Excel

How to delete blank rows in Excel

Having blank rows of data is very annoying. They are very irritating since they make it difficult for you to navigate data around. However, you can easily remove the blank rows by following a few steps.

1. Deleting a single row manually


If you only need to get rid of a few blank rows then you can do it manually.

Steps

1. Click on the row number on the left to select

2. Right-click the selected row and delete the row

3. After that row has been deleted successfully, you can move on to the next blank row

2. The easy way of deleting blank rows

Deleting many blank rows manually can be very tiresome. For instance, when you are dealing with hundreds of blank rows it will take you forever to delete all of them. However, there is a much easier and fast way to do it.

Steps

1. Open your workbook sheet

2. Select the whole area that you want to delete blank rows

3. Click Find and select in the Home tab


4. Click go to special on the dropdown menu

5. Select blanks

6. Only the blank cells will be selected when you hit okay

7. Lastly, click delete on the home tab cells section

3. Delete blank rows using filters (The right way)

Steps

1. Let’s start by highlighting rows A, B, C, D, E and F

2. Click on the data tab

3. Select filter

Alternatively, you can select the filter option by using a keyboard shortcut that is Ctrl+Shift+L. Filter toggles will be added in each column heading

4. Click on the first column toggle

5. Deselect all items by using the select all toggle

6. Check the blank’s button

7. Click the Okay button

The rows numbers of the columns with empty cells will turn blue. Select all the blank rows that contain blue numbering and then delete them using your favorite method. Lastly, press okay to the window that says delete this entire sheet now. All your data will remain intact even without the blank rows.

4. Delete blank rows by sorting method

Apart from the filtering and manual techniques, sorting is another great method for deleting blank cells.

Steps

1. Highlight your range of data that you want to eliminate blank cells

2. Click on the data tab

3. Press either ascending or descending command

All the blank cells will appear on the bottom. You can delete or ignore them. However, if you want to go back to the original sort order, then you need to use an index column before the initial sorting. This will allow us to sort the data as it was based on this index column

5. Using filter function syntax

Another easy way to delete blank rows is by using the filter function syntax. Type it on the formula bar and press enter

=FILTER(array,include,[if_empty])

Array refers to the range of data that you want to filter. Include refers to the logical expression that will indicate what will be included in the results. While empty stands for the results depending on the include argument. To use this syntax just type it on the empty cell.