How do I set up filter for page fields in pivot table?

I have a fantastic pivot table that I built that I use to answer questions 
from by customers.  However, the questions are often varied and I find it 
difficult to easily select and de-select data.  Is there a way to filter the 
page fields in a pivot table.  For example, I might need to go from seeing 
all the data for one plant to the data for one material at all the plants.  I 
have over a thousand materials, over a hundred plants.  So, going from one 
set to data to the other is taking some time because I have to select and 
de-select so many individual items.  I would really like to add a "All" and a 
"None" category.

Thanks in advance for your help!
0
Mitsycat (2)
5/6/2005 8:36:10 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
261 Views

Similar Articles

[PageSpeed] 5

In Excel 2002 and later versions, there's a Show All checkbox on the 
Items dropdown list. For earlier versions, you can use programming, as 
in the sample code shown here:

   http://www.contextures.com/xlPivot03.html


Mitsycat wrote:
> I have a fantastic pivot table that I built that I use to answer questions 
> from by customers.  However, the questions are often varied and I find it 
> difficult to easily select and de-select data.  Is there a way to filter the 
> page fields in a pivot table.  For example, I might need to go from seeing 
> all the data for one plant to the data for one material at all the plants.  I 
> have over a thousand materials, over a hundred plants.  So, going from one 
> set to data to the other is taking some time because I have to select and 
> de-select so many individual items.  I would really like to add a "All" and a 
> "None" category.
> 
> Thanks in advance for your help!


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/6/2005 8:48:24 PM
Thanks Debra!  I have Excel 2000, so I will check out the programming options 
supplied in the address you attached below.

"Debra Dalgleish" wrote:

> In Excel 2002 and later versions, there's a Show All checkbox on the 
> Items dropdown list. For earlier versions, you can use programming, as 
> in the sample code shown here:
> 
>    http://www.contextures.com/xlPivot03.html
> 
> 
> Mitsycat wrote:
> > I have a fantastic pivot table that I built that I use to answer questions 
> > from by customers.  However, the questions are often varied and I find it 
> > difficult to easily select and de-select data.  Is there a way to filter the 
> > page fields in a pivot table.  For example, I might need to go from seeing 
> > all the data for one plant to the data for one material at all the plants.  I 
> > have over a thousand materials, over a hundred plants.  So, going from one 
> > set to data to the other is taking some time because I have to select and 
> > de-select so many individual items.  I would really like to add a "All" and a 
> > "None" category.
> > 
> > Thanks in advance for your help!
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
Mitsycat (2)
5/6/2005 9:22:11 PM
You're welcome! I've also added "Show All Page Field Items" and "Hide 
All Page Field Items" to my Pivot table Add-in, that you can download here:

   http://www.contextures.com/xlPivotAddIn.html

Mitsycat wrote:
> Thanks Debra!  I have Excel 2000, so I will check out the programming options 
> supplied in the address you attached below.
> 
> "Debra Dalgleish" wrote:
> 
> 
>>In Excel 2002 and later versions, there's a Show All checkbox on the 
>>Items dropdown list. For earlier versions, you can use programming, as 
>>in the sample code shown here:
>>
>>   http://www.contextures.com/xlPivot03.html
>>
>>
>>Mitsycat wrote:
>>
>>>I have a fantastic pivot table that I built that I use to answer questions 
>>>from by customers.  However, the questions are often varied and I find it 
>>>difficult to easily select and de-select data.  Is there a way to filter the 
>>>page fields in a pivot table.  For example, I might need to go from seeing 
>>>all the data for one plant to the data for one material at all the plants.  I 
>>>have over a thousand materials, over a hundred plants.  So, going from one 
>>>set to data to the other is taking some time because I have to select and 
>>>de-select so many individual items.  I would really like to add a "All" and a 
>>>"None" category.
>>>
>>>Thanks in advance for your help!
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/6/2005 9:27:36 PM
Reply:

Similar Artilces:

Adding fields to a table
HI, a client want to implement a VIP program using RMS, unfortunately the number of fields that the customer table have are not enough. One of the solutions that the It guy of my client is considering is to add field to the customer table. I have my serious reserve about this but want to give him an apropiate solution not a "no, you can not do that". Do you guys had an experience with this kind of problems? how do you solved it? Thks in advance for your time I would go with adding a new table. I have no real basis for this, but i have seen several other add-ins tha...

How do you change the excel number default settings?
There is no brackets on my negative numbers in excel. I have gone through the cell format, custom, accounting, or currency, choices, but none exists. Can someone advise as to where to set this as a default setting? Go into your Windows Regional Settings and then Customize. On the Currency tab (not Number) select a Negative Currency format that has parens. Then restart Excel. -- Jim "Garry Siskos" <Garry Siskos@discussions.microsoft.com> wrote in message news:8ECE6F1E-5F55-4C4A-93E1-0239395A7025@microsoft.com... | There is no brackets on my negative numbers in excel....

How do I get it to appear on the page as it does in Print Preview?
I'm planning to e-mail a promotion flyer department-wide but I can't get it to where the lines and text boxes are invisible when displayed in the message. When I got to print preview, the lines/text boxes aren't there. How do I get those lines to disappear while I'm viewing it on the page? Nevermind...I figured it out :) Thanks! "Officeworker777" wrote: > I'm planning to e-mail a promotion flyer department-wide but I can't get it > to where the lines and text boxes are invisible when displayed in the > message. When I got to print previe...

Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot find any reference to it in the help. try this url http://www.fontstuff.com/vba/vbatut07.htm ================= "Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message news:72D4DB63-1D7F-477A-93C7-2F767A207B26@microsoft.com... > Is it possible to lookup a calendar for date selection on a cell. I cannot > find any reference to it in the help. ...

Pivot Table Error...Help!
Hi, I have created numerous pivot tables in my workbook. The refres function was working fine. A couple fo days have passed and now when go to refresh the tables i get the attached error. does anyone know how to fix this? thanks +------------------------------------------------------------------- |Filename: pivot.jpg |Download: http://www.excelforum.com/attachment.php?postid=2677 +------------------------------------------------------------------- -- molavi11 ----------------------------------------------------------------------- mo...

Outlook 2000 Setting Up of some default features (calendar & print)
I'm a newbie about Outlook (Version Office 2000), and I'm configuring it in some features. Those are my needs with no answer at the moment (maybe the answer can be it' impossible to do!!). A) I setting the daily calendar to have an hour scale on daily calendar with ant interval of 15 minutes. On the screen the hours are showed only for entire ones (eg : 9:00, 10:00, etc.) and the half and quarterly hour are signed just with a dash. Is it possible to show the entire scale on the screen ? something like that : NOW NEEDS 9:00 9:00 -- 9:15 --- 9:30 -- 9:45 ...

Restore DPM settings after upgrade the server from 32-bit to 64-bit
In order to resolve the 4GB limit on a 32-bit Win 2008 standard, I will need to install Win2008 R2 64-bit on the same DPM server. Since there's no upgrade path between 32 & 64-bit, I will need to resinstall DPM 2007 SP1. In order to save all settings had done to the DPM (protectiong roup, backup schedule settings, etc..) I plan to backup and restore the DPM database. Is there anything I need to know or need to do to make this easier? Thanks Bill http://technet.microsoft.com/en-us/library/bb808991.aspx describes the entire restore process. -- Santhosh Sivar...

TABLE W/RUNNING BALANCE
need an excel table. needs the starting balance, the date, vendor name, amount and have it deducted from the running balance, very simple like a check book. thank you Donna column a .....date column b .....name column c .....amount column d .....balance start in row 1 with headings row 2 with the opening balance in D2 enter in D3 =if(C3="","",D2-C3) this means in plain language if cell c3 is empty show d3 to be empty, otherwise is c3 has a value show the baance in D3 hover over the right hand bottom corner of cell D3 the mouse pointer will change to a &qu...

Sales amount source tables-dailysales, tendertotals, batch
Anyone who can help, Some of the challenges I have is not understanding or not able to find references to some of the tables in Store Ops//HQ. I have read Partner Article 862575 and 935586, and they help a little. I consider the tables relating to sales are important considering the dollar reporting that I have to do from Store Ops/HQ. 1. Which table does the Z report get the data from? I believe it is the TenderTotals and DailySales. I checked them - and I hope I am right. 2. What does the 'shift' and 'count' columns mean in the TenderTotals table? 3. I have two regi...

Copy Worksheet to Preserve Page Set Up
I have a workbook with a very detailed worksheet that I want to copy onto addtional worksheets to run different scenarios. I can easily copy the worksheet but it doesn't preserve the detailed page set up for printing. How do you get it to copy the page set up too? Thanks Uzytkownik "TiChNi" <TCNICHOLS@YAHOO.COM> napisal w wiadomosci news:1114535445.503200.260140@z14g2000cwz.googlegroups.com... > I have a workbook with a very detailed worksheet that I want to copy > onto addtional worksheets to run different scenarios. I can easily > copy the worksheet but it...

To field not showing recipient.
Some of the email sent unable show recipient's name in To field.Only show as blank. But recipient do get the email. What version of Outlook are you using? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Thiru" <Thiru@discussions.microsoft.com> wrote in message news:04AA3D17-1276-451D-9BAD-DEED3932C57C@microsoft.com... > Some of the email sent unable show recipient's name in ...

Expansion Server setting
I recently migrated from Exchange 5.5 to Exchange 2003. Yesterday, I removed the Ex5.5 server for good according to the instructions provided by Microsoft (Q822450). I have also stopped the ADC services and switched to Native Mode. However, when I look at the properties for groups (or what's formerly known as distribution lists), under the Expansion server setting, the old server is still there. Is there any way to get rid of that, and if yes, where can I find the instructions? If you want to know what I mean, I have a screen shot here: http://chrishch.freeshell.org/misc/advpro...

Change to Opportunity fields when record is modified
This seems like an easy thing to do, but some how I'm just not getting it. We have structured our CRM 3.0 to use the Solution Sales model. We have modified the sales rating fields in both Lead and Opportunity screens to work on a 1 to 10 basis instead of Cold/Warm/Hot. We want to set the probablity field to reflect this 10 step process. We have done this successfully with a manual rule to update the probablity percentage based on the stage of the sales rating. All is well so far. Now we want to trigger the above rule whenever the sales rating field is modified, or alternatively, w...

Field which is disabled
Modifying GP 9.0 PM with Modifier/VBA After make visible hidden field and add to VBA I tried to move data to this field and have this error: Run-time error ‘1008’: Unsafe Operation. An attempt was made to set a value into on application field which is disabled. This operation could compromise the integrity of that application. I tried this: ApplyPayablesDocuments.Update1099Amount.Enabled = True But still have the error. Any suggestion will be appreciated. Regards, Valentin What the system is saying is you are doing something that interferes with the logic built into GP and it ...

I am trying to set up the mail section to send emails
I need help in set up of the mail feature regards Donna M. Cleveland "Donna M. Cleveland" <dmariecleveland@yahoo.com> wrote in message news:unEKczGcKHA.2188@TK2MSFTNGP04.phx.gbl... >I need help in set up of the mail feature > > regards > > Donna M. Cleveland I see you posted with a Yahoo email address. Is that your only email=20 address? US-based free Yahoo accounts have webmail access only, no POP access. Windows Mail handles POP mail but not Yahoo webmail.=20 Yahoo's solution is to pay them for premium "Mail Plus" service...

concatenating two fields in one cell, of which one is a date.
I have two cells: Cell A2 Today's date is Cell A3 02/08/2007 I want cell A4 to state: Today's date is 02/08/2007 When I use the formula =CONCATENATE(A2," ",A3) I get the following: Today's date is 17669 How do I make this state [Today's date is 02/08/2007] in one field? One way: =A2&" "&TEXT(A3,"dd/mm/yyy") Or =CONCATENATE(A2," ",TEXT(A3,"dd/mm/yyy")) Regards Trevor "Manuel Murieta" <livelonger@gmail.com> wrote in message news:e0Cvbw8SHHA.480@TK2MSFTNGP02.phx.gbl... >I have ...

retrieve information from table
From the table below i want to retrieve for example the figure "6" (6 i input in another cell) and put the outcome somewhere else on my sheet i the following order: input: 6 (for example =b1) bookings: 2,3 fee: 3,2 How can i retrieve an input number from a table and put the results a above somewhere else (bookings and fee are the names of the x-axis an y-axis ) Hope there is somebody who can help me! Thanks ;) fee bookings 1 2 3 2 2 4 6 2,1 2,1 4,2 6,3 2,2 2,2 4,4 6,6 2,3 2,3 4,6 6,9 2,4 2,4 4,8 7,2 2,5 2,5 5 7,5 2,6 2,6 5,2 7,8 2,7 2,7 5,4 8,1 2,8 2,8 5,6 8,4 2,9 ...

error when field last name is 'empty'
Hallo, I am using a code from the book - Building Microsoft Access applications - to search for duplicate names when adding a new contact. Last week it suddenly returned a specific error. It took me some time to find out that the code behaved that way in my database because the field last name was empty in one of the records. I thought I might use a code to return a message when someone leave the field empty by accident. But this does not solve the problem, is there a better solution? Thanks in advance for any help! Srwe Hi David, Set that field's Required property to Yes in the...

SQL View that includes open and history SOP tables?
Is it possible to create a SQL view (or anything else) that can combine data from open and history tables, like SOP10100 and SOP30200? Similar to what SmartList does for Sales Transactions, but I don't think they create a view. I'm trying to construct queries to search customer activity across both open and history files. -- Jim@TurboChef I created the exact views while back and called them SOPHDR(SOP10100/SOP30200) and SOPLNE(SOP10200/SOP30300). I've used them in Crystal Reports. I'll also post this on my blog for others. Below is the actual view for SOPHDR: SELECT...

Junk Mail Filters -- how to use them?
In two weeks, I've graduated from being a baby-Outlook user to a toddler. Please continue to bear with me. I have activated junk mail filters in Outlook 2002. I may have added one or two senders. * Where do I look to see who I've added? * I can't find where to configure the junk mail filters, except to turn them on or off. * How do I make sure that anybody in my contacts can go ahead and send me junk and porn? lol * I gather the junk mail filter for v2003 can be updated from MS. Does Outlook 2002 have a way to update the junk mail filter? Thank you. -- Lady Dungeness Cr...

Subtotal function with Filtered Data
I trying to use the following worksheet to analysis required and assigned resources across a wide range of projects. Project Source Skill 3Q05 AWG 10G Assigned PL 0.00 AWG 10G Required PL 0.00 AWG 10G Gap PL 0.00 AWG 6G Assigned IDFM 0.00 AWG 6G Required IDFM 0.25 AWG 6G Gap IDFM 0.25 AWG 6G Assigned IDFT 0.25 AWG 6G Required IDFT 0.25 AWG 6G Gap IDFT 0.00 GigaDig 12G Assigned IDFM 0.00 GigaDig 12G Required IDFM 0.25 GigaDig 12G Gap IDFM 0.25 GigaDig 12G Assigned IDFT 0.20 GigaDig 12G Required IDFT 0.25 GigaDig 12G Gap IDFT 0.05 GigaDig 12G Assigned SLM-LSP 0.00 GigaDig 12G Required SLM-LSP ...

CDF tables and tools
Hi, I have an access database I want to import it toCRM. I downloaded the SDK from Microsfot's wesite, but all I can find is the help files. Where are the tools located, are those files on the CRM Cds? Thanks Mike Ling wrote: > I have an access database I want to import it toCRM. > I downloaded the SDK from Microsfot's wesite, but all I can find is the > help files. > Where are the tools located, are those files on the CRM Cds? Hello Mike, you find the english Microsoft CRM v1.2 Data Migration Framework in den MSDN Universal Subscriber Downloads. They also fit for the...

filters for junk email
I have added a number of words to the filters.txt file located in program files\microsoft office\office10\1033 . I also made the additions to files named filters.txt in directories above this one. The problem is that outlook is not filtering out emails with these new words ? Any suggestions ? The filters.txt file is informational only. You cannot edit it to enhance the spam filtering. You can create rules however within teh rules wizard. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the n...

how to create a mail from html page...
Outlook Express has a following feature... Create mail (Toolbar button) ->From Web Page (last menu).. Here we can select a htm page which will go as a mail body.. How can I do that in MS Outlook 2002 ?? Thanks. See if the information on the following page helps: http://www.slipstick.com/mail1/html.htm -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:bn4060$lgu$1@news01.intel.com, NetPointer wrote: > Outlook Express has a following feature......

customizing a field in customer maintenance screen
I have a field already on the customer maintenance screen that is a user defined field with a data type of string, but this field only allows 20 characters. This field is not long enough and I can't seem to figure out how to allow more characters. So, I created a new string field and added it into the customer maintenance screen. I can see the field and type text into the field but after I save it and go back in, the text is no longer there. I think I need to link this field to the table in some way but I'm not sure how to go about doing that or even what table to link it to...