Thursday, October 18, 2012

How to Rollup days to months and years in Excel Pivot

If we have multiple dates, we can rollup them into months and years in excel pivot but we need to make sure it satisfies the below things

1. Format should be same for all dates to verify this, we need to apply filter for date column and then click on dropdown, generally excel will group rows by years and months, in the first image the few values are grouped to years few are not, that means there is an issue with the format, first correct it and verify, the second image doesn’t displays any extra records means you can go a head with 2nd step.

2. Insert – Pivot
3. Drag the date column into columns or rows section
4. Right click on any date --- select group – hold ctrl and select months and years
5. Move the column to columns section or rows section as per your requirement.

No comments: