Nick_London
05-17-2012, 06:17 AM
Hi,
I am able to create a pivot table and manually select/drag the fields/columns that I want to summarise (show sum, average, count etc) but is there a way to select all columns/fields at once without individually moving each one?
For example in my raw data sheet I have the following columns of data:
A1:QTR
B1:X1
C1:X2
D1:X3
E1:X4
F1:X5
G1:X6
H1:X7
I1:X8
For my row I want to column A (QTR) and all remaining columns I want to display as columns showing average value. The final pivot table is shown in the sheet Pivot. I've had to manually drag in each column (B, C, D) etc into the field section and select average as the Pivot table field.
But I would like to do this in automated way so that I don't have to manually select columns B to I. I have a Excel 2007 workbook that I need to show average for over 500 columns of data which are too many to drag in manually!
Hope someone can help. Yes I am looking for a solution in Excel 2007 but it would be nice to have something in version 2003 so I can use for a smaller data size.
Thanks,
Nick
I am able to create a pivot table and manually select/drag the fields/columns that I want to summarise (show sum, average, count etc) but is there a way to select all columns/fields at once without individually moving each one?
For example in my raw data sheet I have the following columns of data:
A1:QTR
B1:X1
C1:X2
D1:X3
E1:X4
F1:X5
G1:X6
H1:X7
I1:X8
For my row I want to column A (QTR) and all remaining columns I want to display as columns showing average value. The final pivot table is shown in the sheet Pivot. I've had to manually drag in each column (B, C, D) etc into the field section and select average as the Pivot table field.
But I would like to do this in automated way so that I don't have to manually select columns B to I. I have a Excel 2007 workbook that I need to show average for over 500 columns of data which are too many to drag in manually!
Hope someone can help. Yes I am looking for a solution in Excel 2007 but it would be nice to have something in version 2003 so I can use for a smaller data size.
Thanks,
Nick