# Removing Blanks

There are a number of different formulas you can use to achieve this.
You don't have to use an Array Formula, there are regular formulas that can do this.
Cells & Ranges > Removing Blank Cells Horizontal
Cells & Ranges > Removing Blank Cells Vertical

### Example 1 - Vertical

`=IF(COUNTIF(\$B\$2:\$B\$12,"?*")<ROW(B2)-1,"",INDEX(B:B,SMALL(IF(B\$2:B\$12<>"", ROW(B\$2:B\$12)),ROWS(B\$2:B2)))) `

can be used with other data in the column
can be dragged down
doesn't need to be on the same rows
only works on vertical lists

### Example 2 - Vertical

Thank you to Chip Pearson (cpearson.com/excel/NoBlanks.aspx)
Enter the formulas as an array formula into the first cell and then drag the formula down.

`=IF(ROW()-ROW(\$B\$2:\$B\$12)+1>ROWS(\$B\$2:\$B\$12)-COUNTBLANK(\$B\$2:\$B\$12),"", INDIRECT(ADDRESS(SMALL((IF(\$B\$2:\$B\$12<>"",ROW(\$B\$2:\$B\$12),ROW()+ROWS(\$B\$2:\$B\$12))), ROW()-ROW(\$D\$2:\$D\$12)+1),COLUMN(\$B\$2:\$B\$12),4))) `

can be dragged down
doesn't have to be on the same rows
only works on vertical lists

© 2024 Better Solutions Limited