Pivot Table Separate Year on Year Columns

[coastie2016]

Using Excel 2016, I would like to use a pivot table that groups by date into monthly columns (this part is easy), though months from different years should be in different columns (this part I can't figure out).

My current pivot table summarises a few thousand financial transactions and groups summarised data into monthly columns. The dates in the records span multiple years and the grouping in the pivot table combines the data from separate years into the same monthly column. For example, records from Jan 2016 are grouped with records from Jan 2017.

I would like to be able to separate the summarised data for these records into separate columns (months) in the pivot table.

I would then end up with columns for Jan 2016 to Dec 2016, followed by columns for Jan 2017 to Dec 2017.

Is this possible to achieve?

 

[bkaehny]

Using Excel 2016, I would like to use a pivot table that groups by date into monthly columns (this part is easy), though months from different years should be in different columns (this part I can't figure out).

My current pivot table summarises a few thousand financial transactions and groups summarised data into monthly columns. The dates in the records span multiple years and the grouping in the pivot table combines the data from separate years into the same monthly column. For example, records from Jan 2016 are grouped with records from Jan 2017.

I would like to be able to separate the summarised data for these records into separate columns (months) in the pivot table.

I would then end up with columns for Jan 2016 to Dec 2016, followed by columns for Jan 2017 to Dec 2017.

Is this possible to achieve?

You need to create a new field that just contains the Year of the date of the transaction. Can you add a column to the source data?

 

[liveinhope]

you don't need to change your source data. Here's how I did this in Excel 2007

- added an additional group level (year) to the row-field grouping...
the right-click on the pivot table and select "show field list"

last of all drag "year" from row labels to column labels

Sum of bookings	Column Labels
Row Labels	2015	2016	2017	2018
Jan	294	329	202	33
Feb	304	257	344
Mar	301	348	318
Apr	326	288	281
May	352	354	300
Jun	337	312	332
Jul	308	372	338
Aug	302	328	333
Sep	320	274	308
Oct	281	308	335
Nov	325	287	281
Dec	293	357	315

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[coastie2016]

Thanks liveinhope, this advice with a bit of tweaking did the trick!

bkaehny, I see how this would work also though, thanks.