Pivot table not grouping by dates from columns (newbie needs help!)

[Tove]

I have a table which pretty much looks like this (but with monthly data ranging from 2010 to 2019):
IMG1.JPG

I want to create a pivot table and group the data by year, quarter etc for each person. For example:
IMG3.JPG

But when I create a pivot table from my data I only get these choices:
IMG2.JPG

I realize I must be doing something wrong, perhaps the original data is not set up right.

Can someone please advise me?

[humdingaling]

original data is not setup right

your question is very similar to the one posted here
http://www.excelforum.com/excel-gene...b-tableau.html

MarvinP has a solution you can use in your situation...requires the use of vba file

[Tove]

Many thanks!

I wish it would just work on my original data since it's collected from another system, but I'll look into MarvinPs solution!

[gjcase]

Tove:

You might also check out Power Query (2016 and 365 these functions are in the data tab.) You can load your data into a query and then unpivot it in a single step by selecting to columns to unpivot, right-clicking, and selecting the Unpivot option. Better yet, you can save the query for subsequent use, so if this is something you do routinely, it might be worth the time to figure the new data tools out. Power Query is available as a download from MS for earlier versions (I think 2008 and up) if you don't have the latest versions. Power Query can also import the data from many external sources, saving time there as well.

[CK76]

PQ is the way to go.

1. For Excel 2010 you can download free add-in. For Excel 2013, you'll need specific license SKU. From 2016 and up, it comes as standard feature and named "Get & Transform" in the ribbon tool.
2. Convert range to table.
3. Data tab -> From Table/Range, Go into Editor.
4. Select all the date/month columns. Right click and "unpivot".
5. Change "Attribute" column to Date data type and rename columns as needed.
6. Click on dropdown arrow at "Close & Load" and choose "Close & Load To...", make it as connection only, and load to data model.
0.JPG

7. Insert pivot table, using Data model as source. The one with cylinder icon.
1.JPG
See attached sample.

NOTE: Data models from 2013 and up isn't compatible with 2010 version. It uses different engine for the model and 2010 will not be able to interact with model made in newer versions.