No more gaps – a formula to remove spaces in lists or empty rows in ranges

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  • 3
  •  
  •  

print
Validation Dropdown with spaces no a good look

Sometimes you just need to get rid of the gaps in lists!  Perhaps you want to use the list for a validation dropdown and you want no gaps in the list so it’s easier to use.  Perhaps you just want to create a better looking table from a long list of values.

I use this formula all the time for these situations, so it’s a good one to have in your Excel tool kit ready to use.Validation dropdown with no spaces

=IFERROR(INDEX(B$9:B$30,AGGREGATE(15,6,(ROW(B$9:B$30)-ROW(B$9)+1)/(B$9:B$30<>””),ROWS(B9:B$9))),””)

Note carefully where the anchoring is row being the start of the list in column B, row 30 being the end including some spaces.  The formula is copied down until all the items in B appear in the new list. in column E.

But there are situations where you have many rows of data and formulas on each row highlighting or extracting something but you need to neatly summarise.  Well the above formula is ready to be dragged across to pick up the next columns data and so on.

Range with no spaces

 

Tagged with: , , ,