Fill the Empty Cells Left by Pivot Table Row Fields

April 2nd, 2012 by datapig Leave a reply »

It's not uncommon to turn pivot tables into hard data by doing a Copy-Past Special-Values on them. This not only leaves you with the values created by the pivot table, but also the pivot table's data structure. Unfortunately, as you can see in this screenshot, this is often not ideal, as the pivot table leaves behind a bunch of empty cells that will have to be filled in before you can use this hard table.


In this post, I'll show you two relatively easy ways to fix this issue:


Option 1: Implement the New Repeat All Data Items Feature

If you have Excel 2010, you can apply the new 'Repeat Item Labels' functionality. This new feature ensures that all item labels are repeated to create a solid block of contiguous cells. The idea is to implement this feature before you Copy-Past Special-Values.


Place your cursor anywhere in your pivot table. Then go up to the Ribbon and select Design -> Report Layout -> Repeat All Item labels.


Once triggered, Excel forces the Row Field values to be repeated, giving you a contiguous table of data without gaps.



Option 2: Use Excel's GOTO Special Functionality

If you don't have Excel 2010, you'll have to use Option 2. This option takes advantage of Excel's GOTO Special functionality. First, select the range you are working with, then go to the Home tab - > Find & Select -> Go to Special.



This will activate the Go To Special dialog box. From here, choose the option for Blanks.



The result is that only the blank cells are selected.

This is effectively the same as me holding down the Ctrl key while selecting all the blank cells one by one with the mouse.



At this point, you can enter a simple formula that essentially copies the value from the cell above. This is done with two keystrokes:

Type an equal sign

Press the up-arrow key

The equal sign tells Excel that you're starting a formula in the active cell, while pressing the up-arrow key points to the cell above the active cell.



Finally, hold down the Ctrl key while pressing Enter.

Pressing Ctrl+Enter enters the same formula in all the selected cells.

The reason this works is because the formula I entered is a relative reference formula. So every formula that is entered essentially points to the value above it.


Now get out there and make me proud.



  1. LainyB says:

    Yahoo! You saved my bacon datapig – thank you!!

  2. VeroR says:

    Thank you! I’m at a new job that has an older Excel version and I was worried I would have to wait my time. Didn’t think about this simple yet effective way.

  3. moreno says:

    Life Saver!!

  4. Barry says:

    Thanks so much for the tip !
    for any other Mac users out there who are still puzzling over this – I use Excel for Mac 2011 and at first I just could not get this to work. What I got after pressing Ctrl+”Enter” was the text of the formula “=A2″ in every cell but it was not working as a formula. After some experimenting I got it to work by holding both the fn key AND the Ctrl key and pressing “Enter”

  5. Cristian says:

    Great tip! this page def goes to my favs!

  6. Ashutosh says:

    Thanks a lot for sharing this tip! Saved a lot of sweat n inconvenience. Thumbs up to u!! :)

  7. Phanindra says:

    This works absolutely excellent and i am delighted to use it seeing the ease. Thanks a lot

  8. claudia says:

    Thank you so much! you have saved me hours of copy-paste.

Leave a Reply

Powered by sweet Captcha