If there are a lot of blank lines in an Excel worksheet, deleting them all manually might seem like a huge job. Deleting a single row is a very simple operation, but if you need to delete a large number of empty rows you will have to let Excel do the bulk of the work. Fortunately, there are some little-known tools that can make this extremely easy.
Steps
Method 1 of 2: Delete Single Lines
Step 1. Locate the row you want to delete
If you need to delete just one or two blank lines from the spreadsheet, you can do it by simply using the mouse.
Step 2. Click on the number of the row to be deleted with the right mouse button
This will select the entire row and display the corresponding context menu.
If you need to delete multiple contiguous rows of the sheet, click on the first and drag the mouse cursor to the last one to select all the rows to remove. At this point click with the right mouse button on any point of the selected area
Step 3. Select the "Delete" option
The selected blank row will be deleted from the sheet. The lines following the selected one will be moved up and automatically renumbered.
Method 2 of 2: Delete Multiple Lines
Step 1. Create a backup copy of the sheet you want to edit
When you need to make numerous and profound changes to an Excel sheet it is always best to start by making a backup copy of the data so that you can easily restore it in case of error. It simply creates a copy of the original Excel file within the same folder it is in.
Step 2. Add a new column on the right side of the sheet, where the data columns end or at the end of the sheet columns, naming it "Blank_Rows"
This method allows you to quickly filter the data in your worksheet by highlighting empty rows and making sure you don't accidentally delete rows that contain hidden data. This method is very useful especially in the case of very large Excel sheets.
Step 3. Inside the first blank cell of the new column, enter the formula which will calculate the number of total blank rows present in the sheet
Type the following formula = COUNTBLANK (A2: X2). Replace the X2 value with the name of the last column in the sheet before the one you called "Blank_Rows". If the data within the sheet does not start at column "A", also replace the value A2 with the address of the starting column of the sheet. Make sure that the row numbers of the data range correspond to the real ones of the sheet in question (where the data to be processed is present).
Step 4. Apply the formula to all cells of the "Blank_Rows" column
Click on the small square visible in the lower right corner of the cell where you entered the formula, then drag it down to select as many cells as you want. Alternatively, double on the small square under consideration to have the formula applied automatically. In this way, each cell of the "Blank_Rows" column will display the number of blank rows present in the indicated data range.
Step 5. Select the entire "Blank_Rows" column and click on the "Sort and Filter" option, then choose the "Filter" item
Inside the cell that contains the column header you will see a small button appear with an arrow pointing downwards.
Step 6. Click on the button with a small down arrow to access the Excel autofilter drop-down menu
The latter will allow you to select how to filter the data to be displayed.
Step 7. Uncheck the "Select all" checkbox
All filter values that have been automatically selected will be deselected.
Step 8. Select the check button of the value that corresponds to the number of columns in the sheet
Click on the "OK" button. This will only display rows that have all cells blank. By doing this you will be sure not to accidentally remove from the sheet the rows that contain real data, but which also have empty cells inside them.
Step 9. Select blank lines
At this point, only the completely blank lines of the sheet should be visible. Select them all so you can easily remove them.
Step 10. Delete the selected rows
After having selected all the empty lines of the sheet, click with the right mouse button on any point of the selection and choose the item "Delete". The rows you have chosen will be automatically removed from the sheet.
Step 11. Disable the filter
Click on the filter button visible in the header of the "Blank_Rows" column and choose the "Delete filter" option. The data within the sheet will be displayed in its original form and the blank lines will disappear. All necessary data will have been kept.
