Bento Box

Great Excel find of the day:
Grabbing a useful data grid out of a pivot table. This gets rid of all those blanks in the table. Maybe there’s an option in Excel 07 for this?

Fill in Pivot Table Blank Cells Using Go To Special

“I have continually run into the problem of using the pivot table option, to summarize reams of data, but it does not fill in the rows beneath each change in row category. Do you know how to make the pivot table fill in below each change in category?? I have been having to drag and copy every code down so I can do more pivot tables or sorting. I have tried changing the options in the pivot table, to no avail.”

The answer is not easy to learn. It is not intuitive. But, if you hate dragging those cells down, you will love taking the time to learn this process! Follow along – it seems long and drawn out, but it really really works. Once you get it, you can do this in 20 seconds.

There are actually 2 or 3 new tips here. Let’s say you have 2 columns on the left which are in outline format that need to be filled in. Highlight from cell A3 all the way down to cell B999 (or whatever your last row of data is.)
Trick #1. Selecting all of the blank cells in that range.

* Hit Ctrl+G, Alt+S, K and then enter. huh?
* Ctrl G brings up the GoTo dialog
* Alt+S will pick the “Special” button from the dialog box
* The Goto-Special dialog is an awesome thing that few know about. Hit “k” to pick “blanks”. Hit enter or click OK and you will now have selected just all of the blank cells in the pivot table outline columns. These are all of the cells which you want to fill in.

Trick #2. Don’t watch the screen while you do this – it is too scary and confusing.

Hit the equals key. Hit the Up arrow. Hold down Ctrl and hit enter. Hitting equals and the up arrow says, “I want this cell to be just like the cell above me.” Holding down Ctrl when you hit enter says, “Enter this same formula in every selected cell, which, thanks to Trick #1 is all of the blank cells which we wanted to fill in.
Trick #3. Which Jennifer already knows, but is here for completeness.

You now need to change all of those formulas to values. Select all of the cells in A3:B999 again, not just the blanks. Hit ctrl+C to copy this range. Hit alt+e then sv (enter). to Paste Special Values these formulas.

Ta-da! You will never spend an afternoon manually pulling down column headings in a pivot table again.