Unable to change all data fields form sum to average at the same time

Question

Unable to change all data fields form sum to average at the same time

Anonymous

How can I change all the data fields in a pivot table from sum to average to max to min and so fourth at the same time instead of clicking on each of them individually? The data fields I am referring to appear on the left side of the screen below.

Industry	Communications Equipment

	Year
Data	1995	1996	1997	1998	1999	2000	2001	2002	2003	Grand Total
Average of Net Sales	13094.96	12409.64	16118.52	17793.46	21493.55	24824.89	21062.05	15815.37	14889.69	17500.24
Average of COGS	4011.32	3932.52	4895.59	5369.70	5762.80	6832.68	6855.26	4514.52	3837.75	5112.46
Average of SG&A	1255.53	1172.19	1557.85	1810.97	2456.99	2698.09	2933.20	2315.54	1934.15	2014.95
Average of Int Exp	174.42	151.95	175.71	206.04	239.89	273.69	349.13	327.50	310.39	245.41
Average of Inc Taxes	108.55	137.75	243.84	203.03	358.99	500.66	-692.54	380.81	160.51	155.73
Average of EPSBasic	0.30	0.40	0.58	0.52	0.73	1.01	-7.20	-1.48	0.15	-0.55
Average of ROA	16.62	15.70	18.35	12.61	17.05	20.42	-96.64	-77.54	-5.26	-8.74
Average of ROE	17.80	33.29	39.67	27.94	35.09	34.35	-164.86	-112.52	-8.04	-10.81
Average of ROI	12.77	19.81	24.43	16.26	21.59	24.23	-111.56	-177.37	-11.42	-20.14

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

Jason,

I agree, there should be ways to set some defaults for pivot tables, but virtually all the options we can change are on a pivot table by pivot table basis. And some of them only apply when apply them, they do not become defaults.

No doubt there is a lot of complexity behind the pivot table, some of which makes it extremely fast and useful, but it might be that giving us some of the desired options would impact the overall functionality.

We should still keep asking. In 2010 Microsoft added at least the following the the pivot table area:

Pivot Chart Filter (floating) is gone replaced by imbedded filters
Five commands under Field Buttons:
Show Report Filter Field Buttons
Show Value Filter Field Buttons
Show Row Labels Filter Field Buttons
Show Column Labels Filter Field Buttons
Hide All
Analysis Tab - Insert Slicer
Draft Mode Options available on pivot charts, see Charts topic area
Search bar has been added to all filters
Pivot Table options - Date, What if Analysis
Pivot Table Options - Alt Text tab
Repeat Item Lables option has been added to pivot table (field settings & under Report Layout)
Insert Slicer - a tool to make filtering easier
New Calculation Group (Option) includes:
Show Values By
Show Values As
Fields, Items, Sets
What if Analysis command on Options tab
New Subtotals option - include filtered items in totals
New Show Values as Options:
No Calculation
% of Grand Total
% of Row Total
% of Column Total
% of Parent Row Total
% of Parent Column Total
% of Parent Total
% Running Totals in
Rank Smallest to Largest
Rank Largest to Smallest

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

0 comments

4 additional answers

Answer 1

Thia is probably just my naivete, but when I am creating the pivot table, I think that there should be a way for me to change the default settings for how the data is presented, whether i want it to be as sum, count, max, min, and so on, so that when I select Net sales, it automatically is average as opposed to sum or count. Either that or the tab that is connected to all the values; "the values tab," should have a similar option, just like the rest of the tabs do, for me to click on it and enter its value field settings and change it to whatever I want it be, such as average.

Is there a way to at least change the default from sum to average?

Answer 2

Please do not multi-post...

No... because your accounts are all in seperate fields as opposed to one field with multiple members you can not change them all at once. Right now I assume your source data is similar to this...

Industry	Year	Net Sales	CoGS	SG&A
x	199x	xxx.xx	xxx.xx	xxx.xx
x	199x	xxx.xx	xxx.xx	xxx.xx
x	199x	xxx.xx	xxx.xx	xxx.xx

To do what you want you would need it to look like this...

Industry	Year	Account	Amount
x	199x	Net Sales	xxx.xx
x	199x	Net Sales	xxx.xx
x	199x	Net Sales	xxx.xx
x	199x	CoGS	xxx.xx
x	199x	CoGS	xxx.xx
x	199x	CoGS	xxx.xx
x	199x	SG&A	xxx.xx
x	199x	SG&A	xxx.xx
x	199x	SG&A	xxx.xx

at that point you could just switch the aggregation of the account field and all of the members would use that aggregation.

Also note that it is not mathematically correct to take averages of some of those fields. Everything from EPSBasic on down potentially do not aggregate properly as they involve ratio type calculations. The only way for those to be valid is to calc the values within the pivot table based on source data.

HTH... Jim Thomlinson

Answer 3

Anonymous

This is great, but is there a way to only use the built-in features of excel to do this. In other words, can I change all the field data to average from sum at the same time without using a macro, VBA, or an add-in?

0 comments

Answer 4

Best to only post once.

Here is the VBA sample solution, you would need to construct code for all the conversions you might make. This code converts two sum fields to count fields

Sub myCount()

With ActiveSheet.PivotTables("PivotTable11")

.PivotFields("2005").Function = xlCount

.PivotFields("Density").Function = xlCount

End With

End Sub

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Share via

Unable to change all data fields form sum to average at the same time

4 additional answers