Free Microsoft Excel 2013 Quick Reference

pivot table field settings % of...

i want to calculate an item as a percentage of another item in the same row
of a pivot table...i have the following PT columns & rows

col a col b col c
state net rev cost

ca $1.00 $0.50

i want to show the cost as a percentage of the net rev without having to
create calculated fields...i htought that maybe the field seting sho data as
% of would work but can not seem to get the result i want...help!


Post your answer or comment

comments powered by Disqus
I tried to get around the problem of the pivot table field setting
defaulting to Count instead of Sum by running a macro of change the
setting from Count to Sum. However, when I tried to run the Macro, I
got error message of run time error 1004, unable to get the pivot
fields property of the pivot field class.

Well, is there any way around this?

I'm MS-Excel 2003 pivot tables which connect to an OLAP database. I am trying to set my subtotals in the pivot table to "AVERAGE". Currently, they're defaulting to the "SUM" setting.

I right click on the cell (I do this for both the cell and the cell heading...same result) and select "FIELD SETTINGS". This brings up the Pivot table field window. The only options that I have that are not ghosted out are "AUTOMATIC" and "NONE".

I understand that I need to tick the CUSTOM radio button under the Subtotal heading, but i am unable to do so, as it's ghosted. Consequently the Sum/Count/Average/Max/Min... options are also unavailable.

This is the case with all of my spreadsheets that connect to this OLAP database.

Is there a global setting that can be reset on Excel? Or is this a setting in our OLAP cubes? Or is there something else?

Scott

Is there a way I can apply a different default 'summarize by' value for my
pivot table fields? as of now, it defaults to 'count' and i need it to be
'count nums'. thanks

Hi,

Columns : 12/27/20009, 1/3/2010, etc...
Rows : Count of Calls Handled, Count of Calls adbandoned etc....

What I would like to do: is loop though all the worksheets and within each worksheet, change all the pivot table field setting from Count to Sum.

Here is the code I have so far:


	VB:
	
 
 
Private Sub formatAllPivotTableDataArea() 
     
    Dim ws As Worksheet 
    Dim pvtTable As PivotTable 
    Dim pvtField As PivotFields 
     
     'don't allow screen updating because it takes time
    Application.ScreenUpdating = False 
     
     'loop through each worksheet
    For Each ws In ThisWorkbook.Worksheets 
         
        MsgBox ws.Name 
         
         'loop through each pivot table in the current worksheet
        For Each pvtTable In ws.PivotTables 
             
             
             'refresh the pivot table
            pvtTable.PivotCache.Refresh 
        Next pvtTable 
         
         'tell the user you are done with the refresh for each pivot table in current worksheet
        MsgBox "The worksheet " & ws.Name & " and the pivot table are refreshed." 
         
         
    Next ws 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have an Excel Pivot table (Excel2003) reading data from a SQL2005 Analysis Services data source. On one of the columns I want to show Running Totals. I select Field Settings, then Options, then select 'Running Totals in' and my base field. After Clicking OK, the values in my column are changed to #N/A. The % of Row and % of column functions work fine. But the other settings all return an #N/A result. What am I missing?

how do you copy the field settings of one field to another within the same
pivot table?

Hi,
When you drag an item from the field list into the pivot table, how does
EXCEL decide what field setting ( Count, Avg, Stdev) to use. Sometimes it
choses Sum and sometimes it choses Count, even in the the same pivot table.
I would like to set it to always use average or stdev as a default.
Thanks.

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

When I drag fields into a pivot table, the field setting is automatically set
to "count". Is there a way to change this to default to "sum" so I don't
have to go into the Field Settings... dialog for every single blasted column
of my pivot table?

Thanks.

And there's no way to set the default.

Jonathan Cooper wrote:
> I think it was Deborah D. that answered this same question for me. If the
> data contains blanks, is assumes that those are text, and does a count. If
> it is all numbers, then it uses sum.
>
> "MMesarch" wrote:
>
>
>>Hi,
>>When you drag an item from the field list into the pivot table, how does
>>EXCEL decide what field setting ( Count, Avg, Stdev) to use. Sometimes it
>>choses Sum and sometimes it choses Count, even in the the same pivot table.
>>I would like to set it to always use average or stdev as a default.
>>Thanks.
>>
>>--
>>Mark Mesarch
>>School of Natural Resources
>>University of Nebraska-Lincoln
>

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

Hello,

Everytime I create a Pivot Table in Excel 2003, excel by default puts the field settings for each of the columns to Automatic, creating a Total for each aggreate column, which is very annoying. I have to manually go in in each field and change field settings > None. for each column, since no copy and paste special function to make all the columns have same subtotal >none.

Is there an easier way to do this, perhaps in 1 click, to make all columns by defualt have subtotal?

Thanks

I have a slicer which is connected to the pivot table. What i am trying to do is "Find the slicer item selected - Store that item in a variable - look up for that item in OLTP database and find the common items - add those items in the pivot table fields list.

Here is my code;

	VB:
	
 ex_getSl() 
     
     
    Dim myStr As Variant 
    Dim VisibleListIems As Variant 
    Dim sc As SlicerCache 
    Dim sL As SlicerCacheLevel 
    Dim sI As SlicerItem 
     
     
    sCount = ActiveWorkbook.SlicerCaches.Count 
     'n = 1
     'For n = 1 To sCount
    Set sc = ActiveWorkbook.SlicerCaches(1) 
    Set sL = sc.SlicerCacheLevels("[Dim Ratio].[Ratio Name].[Ratio Name]") 
     
     
    For Each sI In sL.SlicerItems 
         
         
        If sI.Selected = True Then 
             'MsgBox sI.Name
            var1 = sI.Name 
            rn1 = Mid(var1, 28, Len(var1) - 28) 
             
             
            Dim i As Integer 
            iLastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 
             
             
            For i = 2 To iLastRow 
                If Sheets("Sheet1").Cells(i, 1).Value = rn1 Then 
                    rVal1 = Sheets("Sheet1").Cells(i, 1).Offset(0, 1).Value 
                     
                    Dim str As String 
                    Dim strcat() As String 
                    strn = "[Dim Ratio].[Ratio Name].&[" & rVal1 & "]" 
                    strn1 = Chr(34) & strn & Chr(34) & "," & strn1 
                     'strn1 = strn & "," & strn1
                     
                End If 
            Next i 
             
             
            myStr = Mid(strn1, 1, Len(strn1) - 1) 
             
             
            Sheets("Msheet").PivotTables("PivotTable4").CubeFields("[Dim Ratio].[Ratio Name]") _ 
            .EnableMultiplePageItems = True 
             
             
            ActiveSheet.PivotTables("PivotTable4").PivotFields( _ 
            "[Dim Ratio].[Ratio Name].[Ratio Name]").VisibleItemsList = Array(myStr)  - ERRORRRRRRRRRRRR 
             
            ActiveSheet.PivotTables("PivotTable4").PivotFields("[Dim Ratio].[Ratio Name].[Ratio Name]").VisibleItemsList =
Array( _ 
            "[Dim Ratio].[Ratio Name].&[Rat3]", "[Dim Ratio].[Ratio Name].&[rat2]", "[Dim Ratio].[Ratio Name].&[12]") 
             
             
        End If 
    Next sI 
     
     
     'Next n
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The error is "Application defined or object defined error"

myStr will look like "[Dim Ratio].[Ratio Name].&[Rat3]", "[Dim Ratio].[Ratio Name].&[rat2]", "[Dim Ratio].[Ratio Name].&[12]"

the next line of the error line will work fine. Please somebody help to solve this.

Hia Everyone,

I'm using a workbook that contains 3 pivot tables. One field in all pivot tables is the same. I would like to create something like a list box on the first worksheet that feeds to this field of the pivot tables.

The idea is that a user can select an item from a list box on the front sheet which will then change the field setting of all the pivot tables in the workbook and refresh them.

I figured I could set up a button on the first sheet that when a user clicks it, a macro will activate that takes the value selected from the list box, feeds it to the pivot tables and updates them all.

I recorded a macro that I thought would do the job but it doesn't!

ActiveSheet.PivotTables("PivotTable2").PivotFields("BB").CurrentPage = Range("I7").Select

I intended the pivot table to look in cell I7, change pivot filed BB to this value and then refresh.

Any ideas? Maybe a macro isn't needed for this and it can be done in regular pivot table settings?

Cheers...Alkemist

No question here, just some procedures for the archive.

Search criteria: fill listbox with items from a pivottable field, get
pivot table field values into a listbox, populate listbox with items
from pivottable, listbox values from pivottable, get listbox values
from pivot table.

The three procedures below demonstrate how to fill a listbox with
items from a pivot table on the active worksheet, modify the pivot
table with a selection in
the listbox and then "reset" the modified field in the pivot table
with all values being shown. The target field in the pivottable is a
field call DEPT that will populate a listbox named ListBox1 on the
active sheet.

1. The first procedure, SetupListBox1, populates ListBox1
2. The second procedure, ListBoxSelectionChangesPT, modifies
(shows/hides) DEPT items in the pivottable based on the selection
highlighted in ListBox1 (note with this code, it seems that you can
only select one item at a time in the listbox, I tried adjusting
ListBox1> Properties>Behavior> MultiSelect: 0 - frmMultiSelectSingle,
but this modification conflicted with the code in the procedure. You
may have better luck or insight.
3. The third procedure, PivotShowItemAllVisible, shows all the
previously hidden items in the first field of the pivot table (in this
case, DEPT).

Please note that I gathered these procedures from previous postings in
the newsgroup and modified the existing code just slightly for clarity
and run-time accuracy.

Sub SetupListBox1()
'Clears then populates a listbox named LISTBOX1 on active sheet
'With identified values from .PivotFields("TargetFieldNameHere")

Dim PF As PivotField
Dim I As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
.Clear
For I = 1 To PF.PivotItems.Count
.AddItem PF.PivotItems(I)
Next
End With
End Sub

Sub ListBoxSelectionChangesPT()
'Note for this procedure to work it seems that your
'ListBox1 Properties>Behavior>MultiSelect must be set
'to 0 - frmMultiSelectSingle. So it seems you can only
'select one item in the listbox to update to the pivot table.

Dim PF As PivotField
Dim I As Integer
Dim iVis As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
For I = 1 To PF.PivotItems.Count
If .Selected(I) Then
PF.PivotItems(I).Visible = True
iVis = iVis + 1
End If
Next
If iVis = 0 Then
MsgBox "Must have at least one DEPT visible"
Exit Sub
End If
For I = 1 To PF.PivotItems.Count
If Not .Selected(I) Then PF.PivotItems(I).Visible = False
Next
End With
End Sub

Sub PivotShowItemAllVisible()
'Shows all items in the FIRST FIELD in all pivot tables
'on the active sheet.
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class

Dim pt As PivotTable
Dim PF As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each PF In pt.VisibleFields
For Each pi In PF.PivotItems
If pi.Visible True Then
pi.Visible = True
End If
Next pi
Next PF
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I am using Excel 2003. When I pivot data, I frequently do not want the
automatic sub-total for the fields.

Can I change the default sub-total field setting to "None", instead of
"Automatic"?

It's kind of a pain to constantly change the field setting everytime I move
data around.

In Excel 2002, I get the wrong value (inaccurate) from a calculated field
that is supposed to provide the simple difference between two other data
fields; "% 2002 Total - % 2001 Total"

The data fields "% 2002 Total" and "% 2001 Total" are created using the "%
of Total Option" in the Pivot Table Field Settings menu.

Any suggestions for a fix, or explanation would be most appreciated.
Note: I need to use a calculated field inside the Pivot Table. I realize a
calculation outside the Pivot Table would be a work around.

--
thanks,

Marshall

pivot table field settings (values , report filter , row lables , column
lables)
under values , i have dragged 64 titles
i want to change the 64 items one time ,not one by one (too boring)
hope any one can help to control the values field settings or put default
thanks in advance for the cooperation
--
ismael abdallah

I have my Pivot table, and created a (sum of employees) which gives me the
sum of
all employees grouped by department. Now i would like to add this field to
the pivot table field list so i can manipulate that field in a calculated
field. I hope you can help me out, or tell me where can i learn about this
subject. thank you,,,,,

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 want a macro i can run on a selected Pivot table, to set all pivot table field sub-totals to "None".
Excel has no default setting that i am aware of to set sub-totals to none, before or after creating a pivot table. Without individually selecting each field and setting to none.

The next level would be to show all feilds in a combo box, and select any or all to set to nothing etc.

Any help would be appreciated.

What we would like to do, if possible, is apply conditional formatting to a
pivot table field that stays with the field regardless of where the field
gets moved to on the pivot table as users move fields around while using the
pivot table for analysis. In addition, we'd like to find out if it is
possible to apply any formatting to a pivot table field where the formatting
is relative to the pivot table field and not to the cell that the pivot table
is in at the time the formatting is applied. Thanks in advance.

Hello,

Is there a way to have the Pivot table fields set to sum (or any other
calculations)instead of count before you start to create?

Thank you,

Denise

Hi All,

I have a spreadsheet that contains sales by sales rep. Its very simple, I
have the sales rep name and the total sales for the year, which is a simple
sumif from a data list.
But what I want is to create a macro where you select the sales rep ID and
click a button and a pivot table opens in a new workbook showing the sales by
period (months). I'm almost there the only problem I can not figure out is
how to set the pivot table field to show only the information for the
selected sales rep. I have tried recording a macro to see how to do it but
all I get is this

Sub Macro1()

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee")
.PivotItems("Ian Smith").Visible = False
.PivotItems("John Smith").Visible = False
.PivotItems("Nat Li").Visible = False
.PivotItems("Vivien Soul").Visible = False
End With

End Sub

With the one I have picked not in the list hence the property would be
visible=true.
Is there any way I can get the macro to say hide all except the chosen one,
as the list of sales reps constanty changes.

All help is greatly appreciated

Naz

--

_______________________
Naz,
London

In Excel 2002, I get the wrong value (inaccurate) from a calculated field
that is supposed to provide the simple difference between two other data
fields; "% 2002 Total - % 2001 Total"

The data fields "% 2002 Total" and "% 2001 Total" are created using the "%
of Total Option" in the Pivot Table Field Settings menu.

Any suggestions for a fix, or explanation would be most appreciated.
Note: I need to use a calculated field inside the Pivot Table. I realize a
calculation outside the Pivot Table would be a work around.

--
thanks,

Marshall

Hi. I keep getting an error that I need to change the name of my Pivot Table
field -- it is not valid. It want's me to type a new name for the offending
field. However, there are more than 40 columns -- how do I know which one to
change? I don't think I made any changes although I did bold some headings.
Would that have set this off? It persists even if I copy the information into
a new file and try to create a new pivot table. The help function is no help
-- it just says to type a new name. Thanks for nothing.


No luck finding an answer? You could always try Google.