HOW TO CREATE A BAR CHART WITH REFERENCE LINE IN EXCEL

CREATE AWSOME CHARTS

5.Right-click the Series 2 in the chart and then, on the shortcut menu, click Change Series Chart Type.
6. In the Change Chart Type dialog box, under the Choose the chart type and axis for your data series set the Chart Type to Scatter for the Series 2 data series as per the below screenshot:

7. Press the Ok button to close the Change Chart Type dialog box. The chart should look like this:

8. Right-click the chart and then, on the shortcut menu, click Select Data.
9. In the Select Data Source dialog box, select the Series 2 data series and click on the Edit button under Legend Entries (Series).
10. In the Edit Series dialog box, refer to cell D3 in the Series name edit box. Next, edit the Series X values to include cell E3 and the Series Y values to include cell F3 as per the screen shot below:

11. Press the Ok button twice in a row to close the Edit Series and Select Data Source dialog boxes. The chart should look like this:

12. Select the "World average" series in the chart then click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Error Bars option, as per the screenshot below:

13. While the chart is selected, select the Series "World average" X Error Bars series from the drop down list in the Current Selection group under the Format tab. Press the Delete key to delete the Series "World average" X Error Bars series.
14. Similarly, select the Series "World average" Y Error Bars series from the drop down list in the Current Selection group under the Format tab.
15. In the Format Error Bars pane, with Error Bars Option icon selected, set the Direction to Plus and the End Style to No Cap. Then, set the Error Amount to Fixed Value of 1.
16. While the error bar is still selected, under the Fill & Line icon, set the Line to Solid line, its color to Black and its Width to 1.25 pt and its Dash type to Dash. The chart should look like this:

17. To extend the error bar all the way to the top of the chart, right click the secondary vertical axis and on the shortcut menu click Format Axis.
18. In the Format Axis pane, set the Maximum Bound to 1. The chart should look like this:

33. Finally, change the chart font to Trebuchet MS, its color to black and size to 9.
34. Adjust the size of the chart.
35. Change the Fill color of the bars to light blue and their Gap Width to 25%.
36. Add chart title and data source. The chart should look like this:

Lets do some cleaning.

27. Delete the vertical gridlines, the vertical secondary axis and the horizontal primary axis.
28. Turn off the border of the Chart Area by setting the Border to No line.
29. Next, in the chart, right-click the Vertical (Category) Axis and then, on the shortcut menu, click Format Axis.
30. In the Format Axis pane, with Axis Options selected, check the Categories in reverse order option under Axis position.
31. Next, under Labels, set the Interval between labels to Specify interval unit and keep the default value of 1.
32. Click on the Fill & Line icon and set the Line option to No Line. The chart should look like this:

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.

19. In the chart, right-click the World average series, and on the shortcut menu select Format Data Series.
20. In the Format Data Series pane, with Series Options selected, click on the Fill & Line icon. Under Marker, set Marker Options to Built-in its Size to 7 and Type to the up arrow.
21. Next, set Fill to Solid fill and change its Color to black and set Border to Solid line and its Color to black.
22. In the chart, right-click the World average data series and then, on the shortcut menu, click Add Data Labels.
23. In the chart, right-click the Series “World average” Data Labels and then, on the short-cut menu, click Format Data Labels.
24. In the Format Data Labels pane, under Label Options selected, set the Label Position to Below.
25. Then, under Label Contains, check the X Value option and uncheck the Y Value and Show Leader Lines options.
26. In the chart, right-click the Series “Consumption” data series and then, on the shortcut menu, click Add Data Labels. The chart should look like this: