Pinterest Pixel

Group By Quarters and Years With Excel Pivot Tables

Bryan
Ever encountered a needing to add quarters to pivot table? I was faced with this same scenario and looking at my data on hand, I only had sales numbers for each individual day.
Grouping these would take a ton of effort & complex formulas! Thankfully there is the Pivot Table 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!
Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline

Key Takeaways:

  • Grouping data by quarters and years in Excel Pivot Tables is a straightforward process that avoids the need for complex formulas and extensive effort. By inserting a new Pivot Table and using the ROWS section to add the Order Date field, users with Excel 2016 and newer versions will automatically see their data grouped into Years & Quarters. For earlier Excel versions, right-clicking on a Row value and selecting Group allows for manual grouping.
  • For a more refined analysis, after grouping the dates into quarters and years, you can easily add relevant data such as sales to the VALUES area. This action consolidates your data, providing a clear total of sales for each Quarter-Year range. Formatting these values as currency further enhances the report’s readability and professionalism.
  • Pivot Tables in Excel also enable users to fine-tune their data analysis by offering options to group by other intervals such as months, fiscal years, or even specific sales ranges. The functionality extends beyond standard calendar quarters, allowing for customized reports tailored to different fiscal year structures or specific analytical requirements.

In the example below I show you how to add quarters to pivot table in Excel:

download excel workbookGroup-by-Quarters-and-Years.xlsx

 

STEP 1:Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

Group By Quarters and Years With Excel Pivot Tables

 

STEP 2: In the ROWS section put in the Order Datefield.

Notice that in Excel 2016 (the version that I am using) it will automatically Group the Order Date into Years & Quarters:

Group By Quarters and Years With Excel Pivot Tables

 

STEP 3:If you do not have Excel 2016, right-click on any Row value in your Pivot Table and select Group 

Group By Quarters and Years With Excel Pivot Tables

 

STEP 4:In the Grouping dialogue box,Excelwas able to determine our date range (minimum date and maximum date).

Make sure only Quarters and Years are selected (which will be highlighted in blue).

This will group Excel pivot table quarters. Click OK. 

Group By Quarters and Years With Excel Pivot Tables

 

Notice that a Yearsfieldhas been automatically added to our PivotTable Fields List.  This is cool, as we can use this field for further Pivot Table analysis:

Group By Quarters and Years With Excel Pivot Tables

 

STEP 5: In the VALUES area put in the Salesfield. This will get the total of the Sales for each Quarter-Year date range:

Group By Quarters and Years With Excel Pivot Tables

 

Now we have our sales numbers grouped by Years & Quarters!

Notice that we can improve the formatting:

Group By Quarters and Years With Excel Pivot Tables

 

STEP 6: Click the Sum of SALES and select Value Field Settings

Group By Quarters and Years With Excel Pivot Tables

 

STEP 7: Select Number Format

Group By Quarters and Years With Excel Pivot Tables

 

STEP 8: Select Currency. Click OK.

Group By Quarters and Years With Excel Pivot Tables

This is how to add years and quarters to Pivot Table. You now have your total sales for each quarterly period!

Group By Quarters and Years With Excel Pivot Tables

Common Pitfalls and How to Avoid Them

Dealing with Blank Cells and Text Issues

Have you ever been tripped up by blank cells or incorrect text entries when working with pivot tables in Excel? It’s a common hiccup that can prevent you from grouping your data effectively. If a date or number field contains blanks, simply fill in those cells with a date or number—feel free to use a placeholder if necessary. For text stumbling in where numbers should be, whisk them away or convert them to actual numbers using Excel’s built-in functions.

Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline

Overcoming ‘Cannot Group That Selection’ Errors

Running into the “Cannot group that selection” error can be a head-scratcher, but don’t worry, there are straightforward fixes for you. One issue might be that your pivot table was added to the Data Model, which requires a uniform data type for grouping. Try creating a new pivot table without adding it to the Data Model. Alternatively, if previous groupings linger in your field list, clear them out to start afresh. These tweaks can swiftly restore the path to successful data grouping.

Cannot group that selection in an Excel Pivot Table - SOLUTION!

Pro Tips for Enhanced Pivot Table Functionality

Create Unique Data Groupings for Customized Reports

To tailor your pivot tables for more nuanced insights, craft unique data groupings that align with your analytical goals. For instance, group your sales data into specific time frames that matter to your business, like fiscal quarters or seasons. This can be done by right-clicking your date field within the pivot table, selecting ‘Group’, and then setting your custom range. You can also manually select the dates you want to group together using Ctrl+Click. This customization lets you march to the beat of your own analytical drum, yielding reports with a personal touch.

Automate Date Grouping for Efficiency

Embracing automation can significantly streamline your pivot table work, particularly when dealing with date groupings. Excel’s ability to auto-detect and group dates can be a huge time-saver. Simply drag your date to the rows or columns in the pivot table, and watch Excel automatically group them by years, quarters, and months. If Excel’s default hierarchy doesn’t align with your needs, you can adjust the groupings by selecting the dates, clicking on ‘Group’, and then choosing your preferred intervals. You’ll be soaring through your data analysis tasks faster than you ever imagined.

Frequently Asked Questions

How do I group dates by month and year in a pivot table?

To group dates by month and year in a pivot table, first ensure that all cells in your date column are correctly formatted as dates. Then, follow these steps: Select any date in the pivot table, go to ‘PivotTable Analyze’ tab, click ‘Group Field’, select ‘Months’ and ‘Years’, and click OK. Now, your data will be organized by both months and years, making it easier for you to analyze trends over time.

Why am I unable to group selected data in my pivot table?

If you’re unable to group selected data in your pivot table, the problem might be due to non-date values or blanks in your date column, or perhaps your pivot table is part of the Data Model which doesn’t permit grouping. Check your data for consistency, and if all else fails, recreate the pivot table without adding it to the Data Model to enable grouping.

Group By Quarters and Years With Excel Pivot Tables

PIVOT BANNER

If you like this Excel tip, please share it
Group By Quarters and Years With Excel Pivot Tables | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  How to Create A Yearly Leave Record for Employees in Excel - The Easy Way

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...