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.