Pivot Table Dates Columns

[Ethan Reid]

Hi all,

Please see attached example sheet

I have a table that has columns for countries, and retailers, and then it has columns for each month of sales they made. However I do not want to drag these dates into the 'values' box on the PivotTable, I want them in the columns box along with grouped quarters. I don't know how to do this or where to write Q1, Q2, etc. so that it shows in my pivot table. Also I don't know how to tell Excel that the values are sales units. Currently if I move the month to the column field it puts all the data in the columns too, which I don't want.

In addition to this, is there a way of having data as different formats in the same Pivot Table? My company uses a server to copy data down so I don't know how they do it, but they are able to have value set to sales units or revenue, and depending on what they select the data shows raw numbers or dollars formatting. How can I do this?

Many thanks!!

[#2]

Hi could you let me know if you are using Excel 2016 / 2013 or 2010?

Have you ever used Power Query, as it's perfect for this sort of thing....  (it's a free download for Excel 2010 and 2013,  or built in to Excel 2016)

I even did  a video on it recently showing how to "unpivot" dates so you can analyse it in a pivot table

https://www.linkedin.com/pulse/best-feature-excels-power-query-wyn-hopkins/

[#3]

I'm using Excel 2013, I have never used PowerQuery but I will give that a try, thanks!

[#4]

Let me know how you get on