How to remove blank cells from a list with a formula in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:A13,A2:A13<>"")Filter out the empty cells: =FILTER(A2:A13,A2:A13<>"") returns the list without gaps, and it updates as names are added.
- Result on the example
- a 7-row list that spills from C2 in
C2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
How it works
A2:A13<>"" is TRUE for every cell that holds something and FALSE for the empty ones, so FILTER keeps the names and drops the gaps. The list stays in its original order and closes up when a cell is cleared.
=FILTER(A2:A13,A2:A13<>"")Result{7 rows × 1 col} "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …
A2:A1312 rows × 1 col: "Ana Ruiz", (empty), "Raj Patel", "Lee Chen", …A range: 12 cells in one column.
A2:A13<>""{12 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The <> operator tests whether they differ.
FILTER(A2:A13,A2:A13<>""){7 rows × 1 col} "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A1312 rows × 1 col: "Ana Ruiz", (empty), "Raj Patel", "Lee Chen", … - condition1
A2:A13<>""{12 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:A13,A2:A13<>""). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 7 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TOCOL(A2:A13,1) | spills 7 × 1 | TOCOL with 1 skips blank cells, and it can also turn a block of several columns into one gap-free list. It keeps cells that hold an empty string from a formula, which FILTER drops. |
| =SORT(FILTER(A2:A13,A2:A13<>"")) | spills 7 × 1Different result | Sort the names as well. Alphabetical order. |
Common mistakes
- A cell holding a single space looks empty but isn't, so it stays in the list. Test the trimmed length instead:
LEN(TRIM(A2:A13))>0. - Referring to the whole column,
A:A, pulls in the header and makes the sheet slower. Use a range that covers the data with room to grow.
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 cells from a list with a formula
C2, list the volunteers from A2:A13 without the blank cells.Questions
How do I remove blank rows from a whole table?
Test one column that is always filled: =FILTER(A2:C13,A2:A13<>"").
How do I use the gap-free list in a dropdown?
Point Data Validation at the spilled range with #, for example =$C$2#, so the dropdown grows with the list.
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.