Move markers of line chart/ Format legend

Question

I have a combination chart with a bar chart and 2 line graphs on secondary axis. have to exactly match the formatting in the screenshot attached ( I do not have the actual excel file. just have a screenshot). If you have a look - you can see the markers on line graphs do not align. The red line markers are skewed slightly to the left. Also, in the legend at the bottom - secondary axis legends are separated out to the bottom. I don't know how to do that either. Any help is greatly appreciated

Answer 1

Here's a sample mock-up of a chart done entirely in Excel: no vba, just standard charts

It's actually two charts placed one over the other.

• Bar chart is standard clustered column chart
• Line chart is xy chart, and is placed on top of the column chart
  • Plot area and chart area set to no fill
  • X range is a consecutive series with enough resolution to place the markers where you want them
  • Y values are only entered for the points you want to plot, other cells are empty
  • Data source, hidden and empty cells set to 'Connect data points with line'

Answer 2

i don't think you can make Excel do such things by itself. If someone is demand a pixel-perfect copy, they're going to have to take multiple source Excel graphs, and composite them manually in Photoshop.

You could have different x-values for the red and the black line graphs, that can let you position the points at whatever improper position you require.

But i don't think you have have both on one graph at the same time.

Answer 3

This tutorial shows how to get the markers to line up with the columns:

http://peltiertech.com/Excel/Charts/ClusterColAndLine.html

Excel won't give you split legends like that. I'll bet someone took a screen shot of a legend and pasted it into the chart.