Spreadsheets look disorganized when they contain blank cells and rows.
Empty values also make it difficult to analyze data using formulas. are difficult to analyze using formulas.
In this post, we will be discussing a few ways to delete empty rows in an MS Excel Spreadsheet.
3 methods to delete empty rows in MS Excel
- Delete blank rows manually
- Use the Go To special function
- Filter and delete empty rows
Method 1: Delete Blank Rows Manually
This is the easiest way to delete blank rows. However, it is only useful for small datasets.
Step 1: Right-click and delete
Click on the row number (at the extreme left) of the row you want to delete.
Right-click on this row number and a pop-up menu will appear.
Select Delete.
Or
If you have to remove multiple blank rows:
To delete consecutive blank rows, click and drag the mouse cursor across the numbers of the empty rows, to the extreme left, to select all of them.
Another quick way to select multiple rows is to select one row, press and hold in the Shift key on your keyboard, and then use the down (or up) arrow key on your keyboard to select consecutive blank rows.
Next, right-click on any row that has been selected.
On the pop-up menu that appears, select Delete.
Method 2: Using the Go To Special Function
Step 1: Select the entire spreadsheet
Click on the uppermost left corner of the spreadsheet, between the number 1, and the letter A.
To access the Go To special function, press CTRL and G on your keyboard simultaneously.
You can also access the special function by pressing F5 on your keyboard.
When you press either shortcut, a pop-up window will open.
Step 2: Select the blanks option
Click on the Special button and select Blanks in the next window that opens.
This function will highlight all the blank rows in the spreadsheet.
Now click on OK to exit this window.
Step 3: Delete the empty rows
To delete the empty rows, press CTRL and – (the minus key) on your keyboard simultaneously.
Or
Click on the Delete rows option in the top menu menu ribbon.
In some instances, a pop-up window will appear asking if you wish to delete rows or cells.
Select rows.
Method 3: Filtering and Deleting Empty Rows
This method uses a filter to find the empty rows and is useful for very large datasets.
Step 1: Create a new column
Create a new column and name it Blanks.
Step 2: Enter the formula
In the first cell of the Blanks column, enter the formula (=COUNTA) and include the range of the first row.
For our example below, that would be:
=COUNTA(A1:AF)The COUNTA formula counts all the cells in that row that are not blank.
Copy the formula to all the cells in the Blanks column.
Since you’re using a table, the procedure will return 0 for every empty row.
Step 3: Filter the empty rows
Select the header row of your table.
Click on the little arrow by the Filter icon in the main menu bar.
On the drop down menu that appears, select Filter.
Next to every heading on your table, a triangle will appear.
Click on this triangle – (filter icon) at the top of the Blanks column.
You will get a pop-up window. From the options, select only the 0 value.
(To deselect all the options, before only selecting 0, first click on ‘(Select All)’ which will clear all the other boxes.)
Next, click on OK.
Step 4: Delete the empty rows
The table will show you only the empty rows.
Select all the blank rows by clicking and dragging your mouse cursor over the numbers on the left, and delete them by clicking on the Delete rows icon in the top main menu ribbon.
Conclusion
Empty rows can make a mess of your spreadsheet and any formulas you wish to execute. You should remove them first to improve your work and visibility. The methods above will help you get rid of blank spaces in your spreadsheets.