How to Troubleshoot Excel PivotTable Refresh and Filter Problems
Question details
The user needs to identify why an Excel PivotTable refreshes on one computer but not another and resolve refresh or filter failures caused by source connections, paths, permissions, security settings, or local Excel configuration.
- Product
- Microsoft Excel / WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user works with Excel PivotTables and tries to refresh or filter them on multiple computers, with one computer succeeding and another failing.
- Observed behavior
- The PivotTable refresh or filter fails on one computer, and VBA error 1004 may appear when the PivotTable connection or destination is unavailable.
Before changing connection settings, open the workbook on the affected computer and note whether the PivotTable source is a range in the same workbook, an external workbook, a database, or a connection file.
Check the PivotTable Data Source and Connection Path
Use this first when a PivotTable refreshes on one computer but not another to confirm the source range or connection is available.
Most refresh failures are caused by an unavailable source or a connection path that points to a location that does not exist or is not accessible on the failing computer.
Click any cell inside the PivotTable, go to the PivotTable Analyze tab, and select Change Data Source to view the current range or external connection.
If the source is an external workbook or connection, open Data > Queries & Connections > Connections, select the connection, and check whether the file path, database name, or server is reachable.
On both computers, open the same workbook and compare the source path, connection properties, trusted location, and Excel version under File > Account > About Excel.
Align Permissions, Trusted Locations, and Add-ins
Use this when the source is available but Excel still blocks refresh or filters on one machine because of security settings.
Test the PivotTable Connection Before Running VBA Refresh Commands
Use this when you see VBA error 1004 to separate connection issues from code issues.
Use WPS Office Spreadsheets as a Free Excel Alternative for PivotTable Work
When Excel PivotTable connection errors block your workflow, WPS Office Spreadsheets gives you a free, lightweight way to open and work with XLSX PivotTables. It keeps Microsoft Office format compatibility, provides a familiar ribbon-style interface, and makes migration easy without paying for a new license.
Frequently Asked Questions
Why does my Excel PivotTable refresh work on one computer but not another?
The most common reasons are a different source path, missing connection file, blocked trusted location, insufficient permissions, a conflicting add-in, or a different Excel build. Start by checking PivotTable Analyze > Change Data Source and Data > Queries & Connections > Connections.
How do I find the current source of a PivotTable?
Click any cell in the PivotTable, open the PivotTable Analyze tab, and click Change Data Source. If it uses an external connection, go to Data > Queries & Connections > Connections to inspect the connection string and file path.
What does VBA error 1004 mean when refreshing a PivotTable?
Error 1004 usually means the PivotTable connection or destination is unavailable. Test by refreshing manually from the PivotTable Analyze tab, then check the PivotCache.Connection property in the VBA editor before running the macro again.
Why do PivotTable filters keep old items after refreshing?
This may happen when Excel retains deleted items in the field cache. Click the PivotTable, go to PivotTable Analyze > Options > Data tab, set 'Number of items to retain per field' to None, then refresh and reapply the filter.
Can WPS Office open and refresh an Excel PivotTable?
Yes. WPS Office Spreadsheets supports Excel .xlsx and .xls PivotTables, so you can open the workbook, use the PivotTable tools or Data tab, and refresh the PivotTable without changing the file format.
