How to remove blank rows with a formula in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""))Keep the rows where any column has data: =FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")).
- Result on the example
- a 5-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""))Result{5 rows × 3 cols} "Ana", "Design", 88, "Ben", …
A2:C98 rows × 3 cols: "Ana", "Design", 88, (empty), …A range: 8 rows × 3 columns.
A2:A98 rows × 1 col: "Ana", (empty), "Ben", "Cara", …A range: 8 cells in one column.
A2:A9<>""{8 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The <> operator tests whether they differ.
B2:B98 rows × 1 col: "Design", (empty), "Sales", "Design", …A range: 8 cells in one column.
B2:B9<>""{8 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The <> operator tests whether they differ.
A2:A9<>"")+(B2:B9<>""{8 rows × 1 col} 2, 0, 2, 2, …The + operator adds.
C2:C98 rows × 1 col: 88, (empty), 74, 91, …A range: 8 cells in one column.
C2:C9<>""{8 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The <> operator tests whether they differ.
A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""{8 rows × 1 col} 3, 0, 3, 3, …The + operator adds.
FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")){5 rows × 3 cols} "Ana", "Design", 88, "Ben", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C98 rows × 3 cols: "Ana", "Design", 88, (empty), … - condition1
A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""{8 rows × 1 col} 3, 0, 3, 3, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:C9,A2:A9<>"") | spills 5 × 3 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Remove blank rows with a formula
E2, return the table in A2:C9 without its empty rows, as one formula that spills.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.