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.

Advertisement

47 comments

  1. David Tyler says:

    I recommend adding a final step to Option 2: Copy, Paste Special as Values. If you re-sort the pivot table you don’t want your labels referred to the wrong data.

  2. Clarity says:

    I can’t tell you how pleased I was when Microsoft realised the howling omission and added in the “repeat all items labels” in MS Excel 2010.

    Re option 2 presumably you are copy paste special valuing first to convert it from a pivot table to a static table?

    In the past I have created a table to the right of the pivot table pulling all of the info accross and where blank pulling the info from the relevant cell. Not perfect but got me there.

  3. judgepax says:

    Used this trick today on a 10,000-row PivotTable report dump. After the evil laughter, lovin’ ya, Mr Pig!!

    Stupid easy solution that just saved me at least 15 minutes!

  4. DC says:

    @Clarity

    “In the past I have created a table to the right of the pivot table pulling all of the info accross and where blank pulling the info from the relevant cell. Not perfect but got me there.”

    I’m still in 2007 and this is exactly what I currently do. I like option #2 above, but at the same time it’s almost easier to have the formulas all set up to the right rather than copy paste special values every time I make a JE….could be more efficient tho I will give it a try.

    Getting upgraded to 2010 in a month anyway so will take advantage of Option #1 soon enough.

    Thanks for the tips.

  5. Rian says:

    The second solution it only for those who uses the pivot table once off, but it doesn’t work or it doesn’t seem as a solution for for those who pull the data into summary pages from the pivot table! Waste of time reading here for something that one who can create a pivot table can very easily do these steps! I was looking for a permanent solution within the pivot table!

  6. Robert Katz says:

    What about the blank cells underneath the subgroups in the total rows. I’ve been trying for a while to figure out if there is a way to display the number of items in that subgroup in the pivot table but I can’t seem to figure this out. Any suggestions?

  7. silvia says:

    Hi, I tried and when I tried to type the sign = in a blank cell I got the message “cannot change this part of the pivot table report”
    Note, I have been working on a live Pivot Table row data.

    Thanks!

  8. datapig says:

    silvia: you have to convert the pivot to a hard dataset first. Copy Paste Special Values.

  9. datapig says:

    Robert: I always remove subtotals before doing this.

  10. SG says:

    plz help me on this.by opting for second option for 2007, while typing
    “=”, excel says cannot change part of this part of pivot table…..plz help me soon….how to get rid of this

  11. Donna Baylis says:

    Thanks! Nice work-around for 2007. I like that the whole block of hard data can be selected at the same time thereby addressing multiple columns. I find it helps to change the pivot table options display to Classic PivotTable layout as well.

  12. vic says:

    NICE! This is probably the one of the coolest things I’ve ever read about help in excel. It’s so easy to understand, anyone who’s anyone could easily get it. Thanks.

  13. Catherine says:

    You are an absolute genius, I cannot thank you enough for option 2! You saved me hours and hours of work!! :-D

  14. Marlous says:

    This has just saved me hours of work – love it!

  15. Jasir km says:

    Very useful…tnks a lot

  16. tony says:

    will definitely try no 1, thanks for a nice description!

  17. Andrew Leary says:

    I have always used a solution to this problem that DOESN’T require copy & pasting, and DOESN’T require Excel 2010!! I create a field in my data table — that can be a hidden column — that is all of my data columns concatenated in the format that I want. I then include that field as the top level of my pivot table, using tab format and no subtotals. Everything appears exactly as one would want — with data on every line!

    This is an example formula:

    =TEXT($AR4,”00″)&” “&TEXT($AT4,”00000″)&” “&TEXT($AU4,”00″)&” “&$AS4
    which I used to verify that all numbers would be the same length.

  18. yomandan says:

    I seem to recall the ‘show lables on all rows’ features on older versions of Excel.

    Then some genious at Microsoft decided to disable the option with the 2003 “upgrade”, leaving users in the lurch using all the extra steps described in Option 2.

    Glad to see it back in 2010, but gotta wonder who decides to cut functionality for “upgrades”. I’ve gotten in the habit to look for lost functionality in upgrades before installing. (Alas, my clients don’t always have that option.)

    Microsoft, do you listen?

  19. zio?ek, – Zauwa?y?e?? Pierw kochaj wiernie partnerka w?asnego, Ech, ty.

    Pokr?ci? g?ówk?, http://superagencja.webnode.com/blog widz?c na bu? Wagnera os?upienie.

    Przysiad? na kraw??niku. -

    Podwozie – doniós? po sekundzie, – Sta?e podwozie. Wagner ca?y czas milcza?, stukaj?c

    szpadlem. Frodo jest dozwolone awansowa? makówk?.

    Owe na nowo przesz?y Frodo, spostrzeg? Wagner, Sparta?ski, wyrafinowany
    wypierdek. Z

    nieprawdopodobnym wyczuciem substratów i dodatkowo
    talentem do pochwycenia plus.

  20. At this time it looks like BlogEngine is the preferred
    blogging platform available right now. (from what I’ve read) Is that what you are using on your blog?

  21. Today, I went to the beachfront with my kids.
    I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She placed
    the shell to her ear and screamed. There was a hermit crab inside and
    it pinched her ear. She never wants to go back! LoL I know this
    is totally off topic but I had to tell someone!

  22. henry says:

    I drop a comment when I appreciate a post on a site or
    I have something to add to the discussion. Usually it’s a result of the fire displayed in the post I browsed. And on this post Fill the Empty Cells Left by Pivot Table Row Fields ? Bacon Bits:. I was actually excited enough to drop a thought ;-) I actually do have 2 questions for you if you do not mind. Is it only me or does it appear like a few of these remarks come across like they are coming from brain dead folks? :-P And, if you are posting at other social sites, I’d like to keep up
    with anything fresh you have to post. Could you list every one of your social sites like your twitter feed,
    Facebook page or linkedin profile?

  23. paul says:

    Hello there! I know this is kinda off topic however I’d figured I’d ask.
    Would you be interested in exchanging links or maybe guest authoring
    a blog post or vice-versa? My website addresses
    a lot of the same subjects as yours and I think we could greatly benefit from each other.
    If you might be interested feel free to shoot me an e-mail.

    I look forward to hearing from you! Terrific blog by the way!

  24. donna says:

    Hi, i read your blog occasionally and i own a similar one and i was just wondering if you get a
    lot of spam feedback? If so how do you reduce it, any plugin or anything you can suggest?

    I get so much lately it’s driving me crazy so any support is very much appreciated.

  25. Really no matter if someone doesn’t understand afterward its up to other visitors that they will help, so here it happens.

  26. donald says:

    It’s amazing to pay a quick visit this web page and reading the views of all friends about this paragraph, while I am also eager of getting familiarity.

  27. Jay says:

    This tip is really helpful. Thank you very much.

  28. Lucy says:

    Hi, I’m searching for an answer to a question that is similar to this string. I want to get rid of the (blank) that pivot table shows when the base data has a blank cell. I can’t use the filter because i want to show the data in the rest of the row. I can’t replace the data with ” ‘space” because Excel then thinks there is data in that field and I’m counting full fields. So, bottom line, I want Excel to think the pivot table cell is blank without filling it with “(blank)”. Thanks, Any ideas?

  29. Allie Brown says:

    Absolutely amazing workaround for us still stuck with 2007!

    Thank you so much! :)

  30. szern says:

    thank you so much!!!! I am using MS 2003.. save a lot of time :D

  31. Gizmo says:

    That’s the handiest trick I’ve come across – saved me having to do this manually on a 25k line output. Thank you!!

  32. Alona says:

    Option 2, I LOVE YOU.

  33. ShaneJ says:

    Great tips, thank you!

  34. Chris says:

    …and then they remove it again in Excel 2011 for Mac???

  35. R says:

    Brilliant !!! Thanks a lot

  36. Tamara says:

    This is awesome, simple and streght to the point unlike so many other tutorals on-line.

  37. D says:

    Hi there,

    First of all, thank you for the post, second, my situation is the reverse.

    I’m trying to fill in my columns with a report pulled from QuickBooks with text, so I can create a Pivot Table. Which leads to me trying option 2.

    The result: Instead of filling in all the blanks with the text I want, it showed for example “=C2″ for ALL the selected blank cells.

    (With a brand new worksheet, it doesn’t have that issue)

    I’m guessing this is some sort of formatting issue but I can’t figure it out!

    Thanks guys! Looking forward to your help~

    (Working with Excel 2010)

  38. Abhijit Mone says:

    brilliant – saved me so much time …. thank you

  39. Girija says:

    Brilliant…Can’t thank you enough for saving my time…..

  40. Manoj says:

    Thank you very much !!

  41. Roberto Viana says:

    This solution works for me… Thanks a lot!

  42. April says:

    You are amazing. I love you.

  43. pie says:

    perfect, just tried option 2 and it worked smoothly

  44. Dominik says:

    Great! – THANK YOU!

  45. Zizo says:

    THANK YOU!!!!!!!! THIS JUST SAVED ME A WHOLE DAY OF WORK!!!!

  46. Erich says:

    This just save me a cumulative 5 hours on 14 budget sheets preps I was busy with since this morning.
    Thanks Microsoft Office

Leave a Reply