Comment from Tim Mayes
Time: Friday, June 6, 2008, 1:00 am

Thanks for this Jon. This is an incredibly useful technique, and an underused one. I use it most commonly for chart titles since it allows me to dynamically build the title in a cell. That way I can use the sheet as a template and the chart title will always be correct when I change the data.

Also, you can insert “floating” text (I don’t know what else to call it, but it isn’t connected to anything). In Excel 2003 just select the chart and press = and then select a cell. In Excel 2007, you have to first insert a textbox into the chart and then use the textbox.

One other tip is using the Text() function when building strings to use. This allows you to construct strings that have dynamic numbers in them, and you control the number format.

Comment from Michel
Time: Sunday, June 8, 2008, 3:37 pm

I used a similar technique to have multilingual charts.
With a Choose command, I let the user (me) choose the language and all titles follow…

Comment from Sandi Mays
Time: Monday, June 9, 2008, 2:49 pm

I thought I knew everything about Excel, but never came across this tip. Thank you for posting!

Comment from Orhan Karsligil
Time: Friday, July 18, 2008, 2:16 pm

In Excel 2007 the text box with formula does not automatically change when the referenced cell changes if the file has been opened and saved by Excel 2003 previously.

Comment from Mark
Time: Monday, August 4, 2008, 12:34 pm

I’ve been doing this for years in Excel 2003. Last week, I upgraded to Office 2008, and all my embedded formulas have disappeared from my charts. I’m not happy with the upgrade.

I can get the embedded formula to work in a new Excel 2008 spread sheet, but my old 2008 spreadsheets refuse to display them.

Comment from leroy
Time: Friday, August 8, 2008, 10:40 pm

How can I get the my values to display when using the % as part of the data label in a bar chart?

Comment from Gaetan
Time: Tuesday, August 19, 2008, 10:22 am

John and Orhan -

I’m not 100% sure of this code, but this seems to work ok on a big project to solve the “saved in 2003 and open in 2007″ issue:

Sub xl_2007_recreate_link_images_cells()
    Dim sh As Worksheet
    Dim shp As Shape
    Dim t As String
    For Each sh In ThisWorkbook.Sheets
        For Each shp In sh.Shapes
            If shp.Type = msoTextBox Or shp.Type = 1 Then
            t = shp.DrawingObject.Formula
            If Right(t, 1) = " " Then t = Left(t, Len(t) - 1)
            If t <> "" Then
                shp.DrawingObject.Formula = t
            End If
            End If
        Next
    Next
End Sub
 

That said, i don’t know why the shp.DrawingObject.Formula add a space at the end.

Gaetan

Comment from Gaetan
Time: Tuesday, August 19, 2008, 11:16 am

Jon –

Thks for your reply

That was of course my first idea, but it happened not to work with names because of an added space at the end… very weird…
If I well remember, it works well with normal reference (=A1)

Comment from Kathy
Time: Saturday, October 18, 2008, 4:18 am

I’ve been using this technique successfully for a while, but I have come across two PCs (Excel 2007) where you can’t do this. Typing = into the chart title etc just displays = and clicking on a cell just deselects the chart. Same chart on another machine (being updated by the same person) works fine. Any ideas?

Comment from Rebecca
Time: Tuesday, November 18, 2008, 10:41 pm

Thank you! This is exactly what I needed!

Comment from Marty
Time: Tuesday, October 13, 2009, 6:07 pm

I’m not sure if this is a Excel 2007 issue, but the example code works well for text boxes or other created shapes, but doesn’t work if I have an axis label or chart title selected. Instead, I get a Runtime 438, not supported.

It wouldn’t be too bad if I could record a macro, but I’m using 2007 and it ignores moste verything I do with charts. What I’m really after is VBA code to set an axis title formula to reference a cell. Any suggestions? Impossible?

Comment from Marty
Time: Wednesday, October 14, 2009, 10:33 am

Jon,

Worked beautifully. Thank you very much. Part of my trouble was that I am using two charts on one chart sheet and Excel won’t let me click on the axis and assign it to a cell. I can do that if there is a single chart on the chart sheet, but not if I have two chart objects embedded. Not really sure why that is.

Man, my charts have come a long way since discovering this excellent resource. Thanks for all you do. I’m glad that the one person who knows everything about Excel charts is willing to share.

Comment from Terry
Time: Friday, November 20, 2009, 7:31 pm

Hi everyone,

Jon (or anyone else), do you have any feeling on the reason MS decided to arbitrarily disallow use of functions in those text box formulas?

In my particular case at the moment, it’s a pivotchart.
It’s not like it serves to limit the computation that a graph update can require–relative to using an intermediary cell, that is–in fact it should increase it marginally. (If that were the motive, they would’ve also prevented such an obvious work-around by preventing re-evaluation of any cell referenced by a text box in a chart.)

So if it’s not about computation involved in refreshing,… my best guess would be that it’s something about not wanting to deal with some would-be implication of having formula located anywhere but cells. (Or are there already formulas outside cells?)

Any thoughts?

Comment from Matt
Time: Thursday, May 13, 2010, 5:56 pm

Your article is very helpful when you want the text to go from a cell to a textbox on a chart, but what I am having a hard time figuring out (and finding info on), is how to have the text populate in a cell from the textbox on the chart. It’s probably something simple, but it just isn’t coming to me. Also, I would prefer to avoid VBA in this case if possible. (Otherwise I wouldn’t need to ask. lol)

Comment from Matt
Time: Thursday, May 13, 2010, 10:04 pm

I was just thinking that it might be nice for the “user” to be able to change the “instances >” someNumber from a Textbox on the dynamic chart itself, rather than having to link a cell to the chart. I was curious to see if it could be done, considering Microsoft certainly tries to think of everything. =P