Excel Pivot Table – Grouping Dates

While doing a TDS audit, one of my friend got II-Quarter (i.e. July to September-2012) TDS deduction data from his client. It was a party wise, every day TDS deductions, tabular format report as given in picture A.

Picture A

My friend needs all these data in a simple format as given in Picture B.

Picture B

With the help of Pivot table – Date Grouping, we are going to achieve this. Let see how to do this.

Step 1: Select all the data and apply pivot table (Shortcut: Alt + D + P and press enter 3 times).
Step 2: Drag and drop these fields in the relevant area as mentioned in the picture C

Picture C

Now you get a pivot table as given in Picture D

Picture D

Step 4: Click any date and apply Group Selection (ALT + JT + K or right click and select Group)

Step 5: In the Grouping dialog box, you can select any 'Group By' options (i.e. Months / Quarters / Years).

Picture E

Step 6: Verify whether the starting at and the ending at dates completely cover the TDS Dates. (Excel will automatically select the date range; we can modify the date range)

Step 7: Select Months as 'Group By' Option (Default Selection) and click OK
Now, excel gives us the desired result as given in Picture F

Picture F

Note:
We can select multiple 'Group By' options in the Grouping Dialog box (Example: Months and Quarter)
If so, how the result will look like?  Refer Picture G.

Picture G