Use One Excel Timeline for PivotTables from Multiple Data Sources
Question details
The user needs one timeline to filter PivotTables that use data from multiple source tables or files.
- Product
- Microsoft Excel Power Pivot and PivotTables
- Device & OS
- not provided
- Scenario
- Multiple PivotTables need to respond to the same date-range selection from one timeline.
- Observed behavior
- A timeline controls only PivotTables that share the same date field and Data Model structure, so PivotTables built from separate sources are not all filtered together.
Check that every source table has a date column containing valid Excel dates rather than text. List the source tables and date fields that must be controlled by the shared timeline.
Create a shared Calendar table in the Excel Data Model
Use this method when PivotTables come from multiple imported tables and must respond to one date timeline.
A timeline can control multiple PivotTables when they use the same Data Model and a common date field. Create one Calendar table, relate it to each data table, and use the Calendar table's date field in every PivotTable.
In Excel, select a blank table containing every required date, then choose Power Pivot > Add to Data Model. Include a unique Date column covering the full reporting period.
Select each source table and choose Power Pivot > Add to Data Model, or use Power Pivot > Manage > From Other Sources to import the data tables.
Open Power Pivot > Manage, switch to Diagram View, and connect Calendar[Date] to the date column in each data table. Make sure the date columns use compatible date values.
Choose Insert > PivotTable, select Use this workbook's Data Model, and create each PivotTable from that shared model. Use Calendar[Date] for date filtering instead of a source table's date field.
Select a PivotTable, choose PivotTable Analyze > Insert Timeline, select Calendar[Date], and click OK. Select the timeline, choose Report Connections or PivotTable Connections, and enable every related PivotTable.
Rebuild PivotTables under one shared Data Model
Use this approach when existing PivotTables were created from separate ranges or connections and cannot share the current timeline.
Use WPS Office as a Lightweight Alternative for PivotTable Reporting
WPS Office is a free, lightweight alternative for everyday spreadsheet reporting and supports common Excel workbook formats. It offers a familiar interface for opening, editing, and migrating Excel files, while advanced Power Pivot Data Model relationships and shared timelines may still require Microsoft Excel.
- 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the home screen.
- 2. Open the Excel workbook: Choose Open from the File menu and select the existing .xlsx or .xls workbook containing the source data and PivotTables.
- 3. Review and update the report: Use the Spreadsheet tools to inspect source tables, refresh supported PivotTables, and save the result in an Excel-compatible format.
Frequently Asked Questions
Why does one Excel timeline not filter all of my PivotTables?
The PivotTables may use different PivotCaches, source ranges, date fields, or Data Models. A shared timeline requires the PivotTables to use the same model and the same Calendar table date field.
Can I connect a timeline to PivotTables from different workbooks?
Not directly. Import the required tables into one Excel workbook's Power Pivot Data Model, relate them through a shared Calendar table, and rebuild the PivotTables from that model.
Which date field should I use for the timeline?
Use the unique date column from the shared Calendar table, such as Calendar[Date]. Do not use separate date columns from individual transaction or source tables.
How do I connect an existing timeline to more PivotTables?
Select the timeline, open the Timeline or PivotTable Analyze tab, choose Report Connections or PivotTable Connections, and check the PivotTables that use the same Data Model.
