Constant line in chart by Quarter

KEOJ

New Member
Joined
Nov 25, 2009
Messages
14
A new day=a new problem

I have a bar chart that has data by the quarter over a span of 3 years, so there are a total of 12 bars on the chart. Everything looks good there, but now I need to add the minimum amount that the customer needs to acheive (which varies every quarter) in the form of a line. So I added a line chart with the minimum data, but the problem is it goes up and down from point to point and doesn't create a visual horizontal line for the customer to see if they are hitting their minimum every quarter. Is there any way around this?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi KEOJ,

I did something similar recently - the second series should be the target or benchmark, correct? What I did is that I plotted the second series as X error bars on a an XY Scatter chart. You have to experiment a bit to get the length, color and thickness to look good.
 
Upvote 0
Thank you Dan Waterloo! I created a seperate chart and using the XY scatter with the error bars seems to be exactly what I need. I seem to have a problem adding the series to the chart because I have too much data? I'll have to play with the formatting a bit.
 
Upvote 0
I don't think you need to create a separate chart. You already had the target values displayed with the line chart, right? So then right click one of the target data points and change the chart type for that series.
 
Upvote 0
I think I know what you want.

You would like a horizontal line placed within the bar right?

One way:

You have already changed the series to line chart. Now go to the format of that series, hide the line and choose a horizontal line marker.
 
Upvote 0
Jon --
Your solutions seems to be a bit more user friendly, but neither solutions have solved my problem just yet. They have gotten me closer. My problem now is getting the data in the chart without disrupting my bar chart. The solution of adding the line marker worked good, until I realized it put all my data in the same spot for each quarter.
 
Upvote 0
I'm struggling to visualise what it is that you are after.

I assume you are using a column chart because you are referring to wanting a 'horizontal' line.

Could you perhaps post a sample of the chart table so that I can see what you are dealing with?

Thanks!
 
Upvote 0
I'm struggling to visualise what it is that you are after.

I assume you are using a column chart because you are referring to wanting a 'horizontal' line.

Could you perhaps post a sample of the chart table so that I can see what you are dealing with?

Thanks!

I agree that a visual would help and I am using a column chart. I would post a sample of the chart, but I do not have attachement rights. Can I email it to you? I don't know if that proper ettiquite but this is new to me.
 
Upvote 0
Hi again

It's not the chart I'm after, rather the table that feeds the chart. Lets stick with the forum for now because this way others can contribute.

Have a look in my signature for a link on how to display your table on the board.
 
Upvote 0
KEOJ,

Did you ever have both the column graph and the line graph showing in one chart? I thought you did. If you did, then there is no need to change the layout of your data.

Jon and I were each simply suggesting ways of changing the appearance of one of the series (one of the graphs).
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top