HOW TO CREATE A BAR CHART WITH HORIZONTAL REFERENCE LINE/AREA IN EXCEL

CREATE AWSOME CHARTS

2. While the chart is selected, click the Change Chart Type icon in the Type group under the Chart Design tab.
3. In the Change Chart Type dialog box, select Combo under the All Charts tab.
4. Next, under the Choose the chart type and axis for your data series set the Chart Type to Line for the Capacity data series and Area for the Shading data series as per the below screenshot:

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

6. In order to extend the capacity line and shading beyond the data bars of the orders we need to expand the data source. To do that right-click row number 2 on the worksheet and then, on the shortcut menu, click Insert to add one single empty row above Jan.
7. Next add the value 24.0 in cells C2, D2, C12 and D12.
8. Then, select the chart. The currently displayed source data is selected on the worksheet, showing sizing handles. On the worksheet, drag the sizing handles to include the new data in range A1:D12 as per the screenshot below:

9. Lets do some cleaning. Delete the chart title, the legend, the vertical scale and horizontal gridline.
10. Turn off the border of the Chart Area by setting its Shape Outline to No Outline.
11. Change the chart Font to Trebuchet MS, its color to Black and size to 9. The chart should look like this:

12. Lets do some formatting. Select the "Shading" series in the chart and then set its Shape Fill, under the Format tab, to light grey.
13. Select the "Capacity" series in the chart and then set its Shape Outline, under the Format tab, to black.
14. Next, select the horizontal axis and set its Shape Outline, under the Format tab, to black
15. Select the "Orders" series in the chart and then set its Shape Fill, under the Format tab, to light orange. Next, change the Shape Fill of the last 3 data points (Jul, Aug and Sep) to a darker shade of orange.
16. In the chart, right-click the "Orders" series and, on the shortcut menu, select Format Data Series. In the Format Data Series pane, under Series Options, set the Gap Width to 35%.The chart should look like this:

17. In the chart, right-click the "Orders" series and, on the shortcut menu, select Add Data Labels.
18. Next, select the labels and, in the Format Data Labels pane, under Label Options, set the Label Position to Inside End.
19. Then set, individually for the month of Aug and Jul, the Label Position to Outside End.
20. Select the labels of the "Orders" data series and set their font to Bold. The chart should look like this:

21. In the chart, select the last data point (from the right) of the series "Capacity". Right-click the data point and then, on the shortcut menu, click Add Data Labels.
22. In the chart, select the label (by clicking it twice) of the data point added in step 12 and then, right-click the label and then, on the short-cut menu, click Format Data Labels.
23. In the Format Data Labels pane, under Label Options selected, check the Series Name option under Label Contains.
24. While the label is still selected, set its font to Bold.
25. To fix the problem of the label overlapping the capacity line, adjust the size of the plot area in a way to clear space on the right for the label to fit in as per the below screenshot:

26. Finally, add a chart title, data source and annotations.
27. Adjust the size of the chart and 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.