How to create bar chart with data points in Excel?

1
votes

I want to create this style of chart in Excel.

enter image description here

It is basically a bar chart with has categories on the y-axis, a single dimension on the x-axis, and two points per category showing change over time. I have the data I need, however I do not know

  1. How to create the data points on a bar chart in Excel; or

  2. The easiest way to create a similar chart.

Thanks for your help.

1
excelgraphcharts

1 Answers

1
votes

consider the following screenshot:

enter image description here

  • create an XY scatter chart
  • Add a series with Y Value for Y and StartValue for X
  • Add a series with Y Value for Y and End Point for X
  • Add horizontal error bars set to minus, no cap, custom value and select the Difference values from column E
  • format the error bars to be thick and grey
  • hide the Y axis labels (set to "none")
  • add a helper series with Y value for Y and Y Labels from column G for X
  • add data labels to this series and use the text from column A. With Excel 2013 and later you can use "Value from cells". With earlier versions you need to edit each label and point it to the cell with the text.
  • format the Y labels series to have no marker. The marker is still visible in the screenshot, so you can see where it needs to sit.
  • not in the screenshot: Add data labels to the End point series and use the values from column F.
  • not in the screenshot: format the markers for Start Value and End Point to be a bit bigger and the desired color.