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.

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

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.

Life Saver!!

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”

Great tip! this page def goes to my favs!

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

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

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

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?

coool…save my time, thanks!

You, Sir, are a life-saver !

Many thanks for this excellent time-saving tip

Thank you dear.

you are genius…

Thank you, indeed you are a genius and my life saver!

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)

great really time saving