Excel Pivot Selections Shortcut?

[ejsmith]

I'm working with Excel 2000 pivot tables/charts that unfortunately have hundreds of categories at the lowest level of drill down. To better analyze the individual categories we'd like to look at only one or two of the categories at a time.
Unfortunately the Excel default is to select and display all of the categories, forcing me to de-select several hundred boxes everytime I want to look at only a few categories in a dimension.
Using the standard Shift or Ctrl highlight doesn't work.
Anyone know of a shortcut to deselect/select the categories?

 

[xlbo]

Could put the categories at Page Field - then you canjust pick one at a time. Other than that, there's no way I know of except via code....


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ejsmith]

Thanks Geoff, that's what we've been doing, but it sure would be nice to be able to multi-select when you do that. Maybe Excel XP (or whatever the new version is called) will have a better interface.
Thanks!

 

[SkipVought]

ejsmith,

If I an using a PivotTable for ongoing analysis/reporting, I would write a routine to allow easy selection(s).

However, if it's a quicky, copy the pivot table range and pastespecial - values.

Then apply an autofilter to that table.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Hi ej, as long as I'm reading this correctly, you are indeed correct that XP allows you to do this better. there is a global option to select or deselect all categories, and then it is easy to select or deselect the ones you want.

Regards
Ken...................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

And just to deselect all but one (Need to keep at least one):-

Sub HidePivotItems()

'The following macro will hide all items except one.
Application.ScreenUpdating = False

On Error Resume Next
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField

For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
pi.Visible = False
Next pi
Next pf
Next pt

Application.ScreenUpdating = True

End Sub

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

And just to give you an option either way:-

Sub HideShowPivotItems()

'The following macro will hide all items except one.
Application.ScreenUpdating = False
ans = InputBox("What Do you want to do:-" _
& vbCrLf _
& vbCrLf & "Hide All items bar one ( 1 )" _
& vbCrLf & "Show all items ( 2 )&quot

On Error Resume Next
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField

Select Case ans

Case 1
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
pi.Visible = False
Next pi
Next pf
Next pt

Case 2
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
Next pf
Next pt

Case ""

Case Else
MsgBox ("Maybe you don't read too well - Try again???&quot
Call HideShowPivotItems

Exit Sub

End Select

Application.ScreenUpdating = True

End Sub

Regards
Ken..................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[ejsmith]

Thanks everyone - Thanks Ken - that works brilliantly - I'm working it into my daily routine!

 

[KenWright]

My pleasure.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------