Blank rows break pivot tables, filters and charts. Removing them is easy, but doing it the wrong way can delete rows that only have one empty cell. These methods keep that from happening.
Method 1: Go To Special (for rows that are fully blank)
- Select the whole data range.
- Press F5, then Special, then choose Blanks and click OK.
- Right click one of the highlighted cells, choose Delete, then Entire row.
Warning: this deletes every row that has even one blank cell in the selection. Use it only when a blank means the row is empty, or select one key column such as the ID column only.
Method 2: Filter on a key column
Add a filter, open the drop-down of a column that is never blank in a real row (for example Invoice Number), and tick only (Blanks). Select the visible rows, delete them, then clear the filter.
Method 3: Sort
Sort the data by a key column. Blank cells go to the bottom, so all empty rows sit together and you can delete them in one go. Only use this if the order of your data does not matter, or add a number column first so you can restore the order.
Method 4: Power Query
For data you clean every month, load it with Data, then From Table or Range, and use Remove Rows, then Remove Blank Rows. The step is saved and runs again on refresh.
Check before you delete
Use a helper column with =COUNTA(A2:F2). A result of 0 means the row is completely empty. Filter on 0 and delete those rows only.