How to remove blank cells from a list
✓ Verified in LibreOffice 25.8.7.3Get a clean, gap-free version of a column that has empty cells scattered through it.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =FILTER(A2:A6,A2:A6<>"") | Excel 365/2021. The result spills into as many cells as there are non-blanks. |
| Google Sheets | =FILTER(A2:A6,A2:A6<>"") | Identical. |
| LibreOffice Calc | =FILTER(A2:A6,A2:A6<>"") | Needs LibreOffice 24.8+. |
How it works
FILTER keeps only the cells where the condition is true; A2:A6<>"" is true for non-blank cells, so the gaps at A3 and A5 disappear and the three fruit names spill as a compact list. The output is live — clearing or filling source cells updates it instantly. If every cell is blank, FILTER returns #CALC! (Excel) or #N/A; wrap in IFERROR for a friendly message.
Verified, not just documented
We ran =FILTER(A2:A6,A2:A6<>"") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned apple, banana, cherry — exactly the expected result. Every formula here is confirmed by actually executing it.