Delete Empty Rows in Excel
If your spreadsheet is plagued with empty rows, deleting all of them by hand may seem like a monumental task. It's easy enough to delete a single row on your own, but if you need to delete multiple blank rows you'll want to let Excel do the heavy lifting for you. Luckily there are some not-so-obvious tools that can make the process incredibly effortless.
Contents
[hide]Steps
Deleting Single Rows
- Find the row that you want to remove. If you only have a row or two that you need to delete, you can quickly do it with your mouse.
- Right-click on the row number that you want to delete. You'll see the entire empty row get selected when you right-click.
- If you have several blank rows one after the other, click and hold on the first row number, then drag your mouse to the last of the rows you want to delete. Right-click anywhere on the selection.
- Select "Delete". The empty row will be deleted, and the rows beneath will move up to fill the empty space. All of your rows beneath with automatically be renumbered.
Deleting Multiple Rows
- Make a backup of your spreadsheet. When making drastic changes to your spreadsheet, it's always a good idea to make sure you have a backup that you can quickly restore. Simply copy and paste the spreadsheet file into the same folder to create a quick backup.
- Add a column to the far-right end of the spreadsheet labeled "Blanks". This method will allow you to quickly filter out your blank rows, ensuring that you don't accidentally delete rows that contain unseen data. This is especially useful for large spreadsheets.
- Add the blank counter formula to the first open cell in the new column. Enter the formula =COUNTBLANK(A2:X2). Replace X2 with the last column in the spreadsheet before the "Blanks" column. If the spreadsheet doesn't start in column A, replace A2 with the starting column. Ensure that the row number matches the start of the spreadsheet's data.
- Apply the formula to the entire column. Click and drag the small box in the corner of the cell to apply the formula to the entire Blanks column, or just double-click to apply automatically. Each cell in the column will automatically fill with the number of blanks in that row.
- Select the entire Blanks column and click "Sort and Filter" → "Filter". You'll see a little drop-down arrow appear on the header cell.
- Click the drop-down arrow to open the Filter menu. This menu allows you to select how you want to filter the display.
- Uncheck the "Select All" box. This will deselect all of the different values, which will be selected automatically.
- Check the box with the value equal to the number of columns in your sheet. Click "OK". Checking this box will only display rows which have blanks in every cell. This will ensure that you do not accidentally delete rows that have valuable data along with some blank cells.
- Select all of the blank rows. You should only see rows that are only blank cells. Select all of the rows so that they can be deleted.
- Delete the selected blank rows. After selecting all of the blank rows, right-click on your selection and choose "Delete". The blank rows will be removed from the spreadsheet.
- Turn off the filter. Click the Filter button on the Blanks row and select "Clear Filter". Your spreadsheet will return, and your blank rows will be gone. All of your other data will be preserved.
- At this point, you can delete the Blanks column, or you can keep it for future empty row management.
