Fill non-contiguous blank cells with the value from the cell above the first blank

calendar_today Asked Sep 21, 2010
thumb_up 9 upvotes
history Updated April 16, 2026

Question posted 2010 · +5 upvotes

I have a column like the following:

1 red
2 blue
3 red
4 
5 blue
6
7
8 white

The blanks refer to the record above it. So #4 would be associated with red and 6 and 7 would be blue.

Is there an easy way to fill in the blanks for entire column?

Accepted answer +9 upvotes

  • Select A1:A8.
  • Press F5 to show the Goto dialog.
  • Click Special ….. Select Blanks and click OK.

That will select a noncontiguous range of blank cells.

  • Then, without selecting anything else, type =A3 and press +.
  • That will enter an array formula in all the blank cells referring to the cell above it.
  • Reselect A1:A8, and Edit – Copy.
  • Then Edit – Paste Special – Values. And you’re all set.

Note that the =A3 refers to the cell above the first blank cell.

Top excel Q&A (6)

+9 upvotes ranks this answer #91 out of 167 excel solutions on this site .
excel