Lunchtime Excel question for your pondering

[RacingPete]

I am pretty handy on the old Excel but stuck on one thing which I am not sure if is possible!

Does anyone know a way to get the legend of a chart to show the values of the data with the numbers next to them as well as showing in the chart.

Like this mockup I made:


I am not sure if it is possible, and I could always insert the graph next to the source data, but I wanted to have it as a standalone worksheet for the chart.

[Liszt]

Click on the plotted line, Format Data Series, Data labels tab then "label contains:" tick values

This will get values at end of lines. You cant get it in the label as far as I know unless you embed it in the data the label is built from

Edited by Liszt on Wednesday 17th March 13:14

[FunkyGibbon]

Not sure you can - but you can format the data series to show the data value as a label - if that helps.

edited to add as posted above - you could create a new column of data which concatenates the your current label with the value, and use that as the new legend.

Edited by FunkyGibbon on Wednesday 17th March 13:17

[blank]

Not sure if there's a direct way but you could change the column/row titles to be:

="NAME ("&CELLREF&")"


Where NAME is whatever name you want and the CELLREF is whatever cell the value is in (or could be a count formula or something if you wanted).

[RacingPete]

But with all the options above I can only create graphs like this:



So this doesn't show on the Y axis all the data values and have them separated out like in my original mockup.

or like this (by swapping the axis):



But this then shows whatever the one thing I call the label and not for each element of the bar.

Think that might explain the problem much clearer.

So I need both the legend saying the values (which I can get by concatenating) and the Y axis labels too.

[FunkyGibbon]

RacingPete said:

Would this format with each series formatted to show "date Label -> Category Name" do?

The labels would not be on the y-axis but close to the bars.

[RacingPete]

FunkyGibbon said:

RacingPete said:

Would this format with each series formatted to show "date Label -> Category Name" do?

The labels would not be on the y-axis but close to the bars.

It is an option, but not the ideal. The only reason I wanted this is if you use Google Spreadsheet Surveys the report does this automatically and I wanted to replicate it.

Seems it isn't possible, but thank you everyone for trying.

[LordGrover]

Upside down, d'uh.
Is cheating out of the question?

[FunkyGibbon]

LordGrover said:

uses excel 2007

ah ah - those positioning options not present in 2003.

I'd upgrade but I can't get round the ribbon interface - may be I need to bite the bullet.

[RacingPete]

FunkyGibbon said:

LordGrover said:

uses excel 2007

ah ah - those positioning options not present in 2003.

I'd upgrade but I can't get round the ribbon interface - may be I need to bite the bullet.

I have recently taken the bullet and spend many a day looking around the options trying to find all the stuff I knew very well. (Especially today when I needed to change the Macro settings)

FunkyGibbon - I think that is as close as we can get and looks ok once I have done it. Thank you.