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, PDF & Email