Sometimes our customers give some incomplete data in Excel where only has header but with blank cells below, example as below
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
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.
- Select column A and B where there are blanks
- Go to Home tab then select Find & Select > Go To Special then select Blanks
- Click OK, all the blank cells are selected now
- Next put a simple formula to set the blank cells with value from above cell
- To copy this formula to all blank cells, hold on CTRL key then press ENTER
- Voila!! all the blank cells are now filled up correctly