Line graph going across bar graph when only one bar

superskid

Board Regular
Joined
Aug 25, 2006
Messages
159
I understand how to make a line graph go across a bar graph, but I need the line to go across the whole bar, instead of center point to center point. The reason is on one of my charts I only have one bar so you can't see the line.

It works here:

EXCEL_23TRlr7T7k.png


But not here because the line is only showing as a tiny dot in the center of the one bar.

EXCEL_L9Jd3QSTbz.png

I would hand draw one in but the line needs to be dynamic as the data changes. Please help me have it show.

Thanks,
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,878
Hi

In that case you can have a line graph go across all the bars.

I don't know how much you need so I'll say how I'd do it.

To draw a line across all the bars I'd use a scatter chart line. It allows me to specify the start and end of the line.

To get the extremities of the segment I need 3 things. The length of the X axis in x units, the gap width and the number of points.
I can get the gap width from the series format dialog box (you probably already know the value as it's the type of thing you change when formatting the chart).
The calculation of the number of points depends on the data layout but it's usually just a count of values in a column or a row
The length of the X axis in x units I can force it. I can make the horizontal axes independent and force the limits of the secondary axis. I'd force the limits ex. to the range 0-1 and that solves the problem.
With these values I can get directly the x values of the segment extremities, the left one xL=gap width/(gap width+1)/n points/2 and the right one xR=1-xL

This will be dynamic if your formula for the number of points in the series updates automatically. If you add or subtract points to the series the line will adjust.

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

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