Excel 2010 to create a formula annotation on a chart

Is it possible in Excel 2010 to create a formula annotation on a chart.  Example if I type =E2, I want the value on Sheet1 cell E2 to appear on the chart
mcthomas00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
You can't directly annotate a chart in this way, however, there are several roundabout methods you can use to get related information on a chart.

For example, say I have sales information by product and want to show total sales in the chart.  After plotting the original data, I'll add formula that creates a label like "Total Units: nnnn".  Then I add a new data series to the chart with that as the series label but without any supporting data.  The legend will show the new label but no extra data will plot.  With a little tweaking you can get it to look something like this:
extra total label
It's dynamic also; it will update as any data for the chart changes.

If you have a more-specific request and can provide a sample of what you need, I'm sure a better solution can be provided.

Regards,
-Glenn
0
mcthomas00Author Commented:
See attached sample.  if the value change, I want it to be dynamic and change
test.jpg
0
Glenn RayExcel VBA DeveloperCommented:
Not a problem; the method is exactly the same as in my example.  Just need to use a different formula:
="High Sales: " & TEXT(MAX(B2:B6),"#,###")

See example workbook.

-Glenn
EE-DynamicChartLabels.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

mcthomas00Author Commented:
Awesome - Thank you!
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  Lots of cool ways to trick Excel charts into behaving. :-)

-Glenn
0
mcthomas00Author Commented:
I added a cell (Label) and a value in the corresponding cell.  I added the info to my chart, but the value is not appearing
Copy-of-EE-DynamicChartLabels.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
If you want "Carol" (label) and "8" (value) to appear, you'll need to either extend the range for the existing data series down to row 9 or add a new series for her data.  Probably the first option.
0
mcthomas00Author Commented:
I added the series but only the name shows.  See previous attachment:  Copy-of-EE-Dynamic...
0
Glenn RayExcel VBA DeveloperCommented:
I did notice that.  Where is "Carol" and the related value supposed to appear on this chart?  Is it supposed to be another floating label like "High Sales"?
extra floating label
If so, you just need to create another range that duplicates the blank one for "High Sales"; just put it adjacent and have "Carol: 8" as the label in the first row.

If I'm off track, please elaborate.

-Glenn
0
mcthomas00Author Commented:
The number 8 is a value in its own cell.  If someone change the value then the chart updates according to the cell
0
Glenn RayExcel VBA DeveloperCommented:
But is "Carol" another category on the X-axis?   I'm guessing not.

Take a look at this modified file and see if that's what you're looking for.  I just added another data series with a compound lable like before.  If you change the value for "Carol" in column B, the label will change also.

-Glenn
EE-DynamicChartLabels.xlsx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.