CrePivot Table To Show All Value/Field Columns

[Nick_London]

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

[Bob Phillips]

I would use VBA. What is the VBA password.

[Bob Phillips]

It's okay, this code shoul;d do it. Add QTR to the pivot then run this

[VBA]Sub AddPivotValues()
Dim rng As Range
Dim cell As Range
Dim lastcol As Long

With Worksheets("Raw")

lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rng = .Range("B1").Resize(, lastcol - 1)
End With

With Worksheets("Pivot")

With .PivotTables("PivotTable1")

For Each cell In rng

.AddDataField .PivotFields(cell.Value), "Average of " & cell.Value, xlSum
With .PivotFields("Average of " & cell.Value)
.Function = xlAverage
End With
Next cell
End With

.Columns("B:B").NumberFormat = "0.00"
End With
End Sub
[/VBA]

[Nick_London]

Thanks XLD.

This seems to work fine both in 2003 and 2007 with this data set but it seems to take very very long to work with a much larger data set in 2007 (2000 columns!).

Is there any other way to do what I need? Is there no way to tell excel to pivot all feilds as default? Alternatively I think I may able to get the end results much fatser (average of each data field) using Access.

Nick

[Bob Phillips]

I don't see how you can realistically expect to analyse 2000 columns, that makes no sense.

But I don't think you can do it all in one action. You might turn ScreenUpdating off which might help, but other than that, I have no other ideas.

[Nick_London]

Ok Thanks XLD.

I've been playing around with Microsoft Query in Excel. So for instance I can extract and query data from an external source such as an text file or excel workbook.. In it there is an option to specify SQL. I can easily do group by the QTR column and bring the average for a given column.

e.g

Select Examplexls.QTR, avg(Examplexls.X1)
FROM Examplexls
GROUP By Examplexls.QTR

But is there a way to say to do it for all columns without manually having to specify the average for each variable in the SQL (avg(Examplexls.X1, avg(Examplexls.X2, avg(Examplexls.X3 etc), i.e can I get it to auto populate with all columns in one go?

Thanks,

Nick