Fill in the blanks – quickly

How to fill up all the blank cells in a Table in less than 1 minute???

The table on the left is a typical output from Pivot Table.  The blank cells help to make the table more “reading-friendly”.  However they are not “analysis-friendly” as you cannot apply auto-filter correctly with that layout, not even think about to use formula like SUMIF, etc. (Note: In Excel 2007 or above, you can ask the Pivot Table to fill up the blank cells for you).


If the table is small, you can copy and paste manually to fill up the blank quite easily.  But imagine how long it takes if you are dealing with a table of more than 1000 rows with more than 100 non-continuous ranges of blank cells.

A little trick with Go to Blanks together with inputting by Ctr Enter would help you out.

  1. Select the Range where you have blank cells to be filled in (Shortcut: Ctrl * inside the table)
  2. Ctrl G to open the Go to Dialogue box and then click “Special”
  3. Select “Blanks” ==> OK


4.  All the Blanks are selected.


5.  Here comes the tricky part.  Type “=” then press the Up Arrow (DO NOT do it in the formula bar).  Then press CTRL ENTER.  You should get the result like the snapshot below:


6.  Pls study the formula in the blank cells.  All cells are referring to one cell up.  In the previous step, the action of “=”UP ARROW with CTR ENTER means [Pls get the value of the cell just one cell up to me, and do it to all the selected cells at the same time]

7.  Now, let’s do the copy and paste value thing to get rid of the formula


Isn’t it easy?

Let’s try to understand what CTRL ENTER does to better understand how the above works.


CTRL ENTER simply asks Excel to input the same thing to all the cells selected at the same time.  It applies to both Value and Formula.  And you look back to step 5 and 6 to see what it does.

This entry was posted in Excel Tips and tagged . Bookmark the permalink.

7 Responses to Fill in the blanks – quickly

  1. Abbasabdulla says:

    Smart tip. Thank you very much


  2. LukasLukas says:

    Hi Wong,
    I use the same trick but slightly different execution. Instead of writing the formula after selecting the blank cells, I create a Formula in a blank cell pointing to one cell above, and then simply copy it (pressing CTRL +C). Then I go to select the blank cells. Once selected I just do CTRL + P.

    I find I screw up less that way.

    Cheers, Lukas


    • MF says:

      Hi Lukas,
      Thanks for your sharing. This is a good one indeed, one less step regarding the Ctrl+Enter. I believe many people (including myself) typed too fast and forgot the “Ctrl” key. 😛
      Btw, I think the last action you suggested is Paste, i.e. CTRL+V (in English Keyboard), instead of CTRL+P. Right?


  3. Pingback: Completely changing the structure of a sheet

  4. Pingback: IF Blank, copy above text ???

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s