Forum Discussion
Pivot Table
JulianG950 Regarding the date format, it doesn't have to be a special one. As long as the dates have a data type "Date". Then, you can group them e.g. by year, quarter, month.
Grouping is done by selecting a date header in the PT and then "Group selection" in the "PivotTable Analyse" ribbon.
and then the grouping that you want.
Give it a try and come back if you run into troubles.
Q1: Your task names are text. Hence, "Task 10" gets sorted before "Task 2". Add a 0 before single digit task numbers. Then "Task 02" will be sorted before "Task 10".
Q2: Your data is already "pivoted". It needs to be "unpivoted" first, so that you can put the dates in column headers and then group them by Qtr and Year. Use "Get&Transform Data" (a.k.a. as PowerQuery PQ) and PowerPivot to achieve this.
Q3: The colouring of cells depending on criteria is done via Conditional Formatting.
The attached workbook has Q1 and Q2 resolved, though you may find the steps mentioned in Q2 not all that straight-forward. Especially if you have never work with PQ before. First, determine if the outcome is what you need. Next, see if/how you can implement this in your real life situation.
Riny,
Hi, I've had a look and recreated the table, thanks.
I was wondering about the date format you used in the columns though - did you have to do this for the data to sort in the PT in the correct year order?
also, and thank you for your patience! would there be an easy way to group this by month as well as quarters and years?
Regards
Julian
JulianG950 Regarding the date format, it doesn't have to be a special one. As long as the dates have a data type "Date". Then, you can group them e.g. by year, quarter, month.
Grouping is done by selecting a date header in the PT and then "Group selection" in the "PivotTable Analyse" ribbon.
and then the grouping that you want.
Give it a try and come back if you run into troubles.
Riny_van_Eekelen Hi, thanks for the advice. Think I've got it, took a little while as I was highlighting all the dates not one, to do the grouping.
Your help is appreciated.
Regards
Julian