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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 

KEOJ

New Member
Joined
Nov 25, 2009
Messages
14
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.
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

KEOJ

New Member
Joined
Nov 25, 2009
Messages
14
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.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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!
 

KEOJ

New Member
Joined
Nov 25, 2009
Messages
14
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.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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.
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,770
Members
414,017
Latest member
surajks

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
Top