Graphical co-ordinates format data label (with brackets) for chart

[Neil Goldwasser]

Hi all!

Using Excel 2007, I have made a chart using a data series with the x-values
in one column and the y-values in another. I wish to show the data label for
these points, but want to include the brackets to replicate the mathematical
way of expressing the co-ordinate i.e. that the data label would read (-8, 6)
instead of -8, 6. I've tried creating a custom data label using format codes,
but I'm getting nowhere fast. Can anyone help please?

Also, I have a second series on the same graph that I do NOT want to display
this information (I'm making a teaching resource). It does not have a data
label, but if you hover the mouse over it, it does display the coordinate.
Can I turn this off?

Many thanks in advance, Neil

 

[Andy Pope]

Hi,

To turn off the tooltips select the chart and use,
Office button > Excel Options > Advanced > Display > Show data point values
on hover.

You can create the brackets using a couple of approaches.
First use a helper column to build the text for your data. So if your x and
y values are in A2 and B2 then in C2 use
="(" & A2 & "," & B2 & ")"

You can then use this free addin to link data labels to cells.
http://www.appspro.com/Utilities/ChartLabeler.htm

Another way is to use custom number format.
For your X values use
(General

For you Y values use
General)

You can now use data labels displaying both x and y value separated by
comma. You will need to format both the x and y axes to have General number
format in order to remove the brackets from the axis labels.

Cheers
Andy

 

[Neil Goldwasser]

Thanks for your reply Andy.

Just a few questions...
(1) If I turn off the tooltips in the way that you suggest, is that option
attached to the file or the computer running it? (I plan to share this
resource with my students, so I'd rather that setting just happens for this
one file, rather than them having to alter settings that will affect EVERY
Excel file that they open, as it is a useful feature when it isn't allowing
them to cheat on this one particular file!)

(2) Will my students also need the add-in installed for the data labels to
come up the same on their computer?

(3) I tried altering the number format of the input values, but it does not
handle negative numbers properly, e.g. if the x-value is -8 and the y-value
is 6, it displays as -(8, 6)

Any ideas?

Again, many thanks for your help, Neil

 

[Andy Pope]

Hi,

(1) It's an application level setting, so every file.
(2) Only the person building the chart will require the addin. The addin
autos this technique.
http://www.andypope.info/tips/tip001.htm

(3) Try expanding the X values number format to
(General;(-General;(General

Cheers
Andy

 

[Neil Goldwasser]

Hi again!

Thank you very much - all sorted now.

(1) To get around the tooltips issue without the students having to change
their settings, I protected the chart and the worksheet, making sure that the
user cannot edit objects. My students can't cheat now!

(2) In the end I went with creating the custom data label format and
checking "linked to source" on the options for the data labels - works a
treat now that negative numbers are handled correctly.

Again, many thanks for your help, it is much appreciated.
Neil