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

Answer 1

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!

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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    

Answer 9

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

Answer 10

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.

Answer 11

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....

Answer 12

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

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

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

;)

Answer 20

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.

Answer 21

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.

Answer 22

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

Answer 23

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.

Answer 24

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).

Answer 25

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.

Answer 26

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

Answer 27

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

Answer 28

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

Answer 29

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

Answer 30

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.