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:

Download the workbook to follow along.

Steps to Fill Blanks in Excel

Select the columns of data that are affected with blank cells. In this example, highlight cells B4:C26
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

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

grab user attention

with magazine-style charts and visually-rich tables

Start Now

15 Comments

Become an Advanced Excel User – Data Cycle Analytics May 2, 2016 at 6:14 pm - Reply

[…] 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 […]
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?
John November 2, 2022 at 3:16 pm - Reply

This is one of my favourite excel tricks, I love it! I am so surpised so little people know about it. Every time I see someone in the office deleting the blanks manually , I can believe it! Keep up the nice articles.
- William Kiarie May 19, 2023 at 6:36 pm - Reply
  
  Spot on. Let’s spread the word
Mihai May 7, 2023 at 6:15 am - Reply

I always need to double check, some values further down the rows, they get replaced with a 0. I don`t know why, but it really is annoying.
- William Kiarie May 19, 2023 at 6:34 pm - Reply
  
  I’ve not yet experienced that, Mihai, but yes, a double-check is a must.
Mostafa June 4, 2023 at 4:32 pm - Reply

It was fantastic, and it saved me a lot of time. The important thing, in my opinion, is to be sure that you are filling in the correct data. This only will be assured once you review quickly your sheet before going with this brilliant step.

Thanks, Gaurav for your fruitfully share
- William Kiarie August 11, 2023 at 9:58 am - Reply
  
  Absolutely
david M September 23, 2023 at 1:48 am - Reply

This is a great trick and I have been using it for some time. But have issues when the data set (has fields with no data that I would like to fill) is not blank. Any solution to this will be greatly appreciated.
- William Kiarie November 9, 2023 at 4:12 pm - Reply
  
  Am not sure what you mean, David.
  Is your situation different from what I’ve described in the last paragraphs above?
- Terri B March 28, 2024 at 11:12 pm - Reply
  
  These fields would be NULL values. There is VBA code that can be used to change these to blank fields prior to doing this.
  - William Kiarie May 25, 2024 at 10:52 am - Reply
    
    Absolutely. we keep it as simple as possible though
Dhanshurana October 15, 2023 at 11:49 am - Reply

Thank you
Joe January 26, 2024 at 12:29 am - Reply

This is Magic