The usual way to delete empty lines in an Excel worksheet is to find them all and then delete them line by line, but doing so is cumbersome and inconvenient. So how can you reduce the workload of deleting empty rows on a worksheet? This article will tell you how to do it.
1. Delete Excel Empty Rows Using Excel Go To Special Function.
- Here are the steps to quickly delete empty rows from Excel worksheets.
- Open the Excel worksheet that you want to delete empty rows.
- Select the entire worksheet by clicking on the box to the left of the column headers and above the row numbers.
- Alternatively, you can press “Ctrl+A” on your keyboard to select the entire worksheet.
- Click on the “Home” tab in the Excel ribbon.
- Click on the “Find & Select” button in the “Editing” group.
- Choose “Go To Special…” from the drop-down menu.
- In the “Go To Special” dialog box, select the radio button “Blanks” and click “OK“.
- All the blank cells in the worksheet will be selected.
- Right-click on any selected cell and choose “Delete…” from the context menu.
- In the “Delete” dialog box, select “Entire row” and click “OK“.
- All the empty rows will be deleted from the worksheet.
- This method is quick and easy, and it ensures that you only delete rows that are completely empty.
2. Delete Excel Empty Rows By Adding New Column.
- This is another method to delete the empty rows in an excel table, it will not change the rows’ original sort order also.
- First, open the worksheet to delete empty rows and insert a new column X after the last column of the data in the open worksheet.
- In this example, column X is column C.
- Input numbers 1, and 2 in the new column cells C1, and C2.
- Select the cell range C1:C2, click the small square on the bottom right corner of the cell range, then drag the mouse key down to auto-fill all the other numbers in column C.
- Sort the above data cell by column A or B to make all empty rows are clustered at the bottom of the table. You can refer to the article .
A B C 1 Apple Watch 800 10 2 iPad 900 4 3 iPhone 1000 2 4 Mac Mini 1000 9 5 Macbook Air 1800 7 6 Macbook Pro 1500 6 7 1 8 3 9 5 10 8
- Then you should delete all empty rows at the end of the above data cells.
- And then reorder the data set by column C in ascending order.
- Now delete column C, and you can get the data cells that do not contain the empty rows.
- In this example, column C is just a helper column, it helps us to remember the original data cells’ order.
- Following this deletion method, no matter how many empty rows are contained in the worksheet, you can delete them quickly.