# How to remove blank rows with a formula in Excel

Keep the rows where any column has data: =FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")).

**Excel:** `=FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Name | Team | Score |  | Without blank rows |
| Ana | Design | 88 |  |  |
|  |  |  |  |  |
| Ben | Sales | 74 |  |  |
| Cara | Design | 91 |  |  |
|  |  |  |  |  |
|  |  |  |  |  |
| Dev | IT | 67 |  |  |
| Eli | Sales | 82 |  |  |

Formula in E2: `=FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""))`

Result (spills 5 × 3): Ana, Design, 88; Ben, Sales, 74; Cara, Design, 91; Dev, IT, 67; Eli, Sales, 82

## How it works

Each test like `A2:A9<>""` gives TRUE or FALSE per row. Adding them counts the filled cells in each row, and FILTER keeps every row where that count isn't 0, so only completely empty rows are dropped. The original table is left untouched.

## Other ways

- `=FILTER(A2:C9,A2:A9<>"")`: Checks only the first column. Fine when that column is always filled for real rows.

## Common mistakes

- In Excel, empty cells inside kept rows come back as 0. Add `&""` to the range in FILTER to show them as blank (it turns numbers into text).
- FILTER needs Excel 365 or 2021. In older versions, filter the table for blanks and delete those rows.

## Questions

**How do I drop rows that have any blank cell?**

Multiply the tests instead of adding them: `=FILTER(A2:C9,(A2:A9<>"")*(B2:B9<>"")*(C2:C9<>""))`.

**How do I delete the blank rows in place?**

Select the table, press F5 > Special > Blanks, then Home > Delete > Delete Sheet Rows. Check first that no partly filled rows are selected.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/remove-blank-rows-with-a-formula