working with a pivot table - dealing w/ the months

[bigdan]

I'm working on a pretty big file right now, with my actual spending over the last 27 months, as well as my projected spending for the next 13, each divided by sales rep, customer #, province, etc. As you can imagine this is pretty big.

Here's my issue - in the pivot table I'm seeing, I'm seeing an individual column for every month, for every rep. So for example I'm seeing

Anthony Bob
Actual Spend Mth 1 | Actual Spend Mth 2 | [goes on 27 times] Actual Spend Mth 1 | Actual Spend Mth 2 | [goes on 27 times]

etc etc.

Can I somehow make this more readable, for example by just putting in the sums of either all the months, or 6 months at a time? I dont have these sums in the spreadsheet, and putting them in would convolute them.


On a smaller note, if I create a pivot table, and then either change the heading in the source data, or create a new column, is there some way to incorporate that or would one have to create a new pivot table altogether?


Oh one last thing - I dont know VBA so please dont give the answer in code!



Thanks
Dan

 

[s hal]

Are you working in excel 2007+? You can group fields by months, for instance - its in the options part of the ribbon when you have the pivot table selected. You can also highlight multiple fields and group them together. The manual grouping can also have the name changed from the default (default is group1, etc.). Highlight the cell, hit F2, a cursor appears and you can change the name (you can actually do that on any field by the way).

If you change source data, you can either hit the refresh button (or right click and select refresh) or change source data.

 

[SeanDamnit]

Putting in the sums for all the months should be easy enough - simply don't place the Months field in the Columns portion of the pivot field.

If you'd like to still group the months, then I'd recommend adding another column to your source data - maybe in quarters?

And if you ever change a part of the source data, you simply need to right click the pivot table and select "Refresh" to update with the latest data.

 

[bigdan]

cool thanks a lot guys!