Jumbabox

This is a quick tutorial on how you can populate the blank row area cells which are created by a Pivot Table.

The Example

I have a Pivot Table which looks at the sum of incoming and outgoing calls for the first 5 days of January, February and March. I would like to fill in the blank row areas (highlighted in yellow - see below) with the month each row belongs to.

Step 1 - Copy the cells

Select all the cells within the Pivot Table by either :

• Click + Drag a selection box around the cells including the headers
• Clicking anywhere within the Pivot Table and pressing CTRL + A

Then copy (CTRL + C) the selected cells - do not cut the cells.

Step 2 - Paste Special As Values the cells

In a desired location within a sheet or new sheet click on Edit > Paste Special… this should open up the Paste Special dialog box.

Within the Paste Special dialog box check the Values radio button and then click the OK button.

You should now see your Pivot Table looking like a standard Excel table.

Step 3 - Filling in the blanks!

This is the part you have been waiting for!

Click in the first cell which is blank in your Pivot Table, in this example it is under “Jan” then :

• Hit the = key
• Hold down CTRL and hit the Up Arrow

All you are doing here is making the cell below Jan equal to Jan.

Click on the cell with the formula and copy this cell (CTRL + C).

Now select all the cells your new table encompasses by either :

• Click + Drag a selection box around the cells including the headers
• Clicking any where within the Pivot Table and pressing CTRL + A

So you should now have your Excel table selected and the new formula still copied.

Now, click on Edit > Go To… (or CTRL+G), this will open up the Go To… dialog box.

Within the Go To… dialog box click on the Special… button (ALT + K), this will open up the Go To Special dialog box.

Within the Go To Special dialog box click on the radio button next to Blanks (press K) then click the OK button.

The Go To Special dialog box is a hidden gem in Excel, I will be go through each one of these options with examples at a later date.

You will notice that within your Excel table all the blank cells have been selected.

Now the final step, click on Edit > Paste Special > Formulas option, then click on the OK button.

You can now see that your blank cells have been filled out with the right cell values!

Of course there may be some cells that don’t need to be filled out like the cell next to the Grand Total but you can remove the Grand Total from your selection of the Pivot Table in the first step.

It does look like a long process but once you get the hang of it, it can take just under 20 seconds to fill in blank cells. You can also adapt this method to as many blank columns and rows.

If you have any questions or suggestions please comment!

Enjoy!