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

Thank you SO MUCH! It worked for me and saved me SO much time!

Awesome solution! Thanks.

If so, you cannot avoid these limits by buying several policies.

But there are lots of quotes available and lots of different business insurance policies.

At some later date you might sub-categorize them into types of service,or just kee them in alphabetical order.

Fantastic…..I followed the Excel 2010 method above to populate the pivot table values in empty cells which I was always finding it frustrating populating all the empty cells manually to make meaningful data out of the pivot table with empty cells.

Thanks a lot…

the best and ONLY successful workaround I have found for this for excel 2007 for multiple row headers in a table.

thank you!

Genius ! Thank you so much. Worked beautifully !

I wish I had come across this solution five years ago. Would have save me a lot of time. Anyway, now I know and I’m forever grateful. Thank you!

Great

Dude, it’s sad I know, but option 2 has made me insanely happy. Thanks.

Is there any way to make this a default setting??

Thank you so much. I have dealing with this issue for so long. I am glad I found your tip.

Thank you so much. I have been dealing with this issue for so long. I am glad I found your tip

Very nice and helpfull

This made my day! I didn’t know there was such a simple solution available. I used to do it manually and literally spent hours just to finish the task. BIG THANKS!!!

Awesome. Thanks a lot.Its very helpful

I appreciate it. Worked on both a pivot table and copy/past Excel.

After reading the MS examples it was confusing and I wasn’t able to make their example work.

Odd.

Presentation and explanation excellent!