Every time I’m training staff from various corporates and I introduce this concept, the comments I get at the end is that ‘this is magic!‘ If you don’t know the magic, filling in blanks in Excel is one of the tasks that will leave you feeling that there must be a better way apart from doing it manually.

It can be daunting especially if you have thousands of rows to deal with!

Almost 95% of those I’ve trained indicated they would either (1)copy and paste, (2)auto-fill to blank cells below OR (3)use CTRL+D. All the methods work

But wait a moment…

Wouldn’t it be great to know how to do it like a star and improve your productivity? This posts addresses these inefficiencies.

The data structure is as shown below:

Download the workbook to follow along.

1. Select the columns of data that are affected with blank cells. In this example, highlight cells B4:C26
2. In the Home menu, Editing group, choose Go-to special… within the Find & Select commands. You can use the keyboard short cut, CTRL+G or tap F5 and click Special from the Go-To window. From the dialog box that appears, select Blanks and press OK

3. Excel selects all the blank cells within the selection made in step 1 above.
4. With the blank cells selected, tap the “=” sign on the keyboard followed by the Upward pointing arrow on the keyboard to select the cell above the active cell.
5. At this point, press CTRL + Enter simultaneously in order to fill all the selected cells at a go!
6. The final step is to select the entire data set again and Copy-Paste Special Values ( keyboard short cut ALT+E+S+V or ALT+H+V+V) in the same location. This helps eliminate the formulas created and thus eliminate chances of distortion of data as you try to sort the data later.