Working with Fields in Pivot Tables in Excel

In my source data, I have a 'Date' field. In my pivot table, I want to use
the 'Date' field twice:

* Once to sort by 'Year' (i.e. simply changing the format to 'YYYY')

* And a second time to sort by 'Month' (i.e. simply changing the format to
'MMMM')

Can I do this without the requirement of adding fields to my source data?

Thanks.

Is there a way to modify Pivot Table so that "Sum of, Count of", etc is not shown.
For example, have Table (& Pivot Chart) show "West Region" instead of "Sum of West Region"

Thanks

Do you know how to specify to Excel not to care about the case
sesitivity of Pivot Tables Heading fields?

it is very annoying: when i want to refresh my saved pivot table with a
heading that has changed from lower case to upper case I get an error.

Thx,

Chris

Hi,

I was working on a Pivot Table Report and closed the Display list. Now
everytime that I open the Pivot Tables the field list does not display at
all. I have tried restarting, reopening, and clicking display fields(what is
listed on the MicroSoft Website). Nothing works. Can anyone help me get the
fields back on?

I'm transforming multiple excel sheets into a single sheet using pivot
tables. There are frequently multiple items in the data column which appear
as 'count of'. Is there a simple way to get all the entries to be 'sum of'
instead of doing each one manually?-we're talking about 100 items
occasionally!

Oh, and if you can answer this without using code, I'd appreciate it, Cheers
now, Rob.

why? o why? o why?

I cannot add a Pivot Table to my excel sheet, because I keep getting the message "Pivot Table Field Name Invalid". It has an automatic name, not my fault (thanks MS-B.S.). If I select another sheet which contains only one list of information I can create one, but how useless is that, when I want the real power of this feature unleashed on the whole database (sheet). What is wrong with this function of Excel and how do I work around this cumbersome issue that MS has caused?

thanks in advance,
John

Hi,
I'm working with two pivot tables on seperate worksheets within the same workbook. Both have date fields and I wanted to know if its possible to link them. So that when I change the date in the first pivot table its reflected in the second.

Any help would be appreciated.


thanks
damo

Hello,

In the attached Excel file, there is a pivot table.

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).

Is this possible and how should I do it?

Any help is appreciated, thank you!

Xavier

Hi, anybody know how to lock the fields from being moved (such as a page by field being moved down to a column field etc...) but still allow for drill down selection? I don't want end users who are viewing the pivot table to move the fields around and mess up the formatting.

Is this possible.

Thanks, Don.

I have a Pivot table with values for over 30 colomns of data. As the default setting for numbers is to count the data and I need to average it I am changing each of these coloms using the value field setting option.

Is there any way I can change all of the coloms in one go? I have tried selecting data across the colomds and right clicking to value field setting but it only changes the first colomn.

Hi all,

I have a pivot table which reads from a fairly large list of entries (about 17000 records). The table was tedious to build, so I typically update the data, then refesh the table to produce the new results. The issue I have is that the pivot table field filter dropdown boxes tend to accumulate obsolete data over time and I cannot figure out how to reset these so that only the current values are shown.

For instance, I have a field "Prog" which lists various programs in progress. When I created the table, Prog1, Prog2, and Prog3 existed, and the pivot filter dropdown listed all three. Over time, some of these programs completed, and the records were deleted so data no longer include Prog1 and Prog2. New programs Prog4 and Prog5 were also added. However, the field filter list box shows all 5. (While this is not a big deal with 5 projects, it is wihen the list climbs to 50.)

If anyone can tell me how to reset this without completely rebuilding the pivot table, I'd appreciate it. I'm using Excel 2003 SP3 and Windows XP.

---GJC

Hi All,

I am pretty unfamiliar with pivot tables, but am trying to create what I think is a fairly straightforward pivot table, but am having trouble with formatting the headers the way I'd like. My Pivot table currently looks like the one below.

Region Data Total
Region1 Sum of Market Cap ($m) 4,544
Count of Name 51

But I'd like SUM of Market Cap and Count of Name to be column headers, so that I can have all data for a region on the same row. (The pivot table will need to be copied daily into a PPT presentation). Is this possible?

BTW, using Excel 2003 in this example.

Edit: The forum software removes extra spaces, so I'm not sure how to post a mini-table. Hopefully you can still get the general idea of what is going on. 3 columns with 2 rows of data for each region. I want to keep the 3 columns, but have the labels in column B be the column headers instead of row labels. Hope this makes sense.

Hi there!

I've created a document with many pivot tables. After creation of the entire document, a new addition was requested, which called for a calculated field.

I went to the first Pivot Table, created the calculated field and inserted it. Worked great.

But now I need the same field in every pivot table in the workbook.

This calculated field shows up in the field list, but will not allow me to drag it into new pivot tables. Error message is "The field you are moving can not be placed in that Pivot Table Area". But it is a calculation/field created for that area. (And worked perfectly in the first pivot table)

I've tried going to the pivot table toolbar, finding the custom calculation and hitting ok. The pivot table refreshes, but does not add the calculation.

I've even tried recreating the custom calculation in each successive pivot table, but of course it will not let you name them the same. So even this time-waster will not work.

Am I approaching this wrong? Any suggestions?

Thanks!

Hi All,

I have a spreadsheeet in work that I have inherited. On this worksheet we have a list of customers with their various details in the column headers such as name, telephone number, email and so on.

Now the tricky part is we have a several column headers for the areas they work in. To simplify what I am trying to explain. Let us say they are states. So for example a customer could work in NY, CA, CO, MT and so on. Now this is usually yes or a no. For yes we simply put an 'X' in the customer row for each state they work in. So if a customer works in New York and California then we would put an 'X' under both of those columns.

This works fine, however if I now want to run a privot report to see how many states one of these customers work in then it becomes tricky because each state has its own column heading and therefore a field.

How can I group a range of column headers and call them say 'States' and then from that run a pivot table report to find out what states my customers work in and have a cout of this also?

I am using Excel 2007.

Thanks in advance for any help offered.

When I change names or rearrange columns in my data, my Pivot tables that use that data will break.

Any way around this?

Help!

When I try to drag a particular field into the Page area, Excel tells me that
"the field you are moving cannot be placed into that Pivot Table area". The
data in the column concerned is text, so I can't understand it's not allowing
me to place it there. I've looked at the field settings and there doens't
appear to be anyhthing untoward there. The message box is next to useless at
explaining why I can't drag it into the page area (but I can drag it to the
Row or Column area)- does anyone have an explanation?

Hi All,

I have arranged data using a pivot table and want to display the data
using a pivot chart, which I have been able to do.

I have recorded a basic macro which should (in theroey) change the Value field in the pivot table. However when i run the macro I get the following error message:
Run-Time error 1004
Unable to get the PivotFields property of the Pivoit Table Class.
The code is as follows:
Code:

Sub Water()
'
' Water Macro

    Sheets("Sheet1").Select
    Range("I2").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Result"). _
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Water"), "Count of Water", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
    Sheets("Flux Levels").Select
End Sub

When I run the deubg option the error seems to be occuring on the 3rd and 4th lines of the code.

Any suggestions?

Thanks!

In a pivot table, how do I pass the selected cell field values (ie the values of row & column data for the selected cell) to VBA? I just can't find the correct syntax... I think it's something like

ActiveCell.PivotField("fieldname").value but that doesn't work.

(In Access, I can use Me.PivotTable.ActiveObject.Cell.Recordset.Fields("fieldname").Value )

Have looked through VBA's locals window without success.

Thanks in advance.

I am writing code for 2003 users using Excel 2007.

If the following selects all of the MultiplePage Items in a pivot table field:

Code:

    ActiveSheet.PivotTables("PT1").PivotFields("Proc"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("Top_Defects").PivotFields("Proc").CurrentPage = _
        "(All)"

What code do you use to deselect them all at one time? Once I deselect them all, I want to activate only 2 or 3 of the items using the PivotItems.Visible command (I will put those items together with the two lines above using a "With/End With" command.

I have looked all over for a way to deselect all the items at one time and cannot find the code. Any help is appreciated!

Thank you,

Charles

Hi

I have data on sheet4 which has 13 columns in total 1st being the heading. Now i want to create a pivot table on sheet5. Problem is that the no. of rows change and also the number of columns. Sometimes the columns will be 12, or 6 or 8. I have a macro to dim the selection range and then it creates the pivot table on sheet 5 but does not add heading to row label and rest of the columns to datafield as i dont know the code to specify for changing columns.

Please Help.!!

Hello,

Have a table of sales people,

Want to set rewards program for top sales people based on n# of sales that exceed $X.

so for the source range that feeds the pivot table, I have a vba decision derived decision column title "Huge Sale" that is applied to each sale (does this sale exceed $x, if true then put "yes" in decision column, else "no").

Now, what I am trying to figure out, on my pivot table, I want it only to show salespeople who have had at least n# of "Huge Sales". Is there a way I can set a filter on my pivot table to show only row groups where Count of Huge Sales exceeds n?

If I can do that, user inputs what a huge sale is ($ threshold value) and how many sales before reward (# threshold). And the table would just spit out the sales people to reward.

Any ideas?

Current Row labels a
Huge Sale (show "yes" only)
Salesperson

Data:
Count of huge sale

Hi there.

I'm creating a budget spreadsheet with multiple pivot tables on each worksheet. I'd like to disable the ability to select certain fields to prevent novice users from using them by accident and getting confused.

The thing is that I already have code. And it works on almost everything. Then it just stops working. For example, on the Apr and May sheet it worked on every pivot table on the worksheet. Then on June it only worked on 2 of them. Then on July, it worked on the same 2. But it did work on all the pivot tables on each page previously. So I assume this is some kind of error or glitch. Can anyone provide any help? Or can anyone tell me how to design it to run on a selcted pivot table instead of Active Sheet?

Thanks so much!!!

The code I'm using is:

Code:

Sub DisableMonthSelection()
  
Dim pt As PivotTable

  
Set pt = ActiveSheet.PivotTables(1)

 pt.PivotFields("Month").EnableItemSelection = False

End Sub

Hi - need help with creating a CALCULATED field using a VLOOKUP inside a pivot table - simplified version of what I need to do is below (assume tables start at A1 in the top left corner)

I'm OK with the calculated field (if its possible) but its the VLOOKUP in a Pivot Table I'm struggling with.

PIVOT TABLE Count of 'dog or cat columns' Column Labels Row Labels dog cat AB1 34 54 AB2 55 22 AB3 22 17 AB4 11 34 AB5 76 22 AB6 45 87

From which I want to create the following CALCULATED FIELD:

=VLOOKUP(CONCATENATE($A4,B$3),A2:B13,2,FALSE)

And lookup from these two fields - which I need to place in the pivot table.

LOOKUP FIELDS (need to go into Pivot Table) AB1DOG $5.00 AB2DOG $10.00 AB3DOG $15.00 AB4DOG $20.00 AB5DOG $25.00 AB6DOG $30.00 AB1CAT $2.00 AB2CAT $4.00 AB3CAT $6.00 AB4CAT $8.00 AB5CAT $10.00 AB6CAT $12.00

Thanks in advance for your comment!

Hi folks,
I'm new to this pivot table stuff. I have a Date column in Row, and then Footage in Values (summed). What I would like to do is sort Date by week without making groups and then renaming them. From what I've read, I need to use the Group Field function in PivotTable Tools > Options, but I can't click on it. I opened both Date and Footage and unchecked all the blank values, as many people have suggested, but I still can't use Group Field for some reason.

Any assistance would be greatly appreciated.

Hi all,

New to the forum and had a quick look through the search and couldnt find this query already here.

Basically I'm looking for VBA code to remove all of the Value Fields present in a PivotTable. I dont want it to be Field specific such as the below code that I'm currently using:

Code:

ActiveSheet.PivotTables("PivotTable2").PivotFields("Product ID"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Target"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales vs. Target"). _
        Orientation = xlHidden

More like something similar to this: (But for Fields instead of Filters!)

Code:

ActiveSheet.PivotTables(1).ClearAllFilters

I have attached an example to show the starting point and the desired result.

Any help would be appreciated.

Thanks!


Please see below:

I have an Excel 2007 Pivot table that has a calculated field called Margin. I have the Margin field in the "Values" part of the pivot that is set to "sum" in the value field settings.

I want to be able to filter out all the positive margins and see only the negative margins. For the life of me I can't seem to figure a good way to do this. Below is a shot of the data.

http://www.beachtomountains.com/images/MarginFilter.jpg

TIA

My pivot table is setup like this:

Name.........Pass..........Fail.........Total
Joe.............3...............1.............4
Nancy.........4...............1.............5

Where the Pass and Fail numbers are actually one field (QC Result) broken out by the two values that populate that field, Pass or Fail. The pivot table then added the Total column at right as a simple sum.

In trying to add a calculated field, Excel is only letting me divide one existing field in the list by another existing field in the list. But the data on which I want to calculate is actually from one field, QC Result. I would like to do the following formula: (Count=Pass / Total Count).

And ideally would like it to show as a single column to the right of the "Total" column.

Am I missing something here, or do I need to setup my pivot table another way?

Hi All,

Does anyone have any ideas for my problem below:

I am using a pivot table. I have inserted a calculated field as follows:

= 'Deal %' * (Centre + End)

This works fine until it gets to the subtotal (which is needed).

Deal % is set as an Average and in the Sub Total part it answers 80% (Centre and End are both Sum Fields). However the calculated field at the sub total section it uses the sum of the averages in the Deal % column and not the Average (as displayed in the Sub Total section)?

So I tried the following:

= Average('Deal %') * (Centre + End)

This doesn't change anything. Does anyone have any ideas for how to fix this?

Many thanks in advance.

Ok, I am extremely new to VBA so please forgive me. In Excel I have 4 pivot tables all showing the same field names, but charting different attributes of each. For example, I have 12 different cars. All 12 cars show up on each of the 4 pivot tables, but 1 table is charting the motors (jan - dec), the 2nd is charting the windshield wipers (jan - dec), the 3rd the tires (jan - dec) and the 4th the color of the cars (jan - dec). All I would like to do is when I change the car names on 1, have it update automatically on the other 3 pivot tables so I don't have to make the same change to 4 charts, 4 times. Is this even possible using VBA? And if so, is it difficult? Keep in mind, I have no VBA knowledge. All I have done with VBA was find some code on the internet about how to programmatically clear old items remaining in pivot field dropdowns. I took a chance, pasted the code in for the entire workbook, and it worked, just to give you an example of my VBA knowledge...

Hi Everyone,

Is it possible to filter a PivotTable field using a "contains" keyword from another cell?

I tried recording a macro and got results below:

Code:

Sub CatFilter()
'
' CatFilter Macro
'
'
ActiveSheet.PivotTables("PivotTable6").PivotFields("Category"). _
ClearLabelFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields("Category").PivotFilters. _
Add Type:=xlCaptionContains, Value1:="Green"
End Sub

I'd like to replace Value1:="Green" to reference by cell G25 where the keyword is listed. Attached is a screenshot of what I attempting to do.

Thank you in advance for your help!!!

Is there a way to populate a userform comboBox with Pivot table fields? I have a "Department" field in the pivot table that is not constant and would like to have the combo box reflect the changes. Thank you

Hello,

I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.

=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))

The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.

The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.

Else, if neither condition exist, WMAPE is Abs Error / Actual.

It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.

I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?

Any suggestions?

I am having trouble getting the right VBA code to clear all filters from all fields in a pivot table in Excel 2003. Below is what I have tried, but each attempt returns the following error: "Run-time 1004 error. Unable to set the Visible property of the PivotItem class."

Dim pt As PivotTable
Dim pf As PivotField
Set pt = Sheet1.PivotTables(1)

'Attempt 1
For Each pf In pt.PivotFields
pf.ShowAllItems = True
Next pf

'Attempt 2 For Each pf In pt.RowFields For Each pi In pf.PivotItems
pi.Visible = True
Next pi
Next pf
Even running the following exact code recorded by the macro recorder returns the same error: With ActiveSheet.PivotTables("PivotTable1").PivotFields("Service Type")
.PivotItems("Account").Visible = True
' replicated for each PivotItem
End With
Setting the Visible property to FALSE works, but not when setting to TRUE.

I have searched the web, and seen other users raise the same issue, but have not seen a solution.

Is this a bug in 2003?
Is there a workaround?

Thanks in advance

Good morning!
I have a spreadsheet with a pivot table that has the row fields and page fields controlled by macro buttons. I would like to add an additional button for one of the fields to be placed in the last row field regardless of what is currently there.

Example: One possible row combination based on the macro buttons is to have the row fields SE, SD, SR another would be to have FC, FD, SR, SRG. Now to either of these I would like the user to have the option to add the field PG as the last row field. With the first example that would be in position 4, in the second example it would be position 5.

Is there code that would allow me to define the position as the "last" row field?

Please let me know if you need better definition of the issue. As always, your help is so greatly appreciated!

N

Hi,

In Excel 2007, is there any way to highlight and select multiple fields from the "PivotTable Field List" at the same time? I realise you can 'tick' each field you want to enter, however, I have over a 100 and it would be useful if you could simply highlight the list!

Thanks!

Dear Friends,

I am using MS-office 2007. When I created the Pivot Table, I don't know why the field list is not appearing. I reinstalled entire application also. Even I tried with the pivot tables which are created earlier also. But of no use.

Any sol. for show field list in VBA macro. so that I can activate it.

Thanks in advance.

acsishere.

I receive following errors when I try to sort calculated field of a Pivot Table:

"AutoSort and AutoShow cannot be used with custom calculations that use positional references. Do you want to turn off AutoSort/Show?"

Are there any workarounds?

This forum won't let me upload a sample file but this is what I have:

SOURCE DATA:

Header Row: Asset, MV and Date
Data Row 1: BOAMS 2010, 200, 6/25/2010
Data Row 2: MLMI, 100, 6/25/2010
Data Row 3: MLMI, 600, 6/26/2010
Data Row 4: BOAMS 2010, 50, 6/26/2010

PIVOT
Row Label: Asset
Values: Sum of MV and Sum of Change (Calculated Field shows difference of MV between two dates)
Column Labels: Sum of Values and Date


I am using Excel 2007.

I'm trying to insert a calculated field into a pivot table and I'm having A LOT of trouble. Everything I read says to just click on the pivot table and the Pivot Table Tools tabs appear (excel 2007). No problem so far. Now you're supposed to click on the "Options" tab. Again, no problem.

From here you are supposed to select the "Formulas" option. But it's ALWAYS greyed out. No matter where I click on the pivot table or the PivotTable Field list, it's always greyed out. Any help...anybody?

I created a calculated field in a pivot table with the intent of creating a custom formula for that field which would calculate an average of 40+ other pivot table fields. When i go to the "Pivot Table ->Options->Formulas->Calculated Field" window and enter the formula as follows Excel limits me to 255 characters:
Average ('field 1', 'field 2', etc.)

When I tried using a colon in the formulas to indicate a range of fields to be averaged Excel would not accept the formula:
Average ('field 1':field 40')

Any ideas on how I can accomplish this?

Hi all,

I have a rather lengthy macro that concules by refreshing two different pivot tables. This macro has been in use for several months now with no issues but yesterday the user called me and stated that she was getting an error.
The error is "Run-time error '1004': The Pivot Table field name is not valid. To create a Pivot Table report, you must use data that is organized as a list with labeled columns. If you are changing the name of the Pivot Table field, you must type a new name for the field."

The potion of the code where the error occurs is on the "PivotCache.Refresh" line of the following:
Sheets("Quote_RFQ_Received").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("B4").Select

It appears that the code doesn't like the name "PivotTable3" -- although it didn't have a problem with it before yesterday.

I found a similar thread to this on this message board but the responses were to ensure that the PT name in the code matched what is actually on the PT in the spreadsheet. I followed the different examples to determine the actual table name and I can confirm that the name in the code matches PT field name.
I also confirmed that each of the three PT columns had headers.

I tried the macro on a couple other machines (besides the daily user) and the same error occurs. Now here's the really odd thing; when I run the macro on my machine, it works fine.

Any ideas on 1) why it has worked for the past couple months and all of a sudden an error occurs, 2) why the macro works fine on my machine but not others, and most importantly 3) what is the fix.

Thanks for your help,

Shawn

How to put same data field in data area multiple times and use Sum Of Filed, Count of Field etc ?

I have a SharePoint list with a Choice field that will allow multiple values. I would like to be able to analyze the data in an Excel pivot table.

There are no problems exporting the SharePoint list to Excel, however when the Choice fields (that permit multiple values) are exported they are placed in a column with the multiple choices separated with ";#".

For Example:
Products Capabilities
Shovel Dig;#1;Bury;#2;Cover;#3
Rake Dig;#1;Cover;#3
Hammer Dig;#1;Hang;#4
Pitchfork Cover;#3

The functions are not mutually exclusive and a product may have one or many capabilities.

I am looking for the best method for normalizing the multiple-value fields so I can analyze the data using a pivot table.

Any suggestions would be GREATLY appreciated.

Thanks!

Hi,

I have a pivot table with 3 page fields, PageF1, PageF2 and PageF3
Each has a number of values including (All)

I want to go through every combination of the pagefields and if the pivot table has actual values in the data region to output to a pdf file with a filename format: Pagef1 - PageF2 - PageF3 .pdf

The code goes through each page combination, but
I cant exclude page fields with (all)
i cant capture the names of the page fields of the combinations that have data in the data body range in a filename and use that filename to output the pivottable

I am using the following code that i found at http://www.contextures.com/xlPivot09.html

Your help is welcomed

Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field

On Error Resume Next

Dim pt As PivotTable
Dim File As Variant 'somehow this would be used to define the filename need
Dim pf As PivotField
Dim pi As PivotItem
Dim RngToSum As Range

Set pt = ActiveSheet.PivotTables.Item(1)
Set RngToSum = Nothing For Each pf In pt.PageFields For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name

'this is the data range of the pivottable
Set RngToSum = pt.DataBodyRange
If Application.WorksheetFunction.Sum(RngToSum) 0 Then
ActiveSheet.PrintPreview 'use this for testing
'Specify acrobat file name and type
'This portion does not work, need help:
Set File = pi.value.pdf
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= File, _
'Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=True


End If
Next
Next pf
End Sub