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 |
