Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

- 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

share|improve this question
    
I saved it, exited, and re-opened the file, and it updated. –  user85134 Jun 9 '11 at 17:43
    
Are any of your calculated values derived with a volatile function? –  pnuts Mar 12 '13 at 4:18
add comment

2 Answers

Not sure about 2007 but there are setting that turn automatic calculations off. Look in the calculations options and see if it is set to manual.

share|improve this answer
    
I have Calculation set to automatic –  samJL Feb 5 '11 at 6:02
add comment

I have the same issue as the original writer. And I also tried already all the things he did and none of them worked. Also I now checked the calculations options and that was automatic just as it's supposed to be. Everything else works and calculates fine but this one chart does not update when the data changes (and the data calculates with a special funcion that separates different colored numbers from another set of data).

I found a temporary solution and made a macro (and a button for that) which first updates the data similarly to Ctrl+Alt+F9 and then switches the rows of my chart to columns and then again columns to rows. Strange, but works.

I used this to switch them:

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows

Of course I would prefer to find out the real way to fix it but this has also worked so far...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.