Excel tutorial - Filling in blank cells vertically
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).
