Link Chart Text to a Cell

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

Comment from Robin
Time: Sunday, February 27, 2011, 11:49 pm

Many thanks for your blog and the comments contained herein. Finally I understand how to link chart data labels to cell values in VBA. Too easy! But not at all obvious. *Very* much appreciate that this information is public.

Comment from Peter Luiten
Time: Sunday, March 6, 2011, 6:09 pm

Jon, Many thanks for your blog. I would like to know if it is possible to refer to a chart name in a worksheet cell. I would like dynamic charts in my workbook without macros, and a cell formula referring to the chart name would make it easy.

For example, cell A1 helps define series names for Chart1, Chart2, Chart3. I could determine the content of A1 in a macro with each chart – but is there a formula which will identify the activated chart in A1 so that I can dispense with the macros?

Regards,
Peter Luiten

Comment from Jean
Time: Thursday, March 24, 2011, 1:48 pm

Hi Jon,

I am using Excel 2008 for MAC, Microsoft 2007 I am wondering if you can tell me how to make a scattered plot chart with the y-axis plotted and sorted by text? I am having trouble finding information and learning how to do this. Please help!

Jean

Comment from chris
Time: Thursday, May 19, 2011, 10:24 am

Fabulous!

Comment from Mike Lum
Time: Tuesday, October 4, 2011, 11:23 pm

Hi All,

I’m experiencing a strange problem wth the latest patches from MS in Excel 2007 – I have a textbox (shape) on a sheet that is linked to a cell (ie.=AJ$9$) – this cell is modified by an activeX drop-down control.
The problem is this – when you first open the file, the textbox will not update – you have to click on the text in the textbox or “hover” over the text in the textbox, after you do this, the textbox works normally and updates….

Prior to installing the updates this was not a problem.

A further issue is that this workbook is supplied to clients who use 2007 (and they’re experiencing this problem also) – ie. we can’t get them to use 2010 ….

Has anyone else had this problem….?

The only workaround we can get working is to change the textbox to an ActiveX Label (not the Form Label) and modify the caption property with code – not something we want to do to 20 workbooks …..

I know this is not quite on topic, but it’s related I think…

Thanks in advance…

Comment from Mike Lum
Time: Thursday, October 6, 2011, 6:16 pm

Thanks for your reply John,

1. Yes this is a recent issue – our dashboards have been working fine until only the last few weeks or so (w/c Sept 19 2011 onwards?) – my laptop was working fine until I applied the latest MS Updates -then suddenly I was experiencing the problems reported by our client.. :( – unfortunately we’re now super busy re-writing these DBs (20 of them!) – so I haven’t had time to uninstall the each of the 40 or so KB updates to find the offending one…

2. Yes – we’ve tried the code update approach – but these don’t update either, unless you hover the mouse over each shape… – so we’re now resigned to removing the shapes and using normal cells instead…

Mike

Comment from Howard Stoner
Time: Tuesday, October 18, 2011, 4:33 pm

I just went from Excel 2003 to 2010. There seems to be a bug (?) related to linking a text box to a cell in 2010 that wasn’t there in 2003.

The following happens to me now and I’d like a fix for it:
Open a new workbook, add some text to a cell, create a text box, and link the text box to the cell.
Save the file (either as .xlsx or .xls) and close it.
Now, open the file, don’t change anything, and try to close it.
On close, I always get the dialog box that asks: “Do you want to save the changes you made…?”, even though I made no changes.

Comment from DaleW
Time: Saturday, October 29, 2011, 3:38 pm

More than once I’ve noticed a bug in Excel 2003 — at least for chart sheets — if a chart label is linked to a cell containing a formula which evaluates to a number. At first the proper label is displayed on the chart, updating when the formula changes. Upon reopening the saved file, the label is static and no longer linked to a cell!? Using TEXT() so the cell holds a string allows the link to save properly and display the number.

Comment from DaleW
Time: Monday, October 31, 2011, 7:05 pm

Jon,

Yes, this is a bug that I’ve only seen after adding text boxes to chart sheets. I see now that my =TEXT() workaround was quite indirect. (I tend not to name cells holding text, but I like to name the variables controlled by scroll bars or spinners on a chart sheet, and I like to put the current numeric value next to such form controls.)

Excel 2003 doesn’t seem very adept at helping create linked references from a chart sheet to named cells in the same workbook. If one types in the name or even points to a named cell, Excel won’t automatically associate a sheetname or filename with the reference name. With chart titles and axis titles, Excel rejects such named references until we manually clarify it with the sheetname or filename, as it will do for text labels also on an embedded chart. It seems rather unintuitive behavior if we opt to name cells, but at least Excel doesn’t create named references that it can’t resolve. Alas, when adding text labels (or form labels) to a chart sheet, Excel merrily accepts the name, and all seems fine until that link breaks when one saves and reopens the file. The simple fix is to remember to type in the complete reference name manually — or just not use named cells.

PS – Please tell me that Excel 2010 is smart enough to accept a globally named range as a chart series range without our manually specifying that the range is in the same workbook as this chart. And that it accepts a locally (or globally) named range as an embedded chart series range without our manually specifying that the range is in the same worksheet (or workbook) as the chart.

Comment from thedude
Time: Wednesday, December 21, 2011, 2:37 pm

Jon,

Is there a limit to the number of text boxes you can dynamically link to a chart. I have several stats in a DCF model that I like to show with a cash flow diagram, like IRR, NPV, etc. and I’ve found the easiest way to collect the data is for a separate text box per stat and label linked to individual cells. This way I am able to capture the formatting for each stat and label without going through a VBA solution (this project must remain as uncomplicated as possible for coworkers ;) ).

I currently have 14 text boxes grouped into one object that I paste into my chart. I’d say the first 1/3 update and the last 2/3 fail to update. Does this make any sense? Have you run into this problem before? I am at a loss as to why this is happening and have scoured the internet for answers. BTW many thanks for your great site–it has been very useful to many many people.

Comment from Nicolas
Time: Monday, January 9, 2012, 11:12 am

Hi Jon, I hace a question of excel.
I know how to link a cell content to a text box in a chart.
But what i am trying to do is the other way.
For example, I have a chart with a curve, to this curve I added a Trendline, linear, logarithmical, doesn’t matter. Then I made the chart to show the formula of the trend and the R2 also.
I need to use the formula of the trend to get the forecast values.
Do you know haw can I link this formula to a cell or the only solution is to do copy/paste?

Comment from Nicolas
Time: Tuesday, January 10, 2012, 7:33 am

Excelent Jon, Thank you Very much
Can you do the same for a logarithmic function?