PDA

View Full Version : CrePivot Table To Show All Value/Field Columns



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

Bob Phillips
05-17-2012, 07:58 AM
I would use VBA. What is the VBA password.

Bob Phillips
05-17-2012, 08:13 AM
It's okay, this code shoul;d do it. Add QTR to the pivot then run this

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

Nick_London
05-17-2012, 03:02 PM
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
05-17-2012, 04:20 PM
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
06-21-2012, 12:28 PM
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