Pivot - Untick all

[gossv]

Hello,

I have created a pivot table using a very large amount of data. When a drop down box is selected in the pivot every option is already ticked. I would like the choice to untick all boxes and then just choose 1 option rather than manually unticking all the boxes.

Does anyone know if this is possible when using Excel 2000?

Thank you

Ginny

 

[pgc01]

Hi Ginny

You could not have such an option because in a Pivot Table it's not allowed to hide all the items in a field. A field must always have at least one visible item.

Unfortunately I don't know of any way to do the next best thing that would be to uncheck all items but one.

However, if you really need this functionality often, you can write a small code that hides all the items but the first, for example, in all pivot fields.

Hope this helps
PGC

 

[help_questions]

instead of unticking, you could select the item with your mouse, right click and 'hide'.....this also works for multiple values if you highlight/select them

 

[gossv]

Thank you for your ideas.

I might try to write a macro unticking them all. It must be in a later edition of Excel that pivot tables have this option.

Thanks

 

[pgc01]

Hi again

It must be in a later edition of Excel that pivot tables have this option.

You may be right. I have xl2000 but I've already seen the "Show all" option in xl2003. My doubt is if using this option you can leave a field without any item selected. But, of course, maybe it can.

In xl2000 I can hide all items but 1. For instance leaving the first item visible:

Sub PTHideItems()
Dim PF As PivotField, iPI As Integer

For Each PF In Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields
    If PF.Orientation <> xlDataField Then
        PF.PivotItems(1).Visible = True
        For iPI = 2 To PF.PivotItems.Count
            PF.PivotItems(iPI).Visible = False
        Next
    End If
Next
End Sub

Hope this helps
PGC