Chart won t update in Excel 2007

Question

I have an Excel document  2007  with a chart  Clustered Column  that gets its Data Series from cells containing calculated values  The calculated values never change directly  but only as a result of other cells in the sheet changing  When I change other cells in the sheet  the Data Series cells are recalculated  and show new values - but the Chart based on this Data Series refuses to update automatically  I can get the Chart to update by saving closing  or toggling one of the settings  such as reversing x y axis and then putting it back   or by re-selecting the Data Series  Every solution I have found online doesn t work   Yes I have Calculation set to automatic Ctrl Alt F9 updates everything fine  EXCEPT the chart I have recreated the chart several times  and on different computers I have tried VBA scripts like   Application Calculate Application CalculateFull Application CalculateFullRebuild ActiveWorkbook RefreshAll DoEvents     None of these update or refresh the chart  I do notice that if I type over my Data Series  actual numbers instead of calculations  it will update the chart - it s as if Excel doesn t want to recognize changes in the calculations  Has anyone experienced this before or know what I might do to fix the problem  Thank you

User · Answer

This is a known Excel bug     The best and fastest workaround is the Columns AutoFit - Trick   Sub Update Charts       Application ScreenUpdating   False     Temp   ActiveCell ColumnWidth     ActiveCell Columns AutoFit     ActiveCell ColumnWidth   Temp     Application ScreenUpdating   True End Sub

User · Answer

I had the same problem while working through a tutorial  very frustrating when you follow the steps and don t get the expected result    The tutorial to create a pie chart wanted me to select range A3 A10  then also select non-adjacent range E3 E10  I did so  I got the chart   It then asked me to change a value and watch the percentage change  then to look at the pie chart and see the update   It didn t update   I looked at the data source for the pie chart  and the range was bizarre  It had the A3 A10 range notated properly  but the E10 cell reference repeated several times  and it had all of the E cells listed in a random order  It looked like    SERIES   Revenue  A 3  A 10 Revenue  E 3 Revenue  E 10 Revenue  E 10 Revenue  E 10 Revenue  E 10 Revenue  E 10 Revenue  E 9 Revenue  E 8 Revenue  E 7 Revenue  E 6 Revenue  E 5 Revenue  E 4  1   I changed the data source to read    SERIES  Revenue  A 3  A 10 Revenue  E 3  E 10 1    Problem solved  Sometimes it s a matter of cleaning up your code so the calculations processor has less to sort through

User · Answer

I found that by creating the chart in a separate worksheet any updates will apply  HTH

User · Answer

My two cents for this problem--I was having a similar issue with a chart on an Access 2010 report  I was dynamically building a querydef  setting that as the rowsource on my report and then trying to loop through each series and set the properties of each series  What I eventually had to do was to break out the querydef creation and the property setting into separate subs  Additionally  I put a   SendKeys    DOWN    SendKeys    UP      at the bottom of each of the two subs

User · Answer

On changing the values of the source data  chart was not getting updated accordingly  Just closed all instances of excel and restarted  problem disappeared

User · Answer

This is an absurd bug that is severely hampering my work with Excel     Based on the work arounds posted I came to the following actions as the simplist way to move forward     Click on the graph you want update - Select CTRL-X  CTRL-V to cut and paste the graph in place    it will be forced to update

User · Answer

With Excel 2013  I have encountered the same problem  but it seems related to some  self-made  Add-Ins I m using  which add a tab to the ribbon  After unhooking these AddIns in the Options   Add-Ins   Goto  Exce-Add-Ins   Add Ins Box and restarting Excel      all charts in the workbook kept updating immediately  But as soon as I activate  hook  one of these Add-Ins  none of the charts would update anymore  This issue does not show up with other Add-Ins  like e g  Solver   so I still have to find out if it is caused by the Add-In creating an additional Tab in the ribbon  or just any Add-In of  xlam - type   Only by help of following workaround code I could persuade the charts to do their job   Dim chtChart As ChartObject  For Each chtChart In ActiveSheet ChartObjects    chtChart Chart ChartWizard Next chtChart   Strange enough  the  Chart Refresh - method didn t work either  but  Chart ChartWizard did  though no parameters are given so it doesn t change anything    And yes  the calculation method of the workbook was   still is automatic     update  2019-09-17    Since another  3rd party  Add-In did NOT prevent any charts from updating  I plunged into some further research  disabled all code  deleted all XML-contents of the customUI - ribbon and removed all relations to external libraries  as far as the VBE let me do   Still I could reliably prevent all charts from updating just by activating this now completely useless Add-In   As a last try  I re-built the Add-In from scratch by copying all code into a fresh  xlam - file  set all required library relations and finally added the XML-code for the ribbon with the customUI - editor   And behold   now all charts kept updating  -D    So - without proof - I just can guess that my old AddIn  originally of 2011  so probably done with Excel 2007 or 2010  was not completely compatible with Excel 2013  So it s always a good idea to do a new setup   re-create any AddIn with the present version of Excel  -   Good luck

User · Answer

I just had the same problem  and also found that the line would only display if I put in bad data  characters instead of numbers    This caused the line to appear  but changing back to valid data caused it to disappear again     What I found is that if I double-clicked the line  appearing with bad data   it showed me that it was on the SECONDARY axis for some reason   Changing that to PRIMARY axis solved my problem

User · Answer

I faced the same problem with my work last week when I added some more calculation to my sheet  After that  using radio buttons to select data to be presented on graphs did not update the graphs anymore    The best explanation I have been able to find so far is this  http   support microsoft com kb 243495  If I understood it right  if there are more than 65536 formulas that have another cell as a reference in your file  Excel starts to optimize the calculation and in some cases graphs don t update correctly anymore   If there is a workaround for this without using VBA macros  I would be glad to hear that  can t use those as the files need to be shared through SharePoint without VBA macros

User · Answer

Just activate the sheet where the chart is   Sheets 1  Activate   and your problem disappears    I had the same problem and none of the things you mentioned in question worked for me until I just activated sheet  The accepted answer didn t work for me neither    Alternatively you can make   ActiveCell Activate

User · Answer

Ok I have a solution  really      I found that the problem with my charts not updating first occurred shortly after I had hidden some data columns feeding the chart  and checked  show data hidden in rows and columns  in the Chart s  Select Data Source  msg box       I found that if I went back into the  Select Data Source  msg box and unchecked rechecked the  show data hidden in rows and columns  that the chart refreshes   Programatically I inserted the following into a Macro that I linked a button to  it refreshes all of my charts quick enough for a workaround to a known bug   This code assumes one chart per worksheet but another for statement for charts 1 to N could be added if desired   Sub RefreshCharts        Application ScreenUpdating   False  For I   1 To ActiveWorkbook Worksheets Count  Worksheets I  Activate      ActiveSheet ChartObjects  Chart 1   Activate      ActiveChart PlotVisibleOnly   True      ActiveChart PlotVisibleOnly   False  Next I      Application ScreenUpdating   True  End Sub

User · Answer

Charts are not  feeling  changes with direct inserting values to source cells with macro  You must send values out of chart cells  and after this use code like this  Worksheets  sheet1   Range  A1 K1   Worksheets  sheet2   Range  A4 K4   Value

User · Answer

I faced the same issue  The issue is due to restriction in no  of calculated formulas in your sheet  you can solved it using two ways   Manual force re-calculate   Press SHEFT   F9   Macro to force re-calculate  add below code to the end of the function which changes the data  Activesheet Calculate   I found the solution of it  From excel options make sure to change the calculation options as below  It changed sometimes to manual after heavy work in excel

User · Answer

We found a solution that doesn t involve VBA  multiplying some element of the chart s data range by TODAY  -TODAY   1   Even though the range was recalculating without this  the volatile nature of TODAY   somehow gives it an extra boost that triggers the chart recalc

User · Answer

From Excel 2013 on  there is the Chart Refreh method  https   msdn microsoft com de-de library office ff198180 aspx  which worked for me   Dim cht As ChartObject For Each cht In ThisWorkbook ActiveSheet ChartObjects     cht Chart Refresh Next cht

User · Answer

I have run into this same issue - not sure why  and when it happens the only way I have ever gotten the chart to force update is to change something in the chart definition itself  which can easily be done via VBA as in   Dim C As ChartObject  Set C   Me ChartObjects  chart name   C Chart ChartTitle Text   C Chart ChartTitle Text    1    There may be a better answer that gets to the bottom of the problem - but I thought this might help   Working on the sheet I would do a quick Ctrl-X  Ctrl-V on a piece of the chart  or the whole thing  to force the chart to update

User · Answer

This worked for me   it cuts and re-pastes the charts on the active worksheet  I based this off of Jason s code and a blog post I found in a quick Google search    Sub RepasteCharts    Dim StrTemp As String Dim IntTempTop As Integer Dim IntTempLeft As Integer   Set sht   ActiveSheet  For Each co In sht ChartObjects      Activate the chart     co Activate       Grab current position on worksheet     IntTempTop   ActiveChart Parent Top     IntTempLeft   ActiveChart Parent Left       Cut and paste     ActiveChart Parent Cut     ActiveSheet Paste       Reposition to original position     ActiveChart Parent Top   IntTempTop     ActiveChart Parent Left   IntTempLeft Next co   End Sub

User · Answer

For me  first disabling and then re-enabling calculation  e g  for the active worksheet  solved the problem   ThisWorkbook ActiveSheet EnableCalculation   False ThisWorkbook ActiveSheet EnableCalculation   True   Maybe execute twice in succession  All charts in the worksheet update entirely

User · Answer

I had the same problem with a simple pie chart     None of the macros worked that I tried  Nothing worked on cut  pasting  relocating chart     The Workaround I found was to edit the chart text  remove the labels  then re-select the labels   Once they re-appeared  they were updated

User · Answer

I have another problem of refeshing charts  When generating the charts automatically  some charts appear over and cache the text in the sheet  It happens to be a problem of refreshing the generated charts  When I zoom in or zoom out  I can get the expected results  So I post the solution here if it interest someone  Programmatically  I added this after generating charts    ActiveWindow Zoom   ActiveWindow Zoom   1 ActiveWindow Zoom   ActiveWindow Zoom - 1

User · Answer

This is the only thing I ve found to consistently update a chart   It cuts the root cause of the problem  I assume   the series data is getting cached in the chart   By forcing the chart to re-evaluate the series  we are clearing the cache     Force the charts to update Set sht   ActiveSheet For Each co In sht ChartObjects     co Activate     For Each sc In ActiveChart SeriesCollection         sc Select         temp   sc Formula         sc Formula     SERIES   1 1           sc Formula   temp     Next sc Next co

User · Answer

This might look extremely basic but I just tried Manual Calculating on the spreadsheet where the charts were  by pressing F9  and it worked  Tha VBA code for it is simply   Calculate

User · Answer

I was having a similar problem today with a 2010 file with a large number of formulas and several database connections  The chart axis that were not updating references ranges with hidden columns  similar to others in this chain  and the labels displayed the month and year  MMM-YY  of the dynamic data  I tried all solutions listed except for the VBA options as I d prefer to solve without code   I was able to solve the issues by encapsulating my dates  the axis labels  in a TEXT formula as such   TEXT A10  MMM-YY    And everything immediately updates when values change  Happy days again      From reading the other contributors issues above I started to think that the Charts were having problems with the DATE data type specifically  and therefore converting the values to text with the TEXT function resolved my issue  Hopefully this may help you as well  Just change the format within the double quotes  second argument of the TEXT function  to suit your needs

User · Answer

I had this problem and found that it was caused by having two excel applications running at the same time  If I closed everything and opened just the file I was having problems with the charts where dynamic like they should be  Maybe this helps

User · Answer

This works very well for me -- it flips axes on all charts and then flips them back  which causes them to refresh without changing at all    Refresh all charts For Each mysheet In ActiveWorkbook Sheets     mysheet Activate     For Each mychart In ActiveSheet ChartObjects         mychart Activate         ActiveChart PlotArea Select         ActiveChart PlotBy   xlRows         ActiveChart PlotBy   xlColumns         ActiveChart PlotBy   xlRows     Next Next

User · Answer

As i tried pretty much ALL the presented solutions and since none worked in my case  I ll add my two cents here as well  Hopefully it helps someone else   The consensus on this issue seems to be that we need to somehow force excel to redraw the graph since it is not doing it when it should   My solution was to kill the X-Axis data and replace it with nothing  before changing it to what i wanted  Here my code   With wsReport      Activate      ChartObjects 1  Activate     ActiveChart FullSeriesCollection 1  XValues        Kill data here          Range  A1   Select  Forwhatever reason a Select statement was needed          ChartObjects 1  Activate         ActiveChart FullSeriesCollection 1  XValues     tblRef Secs   End With End Sub

User · Answer

I had the same issue as the poster  Basically I m running a dashboard  and I have a bunch of named ranges that are populated with return values from some UDFs  On the dashboard  there are some pie charts with data series tied to cells which contain these named ranges  the problem also occurs if the data series target cells contain the UDFs directly  bypassing the named ranges    I change a cell value which contains  for example  the date range to base the dashboard on  and the named ranges and UDFs are forced to calculate  However  the pie charts do not update--for some reason  other types of charts do  And by the way  these are chart objects  not chart sheets  Anyway  let s cut to the solution   I didn t want to visibly change the chart title or some other aspect of it  and anyway I noticed this wasn t updating my charts consistently  Sometimes the first time I triggered the calculation the pies would update  but with subsequent calculations the pies would not  I did notice  however  that every time I made a change in the code my dashboard worked  Thus   Solution   With ActiveWorkbook VBProject VBComponents  ThisWorkbook   CodeModule        AddFromString   test        DeleteLines 1  End With   If you re using the Workbook module  I wasn t in this case   just create a new module and reference that instead

User · Answer

For me the macro didn t update the x-axis for all series  but only the first one  The solution I found was to update the x-axis for all series and then it refrehsed  also I had code to change the format of the x-axis  but I don t think that that was the problem    ActiveSheet ChartObjects  Diagram 7   Activate ActiveChart Axes xlCategory  Select ActiveChart SeriesCollection 1  XValues             ActiveChart SeriesCollection 1  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 2  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 3  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 4  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 5  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 6  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 7  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 8  XValues     YYY  BQ 85  BQ 8844    Full macro   Sub TEST       TEST Makro   ActiveSheet ChartObjects  Diagram 7   Activate ActiveChart Axes xlCategory  Select Selection TickLabels NumberFormat       ActiveSheet ChartObjects  Diagram 7   Activate ActiveChart SeriesCollection 1  XValues             ActiveChart SeriesCollection 1  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 2  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 3  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 4  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 5  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 6  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 7  XValues     YYY  BQ 85  BQ 8844  ActiveChart SeriesCollection 8  XValues     YYY  BQ 85  BQ 8844  ActiveChart Axes xlCategory  Select ActiveChart Axes xlCategory  TickMarkSpacing   730 ActiveChart Axes xlCategory  TickLabelSpacing   730 End Sub

User · Answer

To update a chart just put a Sheets  Sheet1   Calculate into your sub procedure  If you have more charts on different tabs  then just create a worksheet loop

User · Answer

I had this problem while generating 1000  graphs through VBA  I generated the graphs and assigned a range to their series  However  when the sheet recalculated the graphs wouldn t update as the data ranges changed values   Solution --  I turned WrapText off before the For   Next Loop that generates the graphs and then turned it on again after the loop   Workbooks x  Worksheets x  Cells WrapText False    and after     Workbooks x  Worksheets x  Cells WrapText True   This a great solution because it updates 1000  graphs at once without looping through them all and changing something individually   Also  I m not really sure why this works  I suppose when WrapText changes one property of the data range it makes the graph update  although I have no documentation on this

User · Answer

I struggled with this problem  too  Finally solved it by recalculating the sheet that has the chart data AFTER the custom function has recalculated  So  in Sheet 1  I have a cell that contains   ComputeScore     In the VBA module  the function is defined as Volatile  to ensure that ComputeScore   runs after any updates to the spreadsheet   Function ComputeScore   As Double     Application Volatile True           do some stuff to get a total           ComputeScore   theTotal End Function   Then  in the VBA of Sheet 1  this   Private Sub Worksheet Calculate        Recalculate the charts data page to force the charts to update       Otherwise  they don t update until the next change to a sheet  and so      chart data is always one update behind the user s data changes      Application ScreenUpdating   False     Application EnableEvents   False     Application Calculation   xlManual     Sheets  Charts Data   Calculate     Application Calculation   xlAutomatic     Application EnableEvents   True     Application ScreenUpdating   True End Sub   So  the sheet named Charts Data  which references the custom function cell of Sheet 1  will do a recalculation AFTER the ComputeScore   function has updated the cell of Sheet 1  since Worksheet Calculate   fires after the ComputeScore   recalc  This additional round of calculation of the chart data causes the chart to update now  rather than later or not at all  The setting of EnableEvents and xlManual keeps infinite recalc loops and other event problems from occurring

User · Answer

This problem is ridiculous  No one s solution worked for me in 2010  but I based mine off of tpascale s   Dim C As ChartObject Set C   ActiveSheet ChartObjects  CTR Chart   C Chart SetSourceData Source  Range         KeywordBreakdown  A 8  A 12 KeywordBreakdown  E 8  E 12     Simply redefined the Source Data range  If it s a named range  that could conceivably be reasonably clean  I guess the best solution to this is keep trying to modify different chart properties until it refreshes

User · Answer

What worked for me was using a macro to insert remove a column in the data table for the chart  This will cause the chart to update the data selection   I found this to be the fastest way to fix it

User · Answer

Just spent half a day on this myself   I have a macro that changes values that are the data for a chart  All worked fine in Excel 2003  but in Excel 2007 the chart seems to lose all connection to its data  although manually changing data values in two column triggered a recalc   My solution has been to make all charts on the active sheet invisible before the change in data  then make them visible again and call chart refresh for good measure    It only seems to be visible charts that have this problem updating     This works for me and also handles similar issues with charts as well as chart objects  The refresh may not be necessary - more testing needed   Dim chrt As Chart Dim chrtVis As XlSheetVisibility Dim sht As Worksheet Dim bChartVisible   As Boolean Dim iCount As Long  Dim co As ChartObject  On Error Resume Next  Set chrt   ActiveChart  If Not chrt Is Nothing Then         chrtVis   chrt Visible         chrt Visible   xlSheetHidden         End If  Set sht   ActiveSheet  If Not sht Is Nothing Then         ReDim bChartVisible 1 To sht ChartObjects Count  As Boolean         iCount   1         For Each co In sht ChartObjects             bChartVisible iCount    co Visible             co Visible   False             iCount   iCount   1             Next co         End If   DO MACRO STUFF THAT CHANGES DATA      If Not sht Is Nothing Then         iCount   1         For Each co In sht ChartObjects             co Visible   bChartVisible iCount              co Chart Refresh             iCount   iCount   1         Next co         End If  If Not chrt Is Nothing Then         chrt Visible   chrtVis          chrt Refresh         If chrt Visible Then             chrt Select             End If         End If  On Error GoTo 0

User · Answer

I had a similar problem - Charts didn t appear to update   I tried just about everything on this thread with no luck   I finally realized that the charts that I was copying and pasting were linked to the source data  and that is why they were all showing the same results     Be sure you are copying and pasting pictures before you go through all the other motions

[vba] Chart won't update in Excel (2007)

Examples related to vba

Examples related to excel

Examples related to excel-2007