Forum Discussion
Solved
Excel Data Model Pivot Charts do not update on Refresh All
I've ran into an issue where pivot charts connected to a data model do not visually update after using Data> refresh all, despite source data having been updated. I originally encountered this in a dashboard I was building however as it contains sensitive information I can't share it. Fortunately I think I've managed to replicate it in a stripped down workbook with dummy data. See attached workbook.
If the costs columns are changed in the data tables (due to the formula used the values can be changed by just pressing F9), pressing refresh all does not cause the pivot charts to update. Strangely, closing and reopening the workbook or changing filters will cause it to update with the new data.
Detailed Explanation of Workbook:
There's two data tables used as the data sources.
To create dummy data the costs columns are using RandBetween(100,1000).
Note:// The original workbook has static data and both cause the problem I'm about to show so this formula is not the problem.
These tables are uploaded and used in power query in order to make a combined data set. This query is a simple collation and merge to get costs from each table per month and year in two columns.
The data tables are added to data model along with the CombinedData query. These are mapped to a reference table used to ensure month ordering works reliably in any pivots
The Charts sheet contains two pivot charts connected to the data model.
One chart uses the CombinedData query and the other uses an ExampleTable. When using RefreshAll on the data tab, these tables do not visually update, even when the data has changed. The charts will visually show the update if you close and reopen the work book, change a filter or even use the "Defer Layout Update" and select "Update". This leaves me to believe the data should have visually updated but for whatever reason it hasn't.
Misc Info:
OS: Windows 11
Version: Microsoft Excel for Microsoft 365 Version 2406 64-bit
I tested the workaround suggested by SergeiBaklan and this did work for me. It appears Pivot Charts, without a Pivot Table, using data directly from the data model have a visual bug with refreshing. The way around it is to rebuild the chart with a pivot table as I don't believe there's anyway to attach a pivot table to a pivot chart after the fact.
Although there's a workaround I still believe this is a visual bug. To emphasise this I rebuilt the workbook showing the issue (attached).
Two charts from the data model, with the exact same settings except one has a pivot table associated with it. Changing the source data and pressing Refresh All causes only the bottom chart to visually update:
Closing and reopening the workbook, modifying a filter or ticking defer layout update and then pressing update will all cause the top chart to visually update and show the same as the bottom chart. This is clearly not the intended functionality.
Hello
Try this :
Sub MajGraphIndependants() Dim objTcd As PivotTable For Each objTcd In ActiveWorkbook.PivotTables objTcd.PivotCache.Refresh objTcd.Update Next End Sub- I think you've misunderstood the main problem. The original charts had no pivot tables so this code wouldn't do anything in that instance. It does refresh pivot charts with a pivot table, but then Refresh All does this too.
Strangely it does seem to update a pivot chart w/o a pivot table as long as there's a pivot table related to the pivot cache elsewhere in the workbook. But again, the original problem was a workbook with a lot of charts directly from a data model without any pivot tables.A small oversight, for the macro to work you had to do a Refresh All before (I did it manually)
Sub MajGraphIndependants() Dim objTcd As PivotTable ActiveWorkbook.RefreshAll For Each objTcd In ActiveWorkbook.PivotTables objTcd.PivotCache.Refresh objTcd.Update Next End Sub
I tested the workaround suggested by SergeiBaklan and this did work for me. It appears Pivot Charts, without a Pivot Table, using data directly from the data model have a visual bug with refreshing. The way around it is to rebuild the chart with a pivot table as I don't believe there's anyway to attach a pivot table to a pivot chart after the fact.
Although there's a workaround I still believe this is a visual bug. To emphasise this I rebuilt the workbook showing the issue (attached).
Two charts from the data model, with the exact same settings except one has a pivot table associated with it. Changing the source data and pressing Refresh All causes only the bottom chart to visually update:
Closing and reopening the workbook, modifying a filter or ticking defer layout update and then pressing update will all cause the top chart to visually update and show the same as the bottom chart. This is clearly not the intended functionality.
I'd ask you to send a frown from Help->Feedback->Report a problem. I did, but more such feedbacks sooner the bug will be fixed.
- I will do that now, thank you.
I observe a similar action of refreshing charts on office 365 but 2021 seems to work normally. Have you already found a solution for this? Or does it remain to wait for the next update?
- Same problem here, started in build 2406. I am sorry to hear it is not fixed in the next beta build either.
Workaround is to build PivotTable and when connected PivotChart. Latest could be moved after that into separate sheet.
- Got the same issue - your workaround works, but would be interesting to know if it is a recent bug.
I started encountering this error in the last couple of weeks — around late August 2024. At first, I thought it was an issue with my laptop, but other colleagues using the shared workbook are experiencing the same problem. To investigate further, I created a new file with a simple table containing just 2 columns and 10 rows, added it to the data model, and then created a pivot chart. However, even with such a small dataset, the pivot chart does not refresh (although Power Pivot does).
By the way, thanks for the hintI checked that both on Current and Beta channels on Excel 365, same story, doesn't work. But it works on Excel 2019.
Another workaround could be in re-applying the filter, that could be slicer of field filter within PivotChart, doesn't matter.
Assume we have such table added to data model and PivotChart based on it
Change it and refresh - no effect
Now apply any filter
and reset it
We have expected result.
Hope that will be fixed one day.
RTNich I am experiencing the same issue, only my pivot charts no longer refreshing. This only began in las 1-2 weeks.
