?
Solved

Excel 2010 to create a formula annotation on a chart

Posted on 2014-08-14
11
Medium Priority
?
563 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
0
Comment
Question by:mcthomas00
  • 6
  • 5
11 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40261752
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
 

Author Comment

by:mcthomas00
ID: 40261773
See attached sample.  if the value change, I want it to be dynamic and change
test.jpg
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40261817
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Closing Comment

by:mcthomas00
ID: 40261909
Awesome - Thank you!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40261917
You're welcome.  Lots of cool ways to trick Excel charts into behaving. :-)

-Glenn
0
 

Author Comment

by:mcthomas00
ID: 40262952
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40263077
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
 

Author Comment

by:mcthomas00
ID: 40263168
I added the series but only the name shows.  See previous attachment:  Copy-of-EE-Dynamic...
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40263216
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
 

Author Comment

by:mcthomas00
ID: 40263305
The number 8 is a value in its own cell.  If someone change the value then the chart updates according to the cell
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40263836
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question