Basic Pivot Table Questions

Two basic questions:

In my pivot table I click on the "Data" box to get a pop-up window of
the possible fields I can filter.  By default "show all" is checked. 
If I go through and uncheck some fields, hit "OK", only those fields
are displayed.  When I click the "Data" box again, only the fields that
I filtered to appear, and even when I hit "Show all", I cant seem to get
back that full original list.  How do I do this?

Also, is there an easy way to add a customized "group" to this list? 
For example if I have 30 fields, and regularly want to see the first
10, could I add a selection titled "1st10" to automatically grab the
first 10?  Seems I only have the choice to select all or individual,
and cannot make a custom list.

Final question, could I create a filter with additional logic?  Such as
'if category starts is "R"'.  Looking for something that allows
wildcards, similar to custom option with auto-filter in excel.

I am obviously quite new with pivot tables, if someone could answer
these questions I appreciate it, would also like a good link to
learning specifically about pivot tables.

Thanks


-- 
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7092
View this thread: http://www.excelforum.com/showthread.php?threadid=532800

0
4/13/2006 9:57:16 PM
excel 39879 articles. 2 followers. Follow

1 Replies
863 Views

Similar Articles

[PageSpeed] 39

Unlike Row or Column field items, if you deselect an item in the Data 
dropdown, it's removed from the pivot table, and from the Data dropdown 
list. To see it in the chart again, you can drag it back from the Pivot 
Table Field List.

You can't group the data fields, but you can group items in the row or 
column fields.

You can't filter a pivot table the same way you can filter using 
AutoFilter. In the next version of Excel, more filtering options will be 
available:

   http://blogs.msdn.com/excel/archive/2005/12/20/506172.aspx

There's pivot table information and links here:

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


shadestreet wrote:
> Two basic questions:
> 
> In my pivot table I click on the "Data" box to get a pop-up window of
> the possible fields I can filter.  By default "show all" is checked. 
> If I go through and uncheck some fields, hit "OK", only those fields
> are displayed.  When I click the "Data" box again, only the fields that
> I filtered to appear, and even when I hit "Show all", I cant seem to get
> back that full original list.  How do I do this?
> 
> Also, is there an easy way to add a customized "group" to this list? 
> For example if I have 30 fields, and regularly want to see the first
> 10, could I add a selection titled "1st10" to automatically grab the
> first 10?  Seems I only have the choice to select all or individual,
> and cannot make a custom list.
> 
> Final question, could I create a filter with additional logic?  Such as
> 'if category starts is "R"'.  Looking for something that allows
> wildcards, similar to custom option with auto-filter in excel.
> 
> I am obviously quite new with pivot tables, if someone could answer
> these questions I appreciate it, would also like a good link to
> learning specifically about pivot tables.
> 
> Thanks
> 
> 


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/14/2006 11:21:02 PM
Reply:

Similar Artilces:

Range problem in Pivot table
This is the problem I have with few excel sheets but not with all. When I select a range of cells including the Column headers and go for Data - > Pivot table and Pivot Chart report ... and click on Next , I can see "Database" in the Range field rather than the range (example : $A$1:$B$7). Could someone help. Hi Ravi, You have not mentioned which version of excel you are using. One possiblity might be that the range you are selecting, already has the name defined as "Database". Sometimes excel, itself defines names such as "data" or "database&quo...

Pivot Table Custom Calculations #3
I have a pivot table with the "columns" set up as Budget - Actual - Variance for each month of the year. The rows are the expenses account, such as supplies, maintenance, etc. I want to be able to add the total of the "Actual" spent through September plus the "Budget" for October through December, basically to get a projected total by expense for the year. How can I do this? Thanks for your help! You could add a column to the source data, and calculate which amount should be used. Then add that field to the pivot table. For example, with dates in colum...

Security setup question
Hello everybody, We have Dynamics GP 9.0, Active Directory 2003, and SQL 2000. Is there a document that can tell me what is the best way to secure Dynamics GP. We want to ensure that certain users have only certain permissions to certain modules/windows etc. We are looking for a document that talks about security at the granular level. Michael Hi Michael Have a look at this whitepaper. https://mbs.microsoft.com/customersource/documentation/whitepapers/securityplanning_eng.htm?printpage=false Without SQL 2005, you will not be able to get password expiry and policy functionality unles...

Pivot Table questions #2
I have several questions about a pivot table I am constructing. 1. Is there a way to have a column reference a cell with data instead of using the drop downs? example - first column in table is part number, instead of using the drop down to search for parts is it possible to reference cell A1 where a part number would be typed by the user. 2. Is there a way to lock a table so the columns can not be added or removed? 3. I do not have anything in the data items or column fields sections, is there a way to remove these sections? Thanks ...

Pivot Table Auto Update?
I'm not too familiar with VBA, so I was wondering if there's a way tha I can have a spreadsheet automatically update itself from a database. Basically, I have a database in Access, and I would like to work wit the data in Excel (as a pivot table). I would like to find a way t have Excel automatically update the pivot table when the spreadshee starts up. Is that do-able? Any examples? We'll assume that the database is data.mdb and is in th same directory as the spreadsheet. Thanks! mat -- mkaak ----------------------------------------------------------------------- mkaake'...

Newbie Question
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C3BB50.487A0580 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Exchange 2000 Native mode. Can I Re-name my first administrative group on the fly? will doing so = break anything? Anyone out there have any bad experiences in renaming = the first and only, or subsequent administrative groups - any = information would be greatly appreciated. Rummie ------=_NextPart_000_0029_01C3BB50.487A0580 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Enco...

Workflow question 03-16-06
There is a field on the Administraton tab of the Account form called Prefered User. Is it possible to use that field in a Case wrokflow. I am trying to use a workflow which will determine who the this field in an IF statement but it does not seem to be available to me. ...

How I can switch row/column in pivot chart?
Hi, I have the new office 2007, in excel I try to draw pivot chart, I succeded. But I want to switch row into column and column into row. When I try to do that tis button bocomes invisible while I can do in PPT. Please help me!! Regards, Tuba Kesen Umar Pivot charts in Excel 2000 through 2007 only allow you to plot series in columns. However, you could rebuild the pivot table so that your old column fields are located in the rows area and vice versa. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "kesen...

Storage Limits delegation question
I am trying to create a delegation for a user account manager group so it can change the storage limits on a user account. This is a brand new forest and exchange 2003 setup and all the users are new accounts. I have added the delegations listed below and I can successfully update the fields only after an admin does it first. Is there a way to grant my user account manager group the ability to do this without intervention from the admins? When someone in the user account mangers group tries to set any of the values I get an Access Denied LDAP Provider id 80070005 Microsoft Active Di...

HELP!!!! PIVOT TABLES
I am 17, and have just accepted a new job to start in 2 weeks, only problem is, it involvs using pivot tables. Please don't think I'm stupid, but have never used these before and am not even sure what they are, let alone how to do them!!!!!!! Any help would be greatly apprieciated!! To summarize the data in a table, you can use a Pivot Table. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Dawn (17) wrote: > I am 17, and have just accepted a new job to start in 2 weeks, only prob...

Dumb MFC Question
I have a really dumb question. In my code when I create a CPen, CFont, CBrush, ... and select them into a device context (CDC or CClientDC or other MFC wrapped DC). These objects are not cleaned up. Johan Rosengren has been helping me with some issues (Thanks Johan for you time away from your family this weekend) and he said that I needed to delete the CFont and other objects. That got me curious and I looked at the destructors of these objects (that can be selected into the DC) and sure enough, they don't call DeleteObject method to release them from the system. These include: CPen C...

Searching for first and last in a table to chart (gantt)
I am using a rather large table and need to search for the first and last cell that has a value entered in a particular row and return the cooresponding date in that column. The table has a few columns showing task name etc that there is serveral rows that represent a date. In each row a number is put in a column that matches a date or is left blank. I have tried to llustrate this below __ |A1__|___B1___ |__C1_|__D1_|....|__E1_|__H1_|__I1_|__J1_|__K1__| A2|Task | Sub Task |Phase| Name| |01/05| 02/05| 03/05|04/05| 05/05 | A3| Xt Xs Yp Xn | null | ...

VB question
I would like some advice please. I am trying to learn VB6. I was recommended a very expensive book, Programming in Visual Basic 6.0. Before I decide to keep this book. I would like to know if the practice/examples in this VB book can be used in Microsoft Visual Basic Access 2003? Thank you for any advice. Sorry, I don't have a copy of the book, so I can't give a definitive answer. However, the language components of VB and VBA are very similar. The major problem you may run into is the fact that the controls available for forms in VB are quite different that for forms in Ac...

Table font size changes
If I try to make the font size in a table smaller so I can fit more on a slide, after I save and close the document, when I reopen it, the font size in the table has gone to a larger size. In article <4451947A-8D54-46A8-A870-C6BD6BCD8245@microsoft.com>, JayR wrote: > If I try to make the font size in a table smaller so I can fit more on a > slide, after I save and close the document, when I reopen it, the font size > in the table has gone to a larger size. This doesn't ring any bells with me, but in case it does with someone else, start by telling us which ve...

Selecting dynamic number of columns into fixed-structure table
I have a table with the following columns (the Pen# columns are all REAL data types and all columns support NULL values): dtDateTime Pen1 Pen2 Pen3 Pen4 Pen5 Pen6 Pen7 Pen8 Pen9 Pen10 I have a stored procedure that returns data similar to the following: dtDateTime TT_S02_20 TT_S02_30 TT_S02_50 ----------------------- ------------- ------------- ------------- 2010-06-26 00:00:06.653 148.1323 115.5447 124.679 2010-06-26 00:00:21.687 148.5214 115.5...

If Vlookup Question
Hi, I have created the following equation using VLOOKUP... =VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE) The result varies depends on my data, but I would like any result that is $0 to equal "***" Does anyone have any ideas? Thanks so much for your help!! Hi Brent, If you are only wanting results that are under a certain amount you can use: =if(VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)>A1,0,VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)) I placed the "upper limit" in cell A1. If you are wantin...

HQ SO Question about costs for transfers
I have a location in Country A using store ops and am opening another location in Country B. I will be getting HQ soon and store ops for Country B. All items will be received in Country A, so there will be a cost associated with the item. However, when I transfer goods to Country B to put into thier stock, I will need to change the cost to account for duty and freight. In other words, the transfer in for Country B will need to have a different cost from the transfer out from Country A. Does HQ and/or SO have the capability to do this? Jason P.S. See also my thread about adding freigh...

Ranking question
Hello - Each individual has many records that are ranked as follows: Jim 1 Jim 2 Jim 3 George 1 George 2 Bob 1 Bob 2 Bob 3 Bob 4 I need a list returned with each individual's highest ranking e.g. Jim 3 George 2 Bob 4 How do I get to this result? -- Sheldon Select FirstName, Rnk = Max([Rank]) From YourTable group by FirstName "Sheldon" <Sheldon@discussions.microsoft.com> wrote in message news:4CF4060C-8E9B-40FF-...

Importing data question
I have a text document that contains unique job numbers, location of th job, date and time last scanned, number of breakages, days in process customer number etc. Several of the fields change multiple times durin a day but job number is unique and would dissappear only once a job i completed. I can figure out how to get external data into an exce spreadsheet and how to refresh the data but my problem is I want to b able to add comments/notes to each one (customer was called regardin delay/part on back order/called customer on xx/xx/xx etc.) Each time refresh data my notes are in the row but ...

pivot table percentages
Using XP and Office 2003 I created a pivot table to analyze a group of email address extensions. (".com" or ".org" or ".gov", etc.) The pivot table returned the count of each extension type and the grand total. Now I want to create an additional column that will calculate the percentage of the total for each extension type. I built the first formula at the end of the first row of data in my pivot table. The problem I am having comes when I try to copy that formula down or even when I try to copy and paste special that formula. I do not understand the w...

Access Switchboard Question
I am trying to open a file in another application (MS Word) from my switchboard in access. I am using Office 2003. How can I do this? Open the switch board in design mode and display the CONTROL TOOLBOX. Verify that the WIZARD button is activated (magic wand trailing fairy dust button) Click the command button tool. In the CATEGORIES column select APPLICATION and in the ACTIONS column select RUN MS WORD. Click NEXT Select the PICTURE you want to use or the text prompt you want to use and click NEXT. Give the command button a name (optional) and click FINISH. Save the form and give ...

eConnect and Trade Discount question
I'm currently using eConnect to insert SOP Transactions. All is going well except for the insertion of Trade Discounts. When I send the orders to GreatPlains I do not know the actual pricing so I have used the field "DEFPRICING" (default Pricing) and set it to 1 or to default the price in. My problem is I know the Trade Discount percent for each order but I do not know the actual amount since I do not know the actual numbers till tne transaction is over. Can anyone tell me if there is a way to flag that the Trade Discount is a percent and not an amount? Also I have pla...

Programming Question: Multi-Player Poker Game
I have a programming task and was wondering if anyone has some thoughts on the best way to achieve this. I am programming a mulit-user poker game using ASP.Net 3.5 and AJAX. I do not want to write a Java applet or any type of standalone program that would reside on the user's computer. I just want the user to use a browser as the user interface. I have 5 users at the table. Each user has their own browser session (i.e. they are located on different computers around the world). Player 1 is the first player to act. He bets $5. My question: How can the other users be n...

Virtual pc 2004 question
I am installing the microsoft virtual pc 2004 and the service pack 1 for the first time and I am getting the following message: Virtual PC could not open the Virtual Machine Network Services driver. Access to the external network and host will be unavailable to all virtual machines using virtual networking. Virtual machines using virtual networking will still be able to access other virtual machines using virtual networking. To fix this problem, re-enable the Virtual Machine Network Services driver on one or more ethernet adapters or reinstall Virtual PC. I do not know what the Virtua...

Subreport always appears blank (even during basic testing with no parameters!)
I have a subreport in my report that takes a set of basic parameters. It does not appear on my main report when the main report is run. As a test, I decided to create a simple subreport with no parameters and only a text field. It also does not display on my report. As a further test, I decided to create an entirely new project with only a main report and a subreport that displays the word "test". It will still not display on my main report. There are no errors at any stage. Does anybody have any idea as to what could be wrong? Zack Is it not working in preview tab...