A very common question I am asked is, “How do I link text on a chart to text in a cell?” This is really not too difficult, but it’s also not intuitive. Except for axis tick labels, text elements in an Excel chart can be linked to worksheet cells. These text elements include the chart title, axis titles, data labels, and shapes.
To link a text element to a cell, you first must create the text element, using a temporary unlinked label. (Technically a data label is linked to something outside the chart if you use one of the standard options, such as series name, values, or labels, but these are not formulaic links.)
The screen shot below shows a chart, the chart source data range B15:C22, and several labels in columns D and E which will be applied to the chart. You can also see the Name Box in the top left, which usually identifies the selected object, and the Formula Bar to the right of the Name Box.
Link Chart and Axis Titles
Start by linking the title to a cell. A chart with one series uses the series name as the chart title. Select the chart title, and the Name Box shows Chart Title.
Click in the Formula Bar, type =, then click on the cell you want to link to, or type the fully referenced address of the cell. Actually the click in the formula bar is unnecessary, as Excel activates the formula bar when any key is clicked. The link formula appears in the Name Box, and the linked cell is highlighted with the marching ants border.
Press Enter. The chart title updates to show the contents of the selected cell. If the cell contents are changed, the chart title will change accordingly.
Use the same protocol twice more to customize the X and Y axis titles.
Link Data Labels
If you are going to link one or two labels, use the protocol shown here. If you are going to link whole series at a time, this protocol becomes tedious very quickly. Fortunately this can be automated through VBA, and there are several utilities around the internet that do this for you. Two good ones (and free ones) are Rob Bovey’s Chart Labeler and John Walkenbach’s Chart Tools. They install quickly and interface smoothly with Excel.
Add a data label to the first point in the series. Select the series with one click, then select the point with another click. Press CTRL+1 (numeral one) to open the Format Point dialog. Select any of the label options, such as Series Name.
The label has been moved from its default Right position to Above. Select the series of labels with one click, then select the specific label with another click. If the Name Box were wider it would display "Alpha" Point "1" Data Label.
Click in the Formula Bar (optional), type =, and click on the cell you want to link to the data label. The link formula appears in the Name Box, and the linked cell is highlighted with the marching ants border.
Press Enter and the data label updates. Add a data label to the last point in the series, and link it to a cell using this process.
Link Shapes
Linking a shape to a cell is a bit trickier. Start by adding a shape to the chart (here the shape is an oval).
Type =, click on the cell that contains the text you want to display in the shape. The link formula appears in the Name Box, and the linked cell is highlighted with the marching ants border.
Press Enter, and if you’re using Excel 2007, the shape picks up the link. Here’s where there’s a bug in earlier versions of Excel. Instead of the selected shape picking up the link, Excel adds a new textbox with the link. Delete the textbox.
A link can be added to the shape using VBA, so I wrote a short procedure to make it happen. Copy this code and paste it into a regular code module (see How To: Use Someone Else’s Macro if you are not familiar with this). Select the shape and run the following procedure.
Sub LabelSelectedShape()
Dim rng As Range
Set rng = Application.InputBox(Type:=8, _
Prompt:="Select the cell with the intended label for the selected shape.")
With Selection
.Formula = "=" & rng.Address(External:=True)
End With
End Sub
Select a cell and click Okay. The oval has been linked to the cell.
Link Formula
Any formula in a chart text element must be a link formula, that is, the formula can only link to a cell or range (including a named range). You cannot write a formula that uses any functions. If you have text in cells A1 and B1, and you want your text element to use the formula ="the fraction is "&A1&"/"&B1, you have to commandeer another cell, say, cell C1, and enter your formula in that cell, then link the text element to the cell with the formula.
Tim Mayes says
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.
Jon Peltier says
Tim –
The “floating” text is simply a textbox. If the chart is selected and you start typing, the text goes into the formula bar. This text is placed in a textbox when you press Enter. If the text is a link formula, the textbox is linked.
Too many people ended up with random textboxes in their charts without knowing why, so Microsoft changed the behavior. In Excel 2007 you need to explicitly add the textbox first. The formula bar still collects your keystrokes, but they are discarded.
I use the F4 key a lot when editing a lot of charts. Sometimes I wonder why the F4 key didn’t do what I expected. Lately I notice that when it doesn’t work in 2003, it’s because I typed 4 instead of F4, and I get a textbox that contains “4”. In 2007 when F4 doesn’t work, I groan because it’s how 2007 was designed.
Michel says
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…
Jon Peltier says
And when the number of languages and phrases becomes too large for CHOOSE, you can use INDEX or VLOOKUP
Sandi Mays says
I thought I knew everything about Excel, but never came across this tip. Thank you for posting!
Orhan Karsligil says
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.
Jon Peltier says
Orhan –
Thanks for describing this glitch. I’ve heard people complain about the problem (or something similar), but nobody noticed the saving is 2003 part. When the file is saved in 2007 are the links fixed?
Mark says
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.
Jon Peltier says
Mark – 2008? You switched to a Mac too? My condolences. Or did you mean 2007?
I’ve heard of these links not surviving the upgrade from 2003 to 2007, but have not experienced it personally. The links seem to work fine in charts created in 2007.
leroy says
How can I get the my values to display when using the % as part of the data label in a bar chart?
Gaetan says
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 SubThat said, i don’t know why the shp.DrawingObject.Formula add a space at the end.
Gaetan
Jon Peltier says
Gaetan –
I don’t know why it works, either, or why the trailing space should matter. Perhaps something as simple as
is all that’s really needed.
Gaetan says
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)
Kathy says
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?
Jon Peltier says
2007 makes it a bit harder to add labels accidentally. You have to make sure the text element itself is selected, but the cursor is not seen in the text itself, nor is any of the text selected.
If you have done this and it still isn’t working, I don’t know why, because I haven’t had this problem.
Rebecca says
Thank you! This is exactly what I needed!
Marty says
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?
Jon Peltier says
Marty –
The code only works for shapes. I added the code because the old way of selecting the shape and entering a formula in the formula bar doesn’t work in 2007. The manual way of adding linked labels to other chart text elements works as in 2003.
This code programmatically links the X and Y axis titles to cells:
ActiveChart.Axes(xlCategory).AxisTitle.Text = “=Sheet1!R2C1”
ActiveChart.Axes(xlValue).AxisTitle.Text = “=Sheet1!R2C2”
Marty says
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.
Terry says
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?
Jon Peltier says
Terry –
I don’t think that functions were arbitrarily disallowed from text element formula links. I think these links were intended to be just that: links. Charts themselves are not responsible for many calculations, other than figuring out positions and sizes of the various chart elements. It is up to the spreadsheet designer to provide the appropriate text in the linked cell, by functions or other means.
Matt says
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)
Jon Peltier says
Matt –
It doesn’t work that way. Can’t you generate the contents of a cell using a formula? This can be based on the same information that the chart text uses.
Matt says
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
Jon Peltier says
Matt –
You might be able to use a scrollbar to change the value in a cell, then show this value in a textbox in the chart. Of course, if the chart is embedded in a sheet, the chart is surrounded by an ocean of cells for you to use.
Robin says
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.
Peter Luiten says
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
Jon Peltier says
Peter –
Links flow in one direction, from worksheet to chart. The sheet can have no knowledge about the chart, unless you use VBA.
I don’t understand why your formulas need to know which chart is which. You need to set up dynamic ranges for each series in each chart anyway, and the chart just shows what’s in the sheet.
Jean says
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
chris says
Fabulous!
Mike Lum says
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…
Jon Peltier says
Mike –
Excel 2007 has issues with linking of shapes to cell text. Other than using code to refresh the links when the workbook is opened (or on another suitable event), I can’t think of a way to fix the issue you’re having.
Is it a recent problem you’re seeing? I’ve heard of a few problems that people attribute to recent updates, that is, they only started occurring recently. I have no direct knowledge of any of these issues.
Mike Lum says
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
Howard Stoner says
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.
Jon Peltier says
Howard – I’d never noticed this, but I can confirm it.
DaleW says
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.
Jon Peltier says
Dale –
I still use Excel 2003 most of the time, and I have not ever seen this problem. But then, I almost never use chart sheets: I prefer to array several charts on a worksheet so I can see them all at once.
Is this a problem for all linkable text in the chart (chart and axis titles, textboxes, data labels), or just for a particular type?
DaleW says
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.
thedude says
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.
Jon Peltier says
Dude –
In Excel 2003 I’d confidently say the limit was probably something like 256 linked textboxes. But I’ve had problems like yours with links to cells updating properly in 2007. I have not yet figured out a viable workaround.
Nicolas says
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?
Jon Peltier says
Nicolas –
Check out Error 3 in Trendline Fitting Errors. Then check out the other errors just to be safe.
Nicolas says
Excelent Jon, Thank you Very much
Can you do the same for a logarithmic function?
Jon Peltier says
Nicolas –
You can do this for any function. You could try the LOGEST function in place of LINEST, or you could transform your data first, then use LINEST on the transformed data. I’ve read about issues with the calculation of sums of squares and errors with transformed data, but I’m not sure where to suggest you find further information.
R L James says
Thought you might like to know about this “Header Disappearing Scenario “ although not strictly a Printing problem:
1. An excel spreadsheet with the data (having 5 series) in sheet 1 was expressed
as a Chart in its sheet 2.
1.1. It had been created in Office 2000.
1.2. This had no text etc in header or footer .
2. This Workbook was transferred into Excel Office 2010 and opened in the “Compatibility Mode”
2.1. The Chart in Sheet 2 was adjusted to fit a Lansdscape A4 print configuration.
2.2. The “Print Titles” corner arrow in the Page Layout – Page setup group was selected revealing the Header and Footer Tab
2.3. A Title and Info in all three sections of the Custom Header was entered.
3. Print View was chosen and all the information appeared and printed out successfully.
4. On performing any moving or manipulation of legends etc or saving the document , the Print View failed to show the Header information – nor did it appear in the printed document.
5. On Sliding the chart into the page area below it, the header data re-appeared in the Print View.
6. This remained so when the Chart was put back in position and all printed satisfactorily.
7. However after saving and closing, on re-opening the Header had disappeared again.
8. I manipulated the chart position as at points 5 and 6 above and the Header re-appeared etc.
9. The only way I found to solve this was to take the workbook back to Excel 2000 and enter the Header details there and saved it.
10. On re-opening in Excel 2010 the Header appeared and remained!
11. Is there a better solution?
12. I did not try converting to *.xlsx to see if this avoided the problem as I needed to keep it operating in *.xls -perhaps I should by using the converter facility available for earlier versions of excel.
Jon Peltier says
RL –
“Compatibility mode” isn’t really as compatible as the name implies. Formulas are generally okay; formatting usually has issues; shapes, charts, and worksheet controls usually behave strangely; and structural workbook things like headers and footers are a crap shoot.
I’m not sure using the converters in old versions of Excel helps that much, but you might try it on a copy of your file.
Roger Trewenack says
Thanks for the help.
Fixed it in a minute!
wu says
Hi Jon,
Thanks for your nice tricks.
I have another question like this:
If I have to first find the max and min of a series and then name them with the corresponding X value and Y value, could you help me to implement that?
Thanks,
Wu
Ruth says
does anyone know how to link the axis scale to cells? TIA
Barry says
Excel 2007. I am using custom data labels in XY plots. When I insert a row in my XY data, the XY plot will automatically update if I add new data in the inserted rows, but the custom labels associated with the points below the inserted line are no longer correct. Is this a glitch in Excel, or am I doing something wrong?
Thanks.
Barry
Marc says
Hi, I tried to apply the same VBA code in Power Point but I get an error message “User-defined type not defined” what is the version of this VBA code for Powerpoint if I want to use the same Macro with shapes in Powerpoint charts? Thanks,
Marc.
Jon Peltier says
Marc –
This code is for Excel charts. I always make my charts in Excel, then if necessary I paste them into PowerPoint. I haven’t tried programming a PowerPoint chart in years (and in fact, for a while PowerPoint 2007 didn’t even let you program its charts).
Daniel M. says
Jon,
Thanks for posting this. Very useful to finally know some of these.
My challenge:
I’m dealing with a graph matrix that has some 2000 data points that can be filtered by the certain search criteria. I know that I can link each data label to a certain cell, but is there a was to link ALL of them to their respective point?
For Example
X Axis Y Axis Title
Cell 1 0.5 0.2 Accountant
Cell 2 0.3 0.9 Associate
Cell 3 0.1 0.4 Director
Etc.
How can I link Accountant, Associate, Director, etc. to their respective points? Kind of like a VLookUp?
Thanks in advance!
Daniel
Jon Peltier says
Basically, you need to link each data label to the corresponding cell. You can do a handful manually, but 2000 would be tiresome. Instead, you can use Rob Bovey’s Chart Labeler, a free add-in you can download from http://appspro.com.
The problem is if you filter the range, you mess up the linkage. Say you have three points. The data label for the first point links to Sheet1!$C$1, for the second Sheet1!$C$2, and for the third Sheet1!$C$3. Now if you filter the range to hide the second row, the two remaining points link to the first and third X and Y values, but the labels still link to C1 and C2. The labels no longer link up. I’ve worked on this problem, and it takes a bunch of funky formulas and another range to make the points in the chart line up with the labels, even after filtering.
But just now I tried this in Excel 2013 to remind myself of the behavior, and discovered that Excel 2013 has learned to keep the link to the original label’s cell for each point. That’s a nice improvement.
Karen says
Thanks so much for the instructions Jon. I’ve used them to add a constant header (via text box) to a dozen graphs and then used the Chart Title link to title the graphs depending on options selected on a Pivot table – so users can pick (All) (renamed to be total) or individual business units and the graphs rename accordingly. Saved me setting up 15 of the same thing for each unit!
Dan Dobruse says
I’m trying to pin/fix an autoshape to a specific point on the x-axis of a chart. Every month I add data to the right end of the chart (which is of a fixed size). Consequently, the chart is effectively compressed along the x-axis, and the old data get shifted to the left with each update. I don’t want to have to move the autoshape manually every time, because I have a lot of charts. Right now I’m using Excel 2003 (but am supposed to be updated to Excel 2010 “any time now.”) Any thoughts?
Thanks in advance!
Jon Peltier says
You can’t attach a plain old autoshape to a particular chart coordinate. But you can plot a 1-point XY series with its point where you want the autoshape centered. Then copy the autoshape, select the point, and paste. The autoshape becomes the marker for the data point.
Jon Peltier says
If what you want is a rectangle that contains text, you could use a regular data label, and adjust its border and fill patterns as desired.
Lorna says
Hello Jon,
My graphers were happy to discover that we could point a text box from within a graph to a cell so that the contents can update automatically. They are using Excel 2010. When I pull in the graph file into my version of Excel (which is 2007), and then insert lines above the graph that has the text boxes (previously pointed), the references shift due to the insertion of the lines. I find this to be strange, as the references in the cell were supposed to be literal to where they were pointing (e.g.. $F$16), yet the references shift. Can you explain this phenomena? Does this have to do with 2010 vs. 2007?
Jon Peltier says
Any link to a cell (e.g., $F$16) will adjust the link so it keeps pointing to the same cell, despite insertion or deletion of rows or columns. If I insert a row above $F$16 and delete a column to the left of $F$16, the reference will change from $F$16 to $E$17. This has always worked in this way, and it’s a good thing.