Pivot Table Query - Version Excel 2003

TASHFIN

New Member
Joined
Mar 16, 2002
Messages
22
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 ?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
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

New Member
Joined
Mar 16, 2002
Messages
22
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

New Member
Joined
Apr 11, 2005
Messages
2
Can you not group the dates by year in the pivot table & manipulate accordingly?
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,274
Members
414,050
Latest member
Rick Royer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top