Pivot Table Separate Year on Year Columns

coastie2016

New Member
Joined
Jan 26, 2016
Messages
4
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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

[TABLE="width: 329"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Sum of bookings[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]329[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"]344[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]348[/TD]
[TD="align: right"]318[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD="align: right"]326[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]281[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]354[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD="align: right"]337[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]332[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]372[/TD]
[TD="align: right"]338[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]328[/TD]
[TD="align: right"]333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]274[/TD]
[TD="align: right"]308[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD="align: right"]281[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]335[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]287[/TD]
[TD="align: right"]281[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD="align: right"]293[/TD]
[TD="align: right"]357[/TD]
[TD="align: right"]315[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks liveinhope, this advice with a bit of tweaking did the trick!

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

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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
Back
Top