Excel 2007 : Pivot table with columns of monthly data

**skibumtx** · 12-19-2010, 11:56 AM

I'm working on budget data for a proposal. The data is structured with columns for contract WBS, name and org. Then I list out each month in the proposal period starting with Oct 2011 and going thru Oct 2018. Each month has an amount associated with it. How do I set up pivot tables that will give me totals by grouping the months into calender year, FY or phase (A,B,C/D,or E driven by date) totals?

Data will look like this:
WBS Name Org Oct11 Nov11 Dec11 Jan12 Feb12 Mar12 Apr12 May12 Jun12 ... Oct18
5.1.1 Joe ES 10 11 10 8 5 3 11 15 12 11
5.1.2 Jane AL 3 3 5 8 11 11 11 12 4 2

Each month column is a date Oct 1, 2011, formatted to display month and year only.

**Ron Coderre** · 12-19-2010, 12:28 PM

The short answer is...you can't.
Not in the format you posted, which is *aready* in a cross-tab kind of format.

You can get the kind of pivot table you want if you structure your data like this:

Please Login or Register  to view this content.

Adding columns to identify the fiscal qtr and year might also help.

After creating a pivot table from that structure, you'll be able to group your data by fiscal qtr/yr.

Does that help?