Bento Box Bits of everything

26Jun/090

Pivot table life saver

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.

25Jun/090

RIP Michael

Never forget

http://www.youtube.com/watch?v=lF89npFbn8g

I was really looking forward to his comeback tour too. I guess he was working out too much preparing for his 50 concert run in the UK. <3 you MJ.

Tagged as: No Comments
24Jun/090

Got em Got em Need em

I don't remember the movie very well, but I sure do remember the fuckin' nightmares it gave me as a kid! Seriously. How can THIS be part of a kids' movie. Best part of the movie too. Surprised that the monster was Howie Mandel. That man still gives me nightmares. Except for Bobby's World. That was cool.

18Jun/090

Macintosh Games

Memory be damned, better jot down the games I played on my brother's Macintosh Great vintage mac info here

Dark Castle

Deja Vu

LodeRunner
Shadowgate
Star Wars
MacPlaymate (crazy early sex 'game')

Crystal Quest (this game was HARD!)

Cairo Shootout
Shufflepuck Cafe
Stunt Copter
Sierra Championship Boxing
Risk
Stratego
PipeDream
Zero Gravity

Apache Strike http://www.youtube.com/watch?v=TWCb8ayXNC8

Tagged as: , No Comments