Bar chart with 'target' lines but target varies per column

Hi,

I know how to change a data series so it can be a line graph. This is great if the target is a constant. I have a graph that has 4 columns of data (each with the same 3 series of bars). For example;

The 3 series are - Target, Actual, Planned
The 4 columns are - Quarter 1, Quarter 2, Quarter 3 and Quarter 4

The target for Q1 might be 50, Q2 100, Q3 150 and Q4 200. I want a line to only go through the Actual and Planned columns for that Quarter (i.e. not a line that goes up and down across the graph). As it is, I could draw a line however the target figure can (and knowing my luck, probably will) change from time to time.

Anyone with any ideas would be greatly appreciated.


Thanks,

Ineedcoffee

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Sounds like you want an XY Scatter for the Target series, with no line, but add X error bars so that the target line extends left and right.

Can you upload a sample of what you have and a second page with a mock up of what you want?

It would be easier with a sample but basically you change the series to XY, then add horizontal error bars (by default Excel will add both, just change the line colour for the vertical ones to No Line), then set an error value as required (probably just want positive errors and say 0.5 as a value)

You can't have it with the XY chart. What you can do is use something like the attached, which is actually a line chart but I replaced the markers with a line that I drew on the sheet and then copied and pasted onto the chart series.

Romperstomper, can you explain howyou drew then were able to insert the line? i'm trying to replicate but can't get a custom line in, and the inbuilt one for the marker options isn't wide enough to cover the width of the columns i'm marking against

I drew a line shape on the sheet, and formatted it the way I wanted. I then copied it, selected the chart series and pasted it.

Ok, i'll try that. I tried reformatting the markers, which wasn't too clever and also insert line, same. I'll try drawing, it's visually quite a nice way to express in a chart

This was an interseting challenge which I could not resist.

I had the same thoughts for a solution as those suggested,

- use xy-scatter with error bars
- use xy-scatter to draw lines

Both of these prohibit the use of a data table.

Custom marker as RS suggest seems the best approach although you lost the ability to format the series without repeating the shape, copy and paste process.

I though about using a 2 series stacked column. Lower stacked is transparent to give the illusion of a floating line. Problem was this added an extra row to the data table.

What I came up with was a line series on the secondary axis plotted as dates. It requires quite a bit of additional data and a specific data layout but it address the problem for formatting the line in the future as well as not adding series to the data table.
The first 4 data points of the series occupies the Target row in the data table. The other points define start, finish and break points.