# How to remove blank cells from a list with a formula in Excel

Filter out the empty cells: `=FILTER(A2:A13,A2:A13<>"")` returns the list without gaps, and it updates as names are added.

**Excel:** `=FILTER(A2:A13,A2:A13<>"")` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Volunteer |  | No gaps |
| Ana Ruiz |  |  |
|  |  |  |
| Raj Patel |  |  |
| Lee Chen |  |  |
|  |  |  |
|  |  |  |
| Kim Ortiz |  |  |
| Mo Diaz |  |  |
|  |  |  |
| Sam Cole |  |  |
| Eve Lund |  |  |
|  |  |  |

Formula in C2: `=FILTER(A2:A13,A2:A13<>"")`

Result (spills 7 × 1): Ana Ruiz; Raj Patel; Lee Chen; Kim Ortiz; Mo Diaz; Sam Cole; Eve Lund

## 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.

## Other ways

- `=TOCOL(A2:A13,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<>""))`: 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.

## 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.

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-cells-from-a-list