Charts not updating when data is updated

Question

Hello,

I have built an Excel dashboard template that has 46 charts referencing cells in a table on the same worksheet. I made a slight change to the cells the charts reference for the title of the charts (the questions in a survey) to remove the question number. Of the 46 charts, 16 of them are not updating with the new title. I have tried Fn-9 to recalculate the whole workbook, Fn-Shift-9 to recalculate the worksheet, I have saved as and reopened, restarted my computer and reopened Excel, and nothing seems to update the charts. I made sure the Excel settings have calculation options set to "Automatic" and "recalculate workbook before saving" is also checked. This is maddening.

I am using Excel for Mac v16.59 on a Macbook Pro with MacOS Monterey 12.2.1.

Can anyone help me figure out how to get these charts to update? Thank you in advance for your help!

Answer 1

Hi Jim,

I really appreciate you looking through my file so carefully! It's interesting to hear that you don't see any English titles. For me, the charts from rows 205-308 on the first sheet (Toutes les reponses...) are showing what is in the screenshot below. Somehow, all the charts on the other tabs updated just fine.

What is most perplexing to me about this is that the formulas for each chart are the same. The only difference is the cells referenced. In fact, I just kept copying the same two charts I started with and changed the cell references and colors. And when I started converting this from English to French, I changed all the cells they are referencing in exactly the same way, so I don't understand why some are updating and some are not. This is also not an isolated issue for me. It has happened multiple times on several different files as I've been creating these dashboards.

My default language is also set to US English, but I wouldn't think that would matter since it should be picking up whatever is in the cell the formula references, right?

Thanks again for your help!

Answer 2

Dear Jess,

Thanks for your confirmation and I'll keep the information in the screenshots.

I'd like to confirm whether you have tried the formula 'dynamic chart title'!reference cell such as B12 in your environment mentioned in How To Create A Dynamic Chart Title In Excel?. If no, I suggest you restart the Mac and then do a test to see the result.

***Disclaimer:***Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Meanwhile, if you have a Windows computer, I suggest you also try the fomula in the Excel for Windows application to see if the issue also happens in your environment as well.

Thanks for your effort and time.

Cliff

Answer 3

Go it! Thanks for the explanation.

Even more weirdness, when I check the language on the chart it says it is set to German.

But the text in the slide title is set to French, so it really should be French. The Font is Arial which I am nearly positive has French glyphs.

To appease Cliff, I opened the workbook in Excel for Windows. The display is the same as on the Mac.

I can't explain how the English text got into your chart title boxes, but it is easy enough to fix them.

Select the chart title, then press Delete on the keyboard.

Then, use the Add Chart Element button on the Chart Design tab of the Ribbon. Choose Chart Title > Above Chart and Viola !

Answer 4

Hi Cliff,

Unfortunately, I don't have access to a Windows computer.

I tried the dynamic cell reference and it worked, but I have more than 200 charts on all the sheets in this workbook and I am going to be using this to create a template that I will use to produce 80 different dashboards. I want to figure out why this isn't working as it should (and as most of the other charts have) so I can ensure the charts will update accurately and consistently when I use the template to create 80 different versions. It will take an inordinate amount of time to go through each and every dashboard I create to see if there are places where something went wrong if this isn't working as it should.

When I click on the first chart that isn't updating to French (starting in row 204), I see this formula in the formula bar:

=SERIES('Toutes les réponses – Sommaire'!$B$12,'Toutes les réponses – Sommaire'!$I$5:$L$5,'Toutes les réponses – Sommaire'!$I$12:$L$12,1)

It's my understanding (see this Community Forum post) that the first part of this formula that I highlighted in red is supposed to be the series name for the chart. Doesn't that formula essentially make this a dynamic chart title? Why would the same formula in the chart above it (starting in row 185) update the chart title and this one doesn't?

Thanks,

Jess

Answer 5

Thanks for finding a workaround, Jim. I also appreciate you testing it out in Windows. It's good to know that this isn't a Mac-specific issue.

The tough part is that I really want to understand why Excel is behaving inconsistently from chart to chart so I don't need to use a workaround. As I said in my last message to Cliff, if I need to do a workaround like this for several charts as I create 80 dashboards, that's going to take a really long time. Do you have any idea why it is behaving differently and picking up the contents of the cell the chart references if you reinsert the title but not doing that initially? Clearly, it knows what cell that title should be coming from if it appears correctly with the contents of that cell when you reinsert the title.

Thanks again for your help.