From 4 hours to 1 minute filling in blanks in Excel

By |2016-09-03T13:48:52+00:00April 30th, 2016|

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.
NOTE:
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.

About the Author:

I am a numbers enthusiast who helps working professionals to work efficiently in MS Excel and MS Power BI. I've trained thousands of corporate professionals across Kenya. I've a degree in Applied Statistics with Computing and a certificate in Data Visualization with Power BI

3 Comments

  1. […] If you have to leave the office at 9pm or later generating a report OR you would spend 4+ hours filling in blanks OR you have to sort your data to get the highest sales, then you need to work on your Excel skills […]

  2. Gaurav June 8, 2016 at 11:52 am - Reply

    Great, nice trick…

    • DataCycleAnalyticsKenya June 8, 2016 at 1:56 pm - Reply

      Thanks Gaurav for stopping by.
      I hope you find the trick great going forward. How much time has the concept saved you?

Share your thoughts...

X
%d bloggers like this: