CODEWITHSUNDEEP
excelvbagraph
hungbm06
hungbm06

Reputation: 1549

How to draw a "Line with Markers" graph like this?

Please view this image (focus to red around area):

enter image description here

Upvotes: 0

Views: 9051

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6073

Here's an easier way.

Set up the data like this:

Data for chart with letter axis labels

Select the first two columns (blue) and insert a column chart (zero value columns do not appear): Column Chart

Select and copy third and fourth columns (red), select the chart, Paste Special as new series, check first column and first row boxes (it's added as another column): Column chart with XY data as another column

Right click new column series, choose Change Series Chart Type, and select the XY with Lines style: Column and XY Chart

Right click XY series, choose Format Data Series, change to Primary Axis: Finished XY chart with letter X axis labels

Upvotes: 1

mousio
mousio

Reputation: 10347

I cannot get it to work with the letters, but I can get it to work with numbers (=CODE(A1)-CODE("a")+1):

Scatter

UPDATE after reading Jean-François's answer (but on another computer):

  • Added a second series just for the custom labels
  • Show data labels for the new series
  • Explicitly set the Y axis minimum at 0
  • Deleted the original X axis labels
  • Deleted the legend entry of the second series

Scatter2

A few more steps and we're there:

  • multiplied the X axis values by 10, to adjust granularity for positioning stuff
  • subtracted 5 for series1 values, to get the data in the middle between major tick marks
  • played with series2 values, to get the labels also in the middle between major tick marks

Scatter3

Upvotes: 4

Related Questions