We help IT Professionals succeed at work.

Excel 2010 to create a formula annotation on a chart

mcthomas00
mcthomas00 asked
on
759 Views
Last Modified: 2014-08-15
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
Comment
Watch Question

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
See attached sample.  if the value change, I want it to be dynamic and change
test.jpg
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Awesome - Thank you!
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
You're welcome.  Lots of cool ways to trick Excel charts into behaving. :-)

-Glenn

Author

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
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.

Author

Commented:
I added the series but only the name shows.  See previous attachment:  Copy-of-EE-Dynamic...
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
The number 8 is a value in its own cell.  If someone change the value then the chart updates according to the cell
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.