How to Delete Blank Rows in Excel Spreadsheet?

When we import data from other sources, then it is not necessary that it is in a clean format. Removing blank rows or cells from a range of data makes it clean. Data can only be further worked on only if it has no blank rows.

So let us start learning the short trick.

Manually Deleting Blank Rows from Range of Cells

Let us suppose we have the following data for the sales of pizzas as follows.

delete blank rows in excel using short trick raw data

Since you can see that this data has a number of blank rows. There are a number of ways to delete these blank records.

You can have the practice workbook to do this by yourself.

We can either delete these rows by selecting each of them manually and then deleting them as follows:-

deleting blank records manually
  • Select the blank row by pressing the Shift and Spacebar keys together. ( This will select the active row.
  • Right Click on the column header of the blank row.
  • Click on the delete button to delete that row.

You can select each of the blank rows and then delete the complete row from the spreadsheet. This method is time consuming and it also deletes the complete row even if there is data on the remaining cells of that row that are apart from the table or range of records.

infographics remove blank rows

Shortcut to Delete Blank Records

Now we would learn a shortcut trick to delete all the blank rows with a few clicks of the mouse. This method is a shortcut trick and is the easiest to remember.

shortcut to delete all empty records in excel
  • Select the complete range of records containing blank records.
  • Press the Ctrl G key and click on Special Button.
  • This opens the Go to Special Dialog box. Choose Blanks from there.
shortcut to delete all empty records in excel shortcut method
  • This will select all the blank cells in the range of all records. Press Ctrl – keys. Choose Shift Cells Up.
shortcut to delete all empty records in excel shortcut method step 2
  • This deletes all the selected blank cells.
shortcut to delete all empty records in excel shortcut method result

Using Filter to Clear Blank Records

This is another method to delete blank records from the set of data. We can apply filters to data to show only blank rows and then delete those rows in one go.

  • Select the range of cells containing blank records and press Ctrl Shift L keys to add filters to it.
  • This is going to add a down arrow button to each of the column headings of the data. Click on any of the down arrow buttons and select the blanks.
usinf filter in excel to remove blank records
  • Click OK. This would filter all the blanks indicated by blue rows.
using filter in excel to remove blank records step 2
  • Select all the empty records and then press Ctrl – key to delete them.
using filter in excel to remove blank records step 3
  • Note that these blank records are filtered out. and deleted. The unfiltered records are only hidden records that are present there and can be shown by removing the filter. Remove the Filter to get the actual records back on the spreadsheet.

This brings us to an end.

Thank you for reading.

Leave a Comment