Removing “ALL” option in a Pivot field

Posted on 2003-03-20
Medium Priority
Last Modified: 2012-05-04
In my Pivot table report, I have placed one Pivot field with orientation “xlPageField”. (Eg: Sex, with values “M”,”F”). Normally excel provides 3 options to select in such conditions (Viz: “(All)”, “M”, “F”). I want it to display only “M” and “F”.

I am NOT looking for a workaround solution like, after selecting “All” validating it in a event and changing it.

I want it to display only “M” and “F”.
Question by:amaronjob
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3

Expert Comment

ID: 8183870
You cannot. It's by design.

I've checked the PivotTable through VBA and the PageFields have all the normal PivotItems. Thereis no "(All)" PivotItem that you can delete or anything. Excel just adds it there. And there is also no other option to deactivate it.

Sorry it's not the answer you want but it's the only possible. (at least on XL2K)


Author Comment

ID: 8207394
I also noticed no "(All)" PivotItem under PivotFields object, can you assure there is no OTHER tweaking possible to avoid "ALL'

Accepted Solution

pauloaguia earned 100 total points
ID: 8213326
I can give you 95% sure (I've learned a long away ago never say never).

I searched many of my usual references for such a tweak and found nothing. I searched google and found nothing. I searched EE and found nothing.

I didn't find any page saying that it cannot be done. However I didn't found a single reference to how it could be done either.

The only alternative I see is for you to build your own combobox (with values M and F) and code it so that when it changes value, the PivotTable is recalculated.

Would this be an option?


Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 8216400
I got a better suggestion from Microsoft community....I think this is the best and simple solution for my requirement...

* Disable ItemSelection for that pivotfield
* add one button near pivotfield

code for toggle button

With ActiveSheet.PivotTables("PT1").PivotFields("Sex")
..EnableItemSelection = True
If .CurrentPage = "M" Then
..CurrentPage = "F"
..CurrentPage = "M"
End If
..EnableItemSelection = False
End With


Expert Comment

ID: 8220000
Apart from the fact that this suggestion has a toggle button instead of a combo box it's basically what I was telling you about :) (if you had more than two values you'd have to go to a combobox).

Is this closed then?

If so and you don't think any of the comments here answers your problem then pleas, post a 0 point question in Community Support Topic Area with a link to this one, asking a moderator to come by, deal with the question and refund your points. Otherwise please, accept the comment you believe is closer to the answer (and please, read the guidelines for grading answers before you do so).

If there are still any questions about this then please post them here.


Author Comment

ID: 8221969
Hi pauloaguia, Thanks for your effort in looking into this problem.

eventhough your comment/solution did not completly solve my problem, it did gave feed to my current solution.

If you are intrested in knowing how it is not complete enough, I will explain.

Many Thanks

Expert Comment

ID: 8227246
Thank you.

Yes, I am. I allways like to have an explanation when I get a C. Around here C's usually means some very serious problems with the expert (check this: http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3)

So, yes, I am interested in knowing why this grade...


Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
New style of hardware planning for Microsoft Exchange server.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question