'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Update the Date Range in an Existing Excel PivotTable
Question details
The user needs to update the source date range in an existing Excel PivotTable without generating unwanted new grouped fields like 'Days(Date)'.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Modifying fiscal-year source dates in a dataset that is already linked to an existing PivotTable.
- Observed behavior
- Updating the data source with new dates causes Excel to create a new grouped field (e.g., Days(Date)) instead of updating the existing PivotTable date field.
Ensure your source data column contains properly formatted real Excel dates rather than text strings, and check that no blank cells exist within your date column before refreshing the PivotTable.
Update Data Source and Regroup PivotTable Dates
Use this method to manually update the PivotTable data source and resolve any unwanted auto-grouping behaviors caused by the new fiscal year dates.
When you add new dates spanning different fiscal periods, Excel's automatic date grouping may create redundant fields such as 'Days(Date)' instead of merging them into your existing date hierarchy. Ungrouping and regrouping the fields forces the PivotTable to re-evaluate the entire date range.
Click anywhere inside your PivotTable. Navigate to the 'PivotTable Analyze' tab (or 'Options' in older versions) on the ribbon, click 'Change Data Source', and highlight the newly expanded date range in your source sheet.
Click the 'Refresh' button on the 'PivotTable Analyze' tab to import the new data. You may notice a new grouped field like 'Days(Date)' appearing in your PivotTable Fields list.
To remove the unwanted duplicate groups, right-click on any date or grouped date field currently visible in your PivotTable and select 'Ungroup' from the context menu.
Right-click the original 'Date' field in the PivotTable, select 'Group', and choose your preferred grouping intervals (e.g., Months, Quarters, Years) from the grouping dialogue box. Click 'OK'.
Update PivotTable Data Easily in WPS Spreadsheet
WPS Spreadsheet offers powerful and highly compatible PivotTable tools that allow you to seamlessly update date groupings without creating duplicate fields. Experience a smooth, familiar interface designed to make data analysis fast and frustration-free.
- 1. Open Your Workbook in WPS: Launch WPS Spreadsheet and open your existing Excel file containing the PivotTable and source data.
- 2. Update the Data Range: Select the PivotTable, navigate to the 'Analyze' tab, and click 'Change Data Source' to include your new dates.
- 3. Refresh and Regroup: Click 'Refresh'. If automatic grouping occurs, simply right-click the date field, select 'Ungroup', and then 'Group' to easily set your preferred date hierarchy.
Frequently Asked Questions
Why does Excel create a 'Days(Date)' field when I update my PivotTable?
This occurs because Excel's automatic time grouping feature attempts to categorize newly added dates that don't perfectly align with the existing group structure. You can resolve this by ungrouping the current date fields and regrouping them.
How do I stop PivotTables from automatically grouping dates?
You can turn off this feature by navigating to File > Options > Data (or Advanced in older Excel versions) and checking the box for 'Disable automatic grouping of Date/Time columns in PivotTables'.
Do I have to manually change the data source every time I add new dates?
No. If you convert your source data into an Excel Table (by highlighting the data and pressing Ctrl+T), the PivotTable's data range becomes dynamic. Any new rows added will be automatically included the next time you click Refresh.
