Pivot Table Issues

How to Group PivotTable Dates by Day in Excel

Bushra Parveen Sep 27, 2026 869 views

Question details

The user needs to group daily transaction data into one-day intervals within an Excel PivotTable.

How to Group PivotTable Dates by Day in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing daily transaction data using a PivotTable where specific date groupings are required.
Observed behavior: The PivotTable defaults to grouping by Month, Quarter, and Year, but does not provide a daily breakdown.

Before you start

Verify that your source data column contains valid Excel dates without any blank cells or text values, as improper data types will disable the grouping feature.

Solution 1Recommended

Use the Group Command to Set One-Day Intervals

Access the native Grouping dialog in your PivotTable to change the default date aggregation from months or years to specific daily intervals.

By default, Excel may automatically group dates into broader categories like months or quarters. You can manually override this to show daily breakdowns.

Right-click a date cell

Locate and right-click on any date value within the Row or Column Labels area of your PivotTable.

Select Group from the menu

Click on 'Group...' from the context menu that appears to open the Grouping dialog box.

Choose the Days option

In the 'By' list, click on 'Days' to highlight it. You can click to deselect 'Months' or 'Years' if you only want a daily breakdown.

Set the interval to 1

At the bottom of the dialog box, ensure the 'Number of days' field is set to 1, then click 'OK' to apply the changes.

Use the Group Command to Set One-Day Intervals

Data Format Check: If the Group option is unavailable, return to your original dataset and ensure there are no blank cells or dates stored as text in the date column.

Seamless Data Analysis

Group PivotTable Dates Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides an intuitive and powerful PivotTable feature that allows you to group dates by day, week, month, or custom intervals easily, fully replacing Microsoft Excel for data analysis tasks.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and navigate to your existing PivotTable.
2. Right-click the date field: Right-click any date item within the PivotTable to open the context menu.
3. Select the Group option: Choose 'Group' to launch the grouping configuration window.
4. Apply daily grouping: Select 'Days' as the grouping criteria, set the interval to 1, and click 'OK'.

Easily group PivotTable dates by day, month, quarter, or custom intervals.Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.Lightweight, free, and features a familiar user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Group option greyed out in my Excel PivotTable?

The Group option is typically disabled if the source data for the date column contains invalid entries, such as blank cells, text strings, or errors. Ensure all cells in the column are formatted strictly as dates.

Can I group PivotTable dates by weeks instead of days?

Yes, you can group by weeks. Right-click the date field, select Group, choose 'Days', and set the 'Number of days' to 7. Make sure to adjust the 'Starting at' date to a Monday or your preferred start of the week.

How do I remove the grouping from my PivotTable?

To remove any date grouping, simply right-click any grouped date cell in your PivotTable and select 'Ungroup'. The data will immediately revert to displaying individual date entries.

Excel & Data Problems