Tip: Batch Delete empty Excel rows

When Excel is used to collect statistics and analyze objects, data input cannot be achieved overnight. adding, deleting, cutting, and merging records are indispensable. After many edits and modifications, many empty rows are often displayed in tables. The existence of these empty rows is both not beautiful and affects the data analysis results. Generally, You need to delete these empty rows. If the table contains fewer empty rows, you only need to delete one row. However, if the worksheet is large (for example, thousands of rows), and there are many empty rows in the table, you can delete one row manually, it will undoubtedly consume a lot of time. If you often encounter such troubles, try the following methods to quickly clean up and batch clear empty rows without names.

"Positioning" Method

After the table is edited, when you need to batch Delete null rows, click "Edit/locate" in the menu bar and click "locate condition" in the pop-up "locate" dialog box. In the "positioning condition" dialog box that appears, click "null" and select "1". Click "OK. Now, let's take a look at whether all the empty rows in the table have been selected and displayed in blue. Click "Edit/Delete" in the menu bar to see If empty rows in the table have disappeared.

Figure 1

TIPS: when using this method to delete a table, make sure that all cells in other non-empty rows are filled with values. Otherwise, empty cells in non-empty rows will be deleted, which directly leads to full table format confusion.

Filter Method

If some items in the table are not filled in, you must use the "filter" function to filter all empty rows in the table before deleting them.

Step 1: first move the cursor to the top of the worksheet, and then insert a new blank row in the table. Insert a new empty column at the leftmost of the worksheet, enter "1" in the first single cells without a grid, and drag the auto-fill handle to fill the column with the previous sequence. In this way, we should prepare for the next step of filtering. Because Excel can only identify consecutive rows during filtering, when there is a blank row, it will not continue to filter down, so before filtering, we insert a column to the leftmost end of the table, and fill in a simple sequence in the column to make the table continuous.

Step 2: press Ctrl + A to select the entire worksheet. Click the "Data/filter/automatic filter" command in the menu bar.

Step 3: At this time, a drop-down list box will appear at the top of each column. Select either column, click its drop-down list box, and select "blank ". If there are data rows on the page, select another column and select "blank" from the drop-down list box until no data is visible on the page (2 ).

Figure 2

Step 4: Well, all the remaining items on the page are blank. Select all items, click the "edit" menu, and select the "Delete line" command.

Step 5: All empty rows have been deleted, and then click the "Data/filter/automatic filter" command to restore all the data in the worksheet, delete the column inserted on the left.

TIPS: To avoid deleting the first row of data, insert an empty row at the beginning of the operation. Second, when the worksheet has a title row, the drop-down list box appears only after the "Auto Filter" command is selected.