Pivot Table Query - Version Excel 2003

[TASHFIN]

I have created a Pivot Table from a spreadsheet - which has monthly values for five years. So that's 60 columns of monthly "numerical" data
plus four other - "text" fields.

Now as users of Excel 2003 are aware - as soon as a new Pivot Table is created - a Pivot Table Field list "task pane" appears besides the
pivot table.

While the feature is good in the sense you can drag and drop any field covered within the range of the pivot table without having to go back to the original layout of the pivot table (as in older versions of Excel). However it seems to me that you can only select one field at a time when using this
feature.

At present my current Pivot Table has only "one" monthly "numberical" column in the "DATA" area of the Pivot Table - Can anyone suggest a quicker way of updating the Pivot table so that more then one or in the case of this example (59 columns) of data can also added to the "DATA" area in one without having to drag each month individually and "drop" it into the Pivot Table ?

 

[Corni]

In this particular case I would have designed the table using month as a row field, in order to avoid having so many columns with data fields. This makes your life a lot more easier when manipulating or upgrading the table.

 

[TASHFIN]

Unfortunatley the 60 monthly columns have to be put in the "DATA" area of the pivot table - as they contains numerical values which are being crunched by using the pivot table "sum" function.

I'm sure its a common enough problem for lot of people who have lots of fields to put into the "DATA" area of the pivot table - and it is extremely tedious dragging each field individually.

Anyone with a solution !!!

 

[agl]

Can you not group the dates by year in the pivot table & manipulate accordingly?

 

[Mark W.]

Your data isn't organized in the best way. What you have is a repeating group of values indexed by (60) months. Ideally, your data should be in 1st Normal Form (1NF) which is accomplished by removing this repeating group.

So, instead of...

Month1,Month2,Month3,...
#,#,#

...you'd have....

Month,Value
1,#
2,#
3,#

This provides the greatest flexiblity for use with a PivotTable, and you'd only have to drag 1 field ('Month') to your PivotTable's DATA area. Once configured your PivotTable wouldn't need subsequent modification as new month's data is added to your source data.