Excel Pivot table offers many useful functions. One of them is to combine items into groups. Grouping items in Pivot table is very simple. Just select the range where you need to group and choose Pivot Table Tools-> options-> Group-> Group Selection. If the field contains dates, then Excel can automatically group them into months, Year, Quarter or the three. Below is the procedure to group different dates into Monthwise summary or year wise summary.
Steps to Group Dates into Months, Year and Quarter in Excel Pivot Table:
Let us start with using a example on the same. Below is the sample data which has two columns of data. Date and Total Sale Value. The table has 20 rows and cover dates between January 2011 to May 2012.
The desired final output is to summarize the sales information by months.
Step 1: Create the pivot table using the table. The initial pivot table looks almost similar to the raw data.
Step2: To group item by month, right click any cell in date column of the pivot table and select Group from the shortcut menu. A Grouping dialog menu appears.
Step3: In the list box Select Months and Years and verify that the starting and ending dates are correct and Click OK.
The date Items in the pivot table are grouped by years and by months. Even you can group the dates by quarter and years too. Below the final output of Grouping by month and year, quarter and Year.
Steps to Group Dates into Months, Year and Quarter in Excel Pivot Table:
Let us start with using a example on the same. Below is the sample data which has two columns of data. Date and Total Sale Value. The table has 20 rows and cover dates between January 2011 to May 2012.
The desired final output is to summarize the sales information by months.
Step 1: Create the pivot table using the table. The initial pivot table looks almost similar to the raw data.
Step2: To group item by month, right click any cell in date column of the pivot table and select Group from the shortcut menu. A Grouping dialog menu appears.
Step3: In the list box Select Months and Years and verify that the starting and ending dates are correct and Click OK.
The date Items in the pivot table are grouped by years and by months. Even you can group the dates by quarter and years too. Below the final output of Grouping by month and year, quarter and Year.
custom grouping in pivot table reports
ReplyDelete