Excel - Pivot Tables

[Elisha]

We currently are still on Excel 97 at work (I think we maybe upgrading this year - I have 2000 at home). One feature seriously lacking in 97 for Pivot Tables is a select all/deselect all function when you want to hide certain values of a variable (e.g. I have prem/loss data for all 50 states but only want to show for a few states). This becomes especially troublesome if say, you have data by class code (of which there are thousands) and you only want to look at, say...50 of them. Anyone know if this feature has been added in later versions?

P.S. Feel free to use this topic to b*tch about other Pivot table inadequacies. :D

[IAm@Work.com]

Not exactly what you asked for, but try this...

Open your pivot table and move everything except state up into the "page" section.

Now select the states you want, using the normal method of clicking on the first state and, while holding the {ctl} key, select the other few you want.

Now hold the cursor over one of the selected states and {right-click}. Then select Group and Otuline... Group.

The pivot table will form a new item called "state 2" with the grouped states labeled as Group1. Move this item to the page section and select Group1.

Move the rest of the pivot items back down and proceed to look only at the few states in the group.

Once you are comfortable with how this works, try this:
At the step where you have just formed the group and you have State2 and state side-by-side...
Select the cell with the word Group1 in it.
change this cell to read "grouped states"

In effect you can name these groups whatever you want. You can then select a different group of states and group them. They initially start as Group2, but you can rename them. You end up with the ability to (for example) have two groups labeled "No Fault States" and "Liability States". Or whatever.

[whisper]

You could group the data in the pivot, as was suggested. You could also create dummy variables in the data file itself that would do that as well - if this is easier to do.

[Dr T Non-Fan]

I'm not a big fan of pivot tables. too much black box. How do you edit them, or do you have to make a whole new one?

I'm doing fine with SUMPRODUCT(()*()), thank you very much.

[Elisha]

No, D*MN it! I want my pivot tables to have (de)select all! :x ( :D )

There's nothing I'm currently doing where I would need alternatives - but something inevitably will. Even in the data, it would be a little tedious to sort them as well, since they most likely wouldn't be in numerical/alphabetical order.

(btw, DTNF, my cube neighbor hates them too and is on somewhat of a crusade to eliminate them, but I've somewhat convinced him to use SUMIF's only for regular 'template' type projects like filings, quarterly/monthly reports, etc.)

[Dr T Non-Fan]

Well, "hate" is a bit strong. And "Crusading" is doing way too much.

What I've done more recently on long data is to try to shorten the length by making it wider. Makes a big dent in recalc time.
For IBNR, for example, instead of every combination of paid month and incurred month on a separate line, I create a new column for incurred year, and 12 columns for incurred calendar month.

Suppose 36 months of paid data and 36 months of incurred dates for each. That's 1296 rows old-school . My new-school would have about 141 rows (36 months are spread over four separate incurred years, except for Decembers).

Works great for data longer than 65536.

The trick to widening is finding a column that is evenly represented among its possible values.

[MathGuy]

Suppose you have the following Data:


State Line Premium
MA Auto 100
MA WC 200
MA GL 150
CA Auto 200
CA WC 250
CA GL 225
TX Auto 350
TX WC 150
TX GL 150


Create a pivot table, putting state in the rows and line in the columns, with premium in the data section. Leave this on the same page as the data. The table should look something like this:

Sum of Premium Line
State Auto GL WC Grand Total
CA 200 225 250 675
MA 100 150 200 450
TX 350 150 150 650
Grand Total 650 525 600 1775


Now, in cell K1 (or any other that you choose, but you'll have to adjust the macro), enter the name of the fields you'd like to hide. I'll hide the state fields.

Create a macro as follows (Note that I named my pivot table "MyPivotTable"):


Sub HidePivotItems()
myPivotField = [k1].Value

With ActiveSheet.PivotTables("MyPivotTable").PivotField s(myPivotField)
myPivotItemCount = .PivotItems.Count

For i = 2 To myPivotItemCount
.PivotItems(i).Visible = False
Next i
End With
End Sub


Now, run your new macro, and you'll get:

Sum of Premium Line
State Auto GL WC Grand Total
CA 200 225 250 675
Grand Total 200 225 250 675


I'll leave it as an exercise to the reader to create the "ShowPivotItems" macro. Once you've hidden some all but one of the fields, right click on the remaining one, in this case "CA", and choose "Field" from the menu. Now, deselect the states you'd like to see from the "Hide" section.

[Dr T Non-Fan]

Actually, I fix it at the source.