Fill the Empty Cells Left by Pivot Table Row Fields

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.

65 thoughts on “Fill the Empty Cells Left by Pivot Table Row Fields

  1. VeroR

    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.

  2. Barry

    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”

  3. Ashutosh

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

  4. Phanindra

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

  5. jun

    Does this option 2 work with excel 2007? when i follow the gotospecial/blanks and formula it, it does not allow to alter the pivot cells.

    Am I missing something? I am using a Windows 7 laptop. Is it the keyboard like the mac user which I just read?

  6. Mijani Agus

    Just a quick note to Jun as well as myself, it’s true you cannot alter/ override pivot cells. Therefore another step required prior to attempting the above magical steps, copy your pivot results to another spreadsheet (make sure the result is without the pivot)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>