From 4 hours to 1 minute 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!‘ I normally inquire how the delegates deal with blanks in data. Some menial ways is to either (1)copy and paste, (2)autofill OR (3)use CTRL+D. All the methods work, the challenge is that people take 4 hours or more doing nothing productive. This posts addresses these inefficiencies. The data structure is as shown below:

The following steps will prove invaluable in your work!
  1. Select the entire data set (click inside your data and press CTRL + A)
  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. 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. If this does not happen as expected, see the note at the bottom of this page on the way forward.
  4. Upon selecting all the blank cells (don’t press anywhere on your worksheet), 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.
While doing steps 2 & 3 above, sometimes (not always), Excel does not recognize some blank cells and therefore these may not be selected. 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 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.
In the workbook you downloaded, attempt the steps using the second worksheet.

3 thoughts on “From 4 hours to 1 minute filling in blanks in Excel”

Share your thoughts...