When working with large volumes of data, sometimes problems arise, the causes of which, at first glance, are not obvious. For example, when you try to select the entire table, only part of it is selected, subtotals or pivot tables are incorrectly constructed. Upon closer examination, it turns out that the fault is empty lines. In this article, we learn to delete blank lines in Excel.
Delete using sort
Where can records with no data appear in them? This can be observed when downloading information from a database or exporting from another file or application. Values can be destroyed manually without deleting the row itself. One way or another, this phenomenon must and can be fought.
One way to remove blank lines in Excel is to use sorting. If you want to delete those records that have no data in any cell, you can sort by any column. If only those that are not filled in certain columns - sort only this column. Let us show an example of how to do this for completely empty records.
The cosmetics supplier sent a price list in which part of the information was deleted. This makes it difficult to work with data, so we want to remove these lines. Select the column “Price” for sorting. To sort the values, you can use the "Sort and Filter" menu of the "Edit" panel of the "Home" tab, sorting the numbers in ascending order.
Or use the header of the formatted table header. One way or another, after building the list, rows with missing prices will appear at the bottom of the table. Now delete the empty Excel rows at the end with the context menu command.
Using filter
Another simple method is to apply a filter to the data. If your table is formatted with heading styles, this makes the task easier. If not, set filters on the heading of any column or all columns. To do this, go to the "Home" tab, find the "Edit" toolbar and select "Filter" from the "Sort and Filter" menu. Buttons with downward triangles will appear on the headings of the selected column or columns, indicating the presence of a drop-down list.
Now expand the menu of this list. At the bottom of the list of column elements is the “Empty” item. Select it by first unchecking all other elements. As a result of the application, you will leave only empty Excel rows. Delete them through the list of operations of the right mouse click or from the block of operations "Cells" of the "Home" tab.
Highlight Field Range
We mark the table and call the menu item "Select a group of cells." It is located in the “Editing” command block and is called by the “Find and Select” operation.
In the command settings window, select the option "empty cells" and click "OK".
The program marks the necessary entries. To delete empty Excel rows, use the "Delete Rows from Sheet" command from the "Delete" operation menu of the "Cells" block.
There is another way to highlight empty fields. To use it, open the “Home” tab and find the “Editing” block. In the "Find and Select" menu, run the "Go To" command. In the transition settings window, select the table you are working with and click the "Select" button at the bottom of the form.
Lack of cell selection method
However, with the latter method you need to be extremely careful. If you select a table before deleting empty rows in an Excel table, the program will mark not only completely empty records, but also those in which there are cells without data. Removing the entire selected range can lead not only to the loss of the necessary information, but also to the distortion of the remaining.
Using the first two methods is more reliable and safe, so it is better to use them in order to remove blank lines in Excel.