How to Properly Handle Date Grouping in Excel PivotTables

Starting in Excel 2016, PivotTables automatically group dates into hierarchies based on the date information in the table. This feature is handy for collapsing data fields and organizing data by logical groupings.

However, this date display may not be the best option for your spreadsheet. If you want to display dates in a group type not available with automatic grouping, you can adjust the grouping parameters to fit your unique needs.

Follow these steps to properly group dates in PivotTables.

1. Open the “Group” Menu

Right-click on one of the dates in the PivotTable and select “Group” to open the Grouping dialog box. You can also go to the “PivotTable Analyze” tab, then the “Group” button.

2. Choose Standard Date Parameters

In the Grouping menu, you can confirm the start and end date to limit the data range. Excel automatically pulls the first and last dates in the table, which you can change if you need. Next, select from these preset groups:

• Seconds
• Minutes
• Hours
• Days
• Months
• Quarters
• Years

You can choose more than one to create data hierarchies. For example, you can select “Months” and “Years” to sort the data by month and group those months into years.

3. Create Unique Data Groupings

You can also adjust the parameters for nonstandard groupings like:

• One week: Select “Days” in the “By” list, then change the number of days to seven. Your PivotTable will group every seven days based on the start date.
• Four weeks: You can group dates by four weeks or other unique groupings for fiscal periods. Select “Days,” then change the number to 28 — the number of days in four weeks. The PivotTable will group every four weeks.

4. Apply the Groupings to the PivotTable

When you’ve chosen your parameters, click “OK,” and the PivotTable dates will be grouped according to your chosen settings. You can click the gray minimize (-) and expand (+) buttons by the date to see the items grouped underneath them in the hierarchy.

Learn More About Our Managed IT Services

Does your organization need managed IT services? Agio is here to provide frictionless IT for hedge funds, financial services, and private equity organizations. Gain consistent uptime, transparency into IT management, and scalability to grow with your company by offloading your IT management to our team.

For more information about our intelligent IT options, contact us today.