MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table Queries

Posted by JAF on July 13, 2001 4:59 AM

Query 1
Using Excel 2000, when a pivot table page field is dragged down to form a row or column field, then Excel provides a drop-down box which allows the user to specify which of the available items they wish to be displayed by checking or unckecking the check boxes.

By default, all available items are checked. Is there a quick way of unchecking ALL items to allow the user to then "re-check" the ones they want?

Query 2
I have a column which contains percentage values (amount of loan as a percentage of the value of the property).

By default, a pivot table displays each percentage, but I need to display these in "bands".

I know that I can use the Group facility, but this restricts me to fixed bandings.

What I need to do is to display in a pivot table the bollowing bandings:
0% to 39%
40% to 49%
50% to 59%
60% to 69%
70% to 79%
80% to 84%
85% to 89%
90% to 94%
95% to 99%
100% and over

As you can see, the first banding covers 40%, the next 4 are 10% each, the next 4 are 5% each and the last banding is "everything else"

Is this possible and if so, how?!?


Posted by Colm on July 13, 2001 5:22 AM

In Reply to your first query Excel XP has this capability i.e a check box called Show all where you can either check or uncheck all the items and then choose the ones you want.
Excel 2000 does not have this so will either have to updrade to Excel XP or manually uncheck the items.