Save 99% of Time Spent Filling in Blanks in Excel

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:

fill blanks in Excel

Download the workbook to follow along.

Steps to Fill Blanks in Excel

  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
fill blanks in Excel
  1. Excel selects all the blank cells within the selection made in step 1 above.
  2. 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.
  3. At this point, press CTRL + Enter simultaneously in order to fill all the selected cells at a go!
  4. 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.

What if Excel doesn’t select the blank cells?

While doing steps 2 & 3 above, sometimes (not always), Excel does not recognize some blank cells and therefore these may not be selected. You can test this with the second worksheet in the workbook you downloaded
When this happens, do the following:
  • Select the region where you want to find blanks
  • Press CTRL+H to find and replace
  • In the Find and Replace dialog box, do not type anything in the Find What box. In the Replace With box, however, type a dummy word, say ‘check’, and press Replace All
  • While the data set is still selected/highlighted press CTRL+H but this time round reverse the order. That is, in the Find and Replace dialog box, the dummy word ‘Check’ in the Find What box and do not type anything in the Replace With box. Press Replace All.
Once you do these steps, resume to step 2 above and it should work.