{"id":5162,"date":"2020-11-30T17:19:45","date_gmt":"2020-11-30T16:19:45","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5162"},"modified":"2024-03-22T18:55:33","modified_gmt":"2024-03-22T17:55:33","slug":"group-quarters-years-excel-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-quarters-years-excel-pivot-tables\/","title":{"rendered":"Group By Quarters and Years With Excel Pivot Tables"},"content":{"rendered":"

Ever encountered a needing to add quarters to pivot table<\/a><\/strong>?<\/p>\n

I was faced with this\u00a0same scenario and looking\u00a0at my data on hand, I only had sales numbers for each individual day.<\/p>\n

Grouping these would take a ton of effort & complex formulas<\/a>!<\/p>\n

Thankfully there is the Pivot Table<\/a> way to add quarters to pivot table, which is quick, easy, and reduces the risks of making any errors….and it makes updating the report easy with any new additional data!<\/p>\n

\n

In the example below I show you how to add quarters to pivot table in Excel:<\/p>\n

\n

download excel workbook<\/strong><\/span>Group-by-Quarters-and-Years.xlsx<\/span><\/a><\/p>\n<\/div>\n

 <\/p>\n

STEP 1:<\/strong><\/span>Insert a new Pivot table by<\/a> clicking\u00a0on your data and going\u00a0to\u00a0Insert > Pivot Table > New Worksheet or Existing Worksheet<\/em><\/strong><\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 2:<\/strong><\/span>\u00a0In the\u00a0ROWS<\/strong>\u00a0section put in the\u00a0Order Date<\/strong><\/em>field.<\/p>\n

Notice that in Excel 2016 (the version that I am using) it will automatically Group<\/strong> the Order Date<\/em><\/a> into Years & Quarters:<\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 3:<\/strong>If you do not have Excel 2016, right-click<\/span><\/span>\u00a0on any Row value in your Pivot Table and select\u00a0Group\u00a0<\/strong><\/span><\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span>In the Grouping dialogue box,Excelwas able to determine our date range (minimum date and maximum date).<\/p>\n

Make sure only Quarters\u00a0<\/b>and Years\u00a0<\/b>are\u00a0selected\u00a0(which will be highlighted in blue).<\/p>\n

This will group Excel pivot table quarters. Click OK.\u00a0<\/strong><\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

Notice that a\u00a0Years<\/em><\/strong>fieldhas been automatically added to our PivotTable Fields List. \u00a0This is cool, as we can use this field for further Pivot Table analysis:<\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 5:<\/strong><\/span> In the\u00a0VALUES <\/strong>area\u00a0put in the\u00a0Sales<\/strong><\/em>field. This will get the total of the Sales for each Quarter-Year\u00a0date range:<\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

Now we have our sales numbers grouped<\/a> by Years & Quarters!<\/p>\n

Notice that we can improve the formatting:<\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 6:\u00a0<\/strong><\/span>Click the Sum of SALES<\/strong> and select\u00a0Value Field Settings<\/a><\/strong><\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 7:\u00a0<\/strong><\/span>Select\u00a0Number Format<\/a><\/strong><\/p>\n

\"Group<\/a><\/p>\n

 <\/p>\n

STEP 8:\u00a0<\/strong><\/span>Select\u00a0Currency.\u00a0<\/strong>Click\u00a0OK.<\/strong><\/p>\n

\"Group<\/a><\/p>\n

This is how to add years and quarters to Pivot Table. You now have your total sales for each quarterly period!<\/p>\n

\"Group<\/a><\/p>\n<\/div>\n

Learn more about Excel with our <\/span><\/span>Free Microsoft Excel Online Course!<\/span><\/span>\u00a0<\/span><\/a><\/p>\n

\n

Further Learning:<\/strong><\/p>\n