How to Fill Blank Cells in Excel with Value from Above

Sometimes our customers give some  incomplete data in Excel where only has header but with blank cells below, example as below

RegionEmployeeItems
CentralJonesBinder
Pen
Pen Set
EastAndersonBinder
Pen Set
ChanBinder
Pin Set
WestSmithBinder
Desk
Pen
Pen Set

 

Of course this format is understandable by human, but most of them we would like to import these data into the system, and the format we would like to import are complete rows under the header like below

RegionEmployeeItems
CentralJonesBinder
CentralJonesPen
CentralJonesPen Set
EastAndersonBinder
EastAndersonPen Set
EastChanBinder
EastChanPin Set
WestSmithBinder
WestSmithDesk
WestSmithPen
WestSmithPen Set

We could just copy all the blank rows manually or simply double click at the right below corner of the cell above the blank rows, however the challenge is if we have lots of data, let say like 10K rows with blank rows here and there, we still could do this using the above method if you want your finger to cram hahaha, above method is not efficient anymore.

Here’s the trick to achieve the same thing by using a simple formula.

  1. Select column A and B where there are blanks
  2. Go to Home tab then select Find & Select > Go To Special then select Blanks
  3. Click OK, all the blank cells are selected now
  4. Next put a simple formula to set the blank cells with value from above cell
  5. To copy this formula to all blank cells, hold on CTRL key then press ENTER
  6. Voila!! all the blank cells are now filled up correctly
Posted in Blogs and tagged .