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.
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:-
- 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.
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.
- 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.
- This will select all the blank cells in the range of all records. Press Ctrl – keys. Choose Shift Cells Up.
- This deletes all the selected blank cells.
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.
- Click OK. This would filter all the blanks indicated by blue rows.
- Select all the empty records and then press Ctrl – key to delete them.
- 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.