Forum Discussion
Pivot table
having an Excel workbook with multiple sheets. each contain certain data and a pivot table in each one. recently started having problem that is when I copy a pivot table from an existing data sheet to a new inserted sheet and then changing the source of data to the new sheet and then refresh the pivot table then save and close the file; then when I try to open the file again I got an error and the most recent pivot table get deleted.
tried several ways to solve the issue with no avail noting I'm using Microsoft 365 for Mac.
appreciate any help
2 Replies
Below may help to fix the issue:
- Avoid Copying Pivot Tables Directly
Instead of copying the entire pivot table:
- Create a new pivot table from scratch on the new sheet using the new data source.
- This avoids hidden references or cache issues that may be carried over from the original pivot.
- Disable “Add to Data Model” Option
- Try disabling it when creating or modifying the pivot table.
- Check for Named Ranges
Ensure your new data source is:
- A defined named range, or
- A structured table (Insert → Table), which is more stable as a pivot source.
- Avoid Overlapping Pivot Cache
- Try creating the new pivot table in a fresh workbook, then copy it back.
- Or use different cache settings if you're comfortable with VBA (though this is limited on Mac).
- Update Excel
Make sure you're running the latest version of Microsoft 365 for Mac:
- Microsoft has patched several pivot-related bugs in recent updates.
Go to Help → Check for Updates in Excel.
thank you Kidd for the help.
I tried all above without avail. please note that disabling the "add to Data Model" option is not available in Mac version
