SharePoint Excel: How to Group Dates in a PivotTable

How-to basics · Updated November 20, 2026

To group SharePoint data by date in an Excel PivotTable, open the workbook in Excel for Microsoft 365 on Windows, place the date field in the Rows or Columns area, right-click a date in the PivotTable, choose Group, select intervals such as Months, Quarters, or Years, and click OK. The source field must contain real Excel dates, not text that merely looks like dates; SharePoint itself does not change the grouping steps.

What date grouping does in a PivotTable

A PivotTable normally treats each distinct date as its own item. If a list contains a sales date for every transaction, placing that field in Rows can produce a long list of individual days. Grouping rolls those dates into larger reporting periods, so you can compare monthly totals, quarterly counts, or yearly results without changing the underlying records.

Grouping changes how the PivotTable displays and summarizes its source dates. It does not rewrite the dates in the workbook or change the original SharePoint list. For example, grouping order dates by Months and Years lets you expand a year to see its months while retaining the year context. The summarized values still come from the PivotTable’s value field, such as Sum of Sales or Count of Requests.

This is useful for SharePoint list exports, workbooks stored in a SharePoint document library, and other Excel data that originated in SharePoint. The important distinction is whether the workbook contains a normal, groupable date field and what kind of PivotTable connection it uses—not where the file is saved.

Check the source dates before grouping

First make sure the source column contains consistent date values. Excel stores genuine dates as numbers and displays them with a date format. Text strings such as 2026-03-14 may look like dates but are not necessarily stored as dates, particularly when imported with mixed regional settings. A date field with text entries, errors, or blanks can stop Excel from grouping the field.

  • Check several source cells by selecting them and looking at the formula bar and Number Format. Applying a date format alone does not convert text into dates.
  • Look for empty cells, error values, and inconsistent entries in the date column. Correct or remove invalid entries in the source if appropriate.
  • Check ambiguous dates such as 04/05/2026. Depending on locale, that can mean April 5 or May 4. Confirm the intended interpretation before reporting by month.
  • If a SharePoint column includes times as well as dates, decide whether the time component matters to the analysis. Excel can group true date-time values, but an unplanned time component can make individual source values appear more varied.

For a SharePoint list, also check whether the workbook is a static export or a connected dataset. A static export is simply a copy of the records at the time it was created. A connected workbook may have a refreshable query or connection, and its refresh behavior depends on that connection.

Group dates in Excel for Microsoft 365 on Windows

Use the following workflow in the desktop version of Excel. If the workbook is open in a browser from SharePoint, choose the option to open it in the desktop app when the grouping command is unavailable.

  1. Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and choose where to put the PivotTable. If your records are already formatted as an Excel table, use that table as the source so added rows can be included more reliably.
  2. In the PivotTable Fields pane, drag the date column into Rows. Drag a numeric or otherwise useful field into Values, such as Amount for a sum or Request ID for a count. You can also put the date field in Columns if that layout better suits the report.
  3. Click a displayed date inside the PivotTable, then right-click and choose Group. Do not right-click a source-data cell outside the PivotTable.
  4. In the Grouping dialog, select the periods you need. Choose Months for monthly totals, Years for annual totals, or a combination such as Months and Years for a year-by-year monthly view. Quarters can be combined with Years for quarterly comparisons.
  5. For a custom number of days, select Days and enter the interval, such as 7 for seven-day groups. Review the starting and ending dates shown in the dialog, then choose OK.

Excel adds grouped fields to the PivotTable layout. Expand or collapse the year or quarter labels to control the level of detail. If the result is not what you intended, right-click a grouped label and choose Ungroup, then repeat the grouping with the correct options.

Choose useful periods and avoid mixing years

The grouping options should match the question the report is meant to answer. Months are convenient for a monthly trend, but selecting Months alone can combine the same month across different years. A PivotTable containing records from 2025 and 2026 could show one January total that combines both years. Select Years as well as Months when readers need to compare January 2025 with January 2026.

Likewise, quarters are not meaningful in isolation if the source covers multiple years. Grouping by Quarters and Years gives separate Q1, Q2, Q3, and Q4 results for each year. For a rolling weekly report, grouping by Days with an interval of 7 can be useful, but it creates consecutive date ranges rather than named calendar weeks. If the business defines weeks as Monday through Sunday or uses a fiscal calendar, confirm that the resulting boundaries match the reporting rules.

Excel’s standard date grouping follows calendar periods. If the organization uses a fiscal year that starts in a month other than January, ordinary Years grouping may not represent its fiscal years. In that case, add fiscal-year and fiscal-period fields to the source or to a calendar table, then use those fields in the PivotTable.

Keep a SharePoint-based PivotTable current

Grouping is a presentation and analysis step; it does not automatically update the underlying data. If a workbook is connected to a source, use Data > Refresh All in the desktop app to request an update. The precise behavior depends on whether the workbook uses a query, an external connection, or a static range. If it is a static export, refreshing cannot retrieve newly added SharePoint records unless a refreshable connection has been set up.

For a PivotTable based on an Excel table, new rows added to that table are generally included when the PivotTable is refreshed. If the PivotTable uses a fixed cell range instead, rows added beyond that range may be missed. Check the source through PivotTable Analyze > Change Data Source if the report does not include recent records, then refresh.

After refresh, check the date groups and filters, especially when new records extend the source into a new year. Refreshing updates the PivotTable from its source; it does not fix text dates or invalid cells in that source. If new SharePoint items arrive with a different date format, correct the import or transformation so the date column remains consistent.

Common grouping errors and fixes

If Group is disabled or Excel reports that it cannot group the selection, start with the source date column. A single text value, blank, or error among otherwise valid dates may be enough to cause trouble. Filter or inspect the source field, correct the problematic cells, and refresh the PivotTable. If the data is imported, fix the conversion step rather than repeatedly changing the cell display format.

  • Dates appear as separate items: Confirm that the field in Rows is a real date field, then right-click a date within the PivotTable and choose Group.
  • The Group command is unavailable: Confirm that the clicked cell belongs to the PivotTable, and check whether the PivotTable uses the Data Model or an OLAP/external source. Those sources can restrict ordinary manual grouping.
  • Months from different years have merged: Ungroup and group by both Months and Years.
  • New records are missing: Refresh the PivotTable and verify that its source range or table includes the added records.
  • The periods look shifted: Check date interpretation, locale, and any time-zone conversion in the data preparation process. A date-only SharePoint field and a date-time field may not behave identically after export.

For a PivotTable built from the Data Model or a connection that does not permit grouping, add useful period columns to the model instead. A calendar table containing calendar year, month, quarter, and—if needed—fiscal period fields is a robust approach for larger or recurring reports. Relate the calendar table to the transaction date in the model, then use its period labels in the PivotTable. This also makes fiscal calendars and consistent month names easier to manage.

Using Excel for the web and Mac

Excel for the web can work with PivotTables, but its available PivotTable commands may differ from the Windows desktop app and can vary with the workbook and feature rollout. If you cannot find the date grouping dialog in the browser, open the SharePoint workbook in the desktop app and use the Windows steps above. The workbook remains in SharePoint, but changes may need to finish saving before other users see them.

In Excel for Mac, the core idea is the same: put the date field in the PivotTable, select a date item, and use the grouping command if it is available for that PivotTable and source. Context menus and ribbon placement can differ from Windows. Control-click can open a context menu on a Mac when a right-click is not configured. For a workbook with a restricted external or Data Model source, use period fields in the source or model rather than expecting the desktop version alone to remove that limitation.

When several people edit a workbook in SharePoint, avoid making structural changes to a PivotTable at the same time as another editor. Allow the workbook to save, refresh only when the source is accessible, and check the final grouped labels before distributing a report.

When to use source period columns instead

Manual PivotTable grouping is usually the quickest choice for a one-off summary of clean dates. Source columns are preferable when reports need a fiscal calendar, named business weeks, custom seasons, or consistent categories across multiple PivotTables. They are also helpful when the PivotTable source does not support grouping or when a refresh process must reproduce the same layout reliably.

For a small worksheet, you can add columns such as Year, Month, and Quarter to the source, populate them consistently, and use them as PivotTable fields. For a model shared across reports, a separate calendar table is often cleaner than duplicating date logic in every source. Whichever approach you use, keep the original transaction date as well; period labels summarize dates but should not replace the detail needed for audit or drill-down.

Frequently Asked Questions

Why can’t I group dates in my Excel PivotTable?
Most often, the date field contains a blank, text value, or error, or the PivotTable uses a source that limits manual grouping. Check and correct the source values, refresh, and try again. For a Data Model or OLAP source, use a calendar or period field.

How do I group SharePoint dates by month and year?
Put the date field in Rows, right-click a date in the PivotTable, choose Group, and select both Months and Years. Selecting both keeps January in different years as separate groups.

Will a PivotTable update when SharePoint list dates change?
Not automatically in every workbook. A static export must be replaced or re-imported to include list changes. A refreshable connection may update through Data > Refresh All, subject to its connection and access settings; refresh the PivotTable afterward if needed.

Can I group dates in Excel for the web?
Some PivotTable operations are available in the browser, but the grouping controls may not be available for every workbook or source. Open the file in the desktop app if the Group command or interval choices are missing.

Can I group by fiscal quarter instead of calendar quarter?
Standard date grouping uses calendar periods. Add fiscal-year and fiscal-quarter fields to the source or use a calendar table that defines the organization’s fiscal calendar, then use those fields in the PivotTable.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.