How to delete a blank row in an Excel table

In the process of data statistics and analysis, some blank lines appear in the table due to the addition, deletion and merging of the data tables. The existence of these empty lines is not beautiful, but also affects the results of data analysis. If one line to delete, time-consuming and laborious, the author here summarizes the following three methods, so that you can easily delete Excel table blank lines.

I. Positioning and deletion method

Use the "positioning" feature provided in Excel to locate all empty rows in a table at once, and then "catch" them.

Select the edit → position menu command in turn, in the Locate dialog box that appears, click the Position Condition button, select the empty value single option in the Next pop-up criteria dialog box, and then click the OK button so that all blank lines in the table are selected. Then right-click the selected area and select "Delete → whole line" in the pop-up shortcut menu.

Cico Tip: Use this method to delete when you want to ensure that all other non-empty rows in all cells are filled with numeric values, otherwise there will be the phenomenon of mistakenly deleted records.

Ii. Screening and deletion method

Filter all empty rows in the table by using the filtering features provided in Excel and then delete them.

Because Excel can only recognize contiguous rows while filtering, it does not continue to filter down when an empty row is encountered, so before filtering, we can insert a column anywhere in the table, filling a simple sequence in the column to make the table contiguous.

Select the data → filter → autofilter menu command, and then select a field (such as "gender") and choose a filter with blank so that you can filter out empty rows. Next, select the empty lines to delete them, and then display all of the non-empty rows at the end.

Cico Tip: If a record's filtered field (such as "sex") corresponds to a cell that is not filled in, the data is filtered out, and other fields can be filtered until the filtered record is blank.

Third, sorting Delete method

Use ascending or descending action to arrange all empty rows in the table together and then delete them.

Like filtering, sorting in Excel can only be done in contiguous non-empty rows, so you must also add a new column in the table that has a simple sequence filled in. Then select a field to sort the field in ascending or descending order, at which point all the empty rows will be sorted to the end of the table and then deleted.

Cico tip: Because of the use of sorting operations, so that the order of the original records in the table is disrupted, the new "Empty column" field can only be sorted in ascending order.