Excel tutorial – filling in blank cells vertically

Note: Other Excel tips can be found at this link.

Excel – Filling in blanks

It’s not unusual to receive Excel data formatted for the eye, but not formatted nicely to manipulate in a database. Sometimes, spaces are left blank, as in this example:

 

 

 

 

There is a quick way to fill in the blanks in mass, rather than one section at a time.

Step 1

  • Insert a blank column in Column B, next to your incomplete data in Column A.

Step 2

Enter this column in cell B2:

  • =IF(A2=”",B1,A2)

Step 3

  • Drag to repeat the formula down through the end of your spreadsheet.

Explaining the formula

  • A2=”” – This is searching for blank fields.
  • B1 – If the field to the left is blank, the value from the line immediately above will be inserted (in this case the value from B1).
  • A2 – If the field is not blank, the value from the field immediately to the left will be inserted (in this case the value from A2).

See more Excel tips

Print Friendly