CODEWITHSUNDEEP
excelplotgraphscatter-plotscatter
user9990184
user9990184

Reputation: 133

Need to add dotted lines in scatteplot Excel

I created the below graph doing a scatteplot in excel then inserted the grey dotted lines by designing them one by one. I was wondering if this could be done automatically by inserting the data serie for the dotted lines into the chart.enter image description here

Upvotes: 0

Views: 471

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Yes you can do something close to it, by combining a Scatter Plot chart and a Stacked Column chart. So result will be something like this:

enter image description here

Step by Step guide to create the chart.

  1. The data looks like this:

enter image description here

  1. Mark the first part of the data, where the column "Data Bar" will be the difference between your two scatter plot values. It will represent the "area" between your scatter values. Choose to plot a "Stacked Column" Chart.

enter image description here

  1. Hide the lowest part of your "Stacked Column" by click on the "blue part" of the bar which I named "Hidden Bar". Go to "Format Data Series" and choose "No Fill".

enter image description here

  1. Add the part that will be your data for the "Scatter Plot". For me it was the column LowerBoundBar and UpperBundBar.

enter image description here

  1. Your graph should now have four layers of Stacked bar. Go and change the Chart type by clicking "Change Chart Type".

enter image description here

  1. Here is where the magic happens. Go down to Combo. Choose the two first to still be "Stacked Column". The last two series should be "Scatter". Your options should look like the picture below.

enter image description here

  1. Time to modify your chart so it looks a bit nicer. Mark the clustered bars that represent "Data Bar" values. Go to "Series Option". Maximize "Gap Width". It will make your clustered bars thinner.

enter image description here

  1. Time to get rid of the orange color. Go to "Fill & Line" option and choose "Pattern fill". Use the horizontally dotted line (marked with yellow). Then go down to Border options (lower in the picture). Choose to use white border and make them a bit thicker by increasing "Width".

Finally you might need to resize the whole chart (the outer circles in the big chart in picture below) so the lines shows up better. If the whole graph is to small it will only be white. If it's to big it will show two parallel dotted lines.

enter image description here

Upvotes: 1

Related Questions