Creating the Chart
Now that we have our data updated with the average column, we can create the chart. Select the cells you want to be included.
Under the Insert menu tab, in the Charts section, click the Column button and choose a basic vertical column chart.
At first glance, our bar graph isn’t off to a good start.
The chart has too many columns of data to display clearly. Let’s narrow it down to just the Total and Average columns. With the chart selected, click one of the left corners of the blue selection box (blue represents the data points), and drag it to the right until just the last two columns are selected.
Now the chart is looking a bit better, but we still need to change those averages to a line.
Right-click one of the red bars in the Average data series on the chart. In the menu that appears, click Change Series Chart Type…
Then choose a basic Line chart from the Change Chart Type dialog and click OK.
We’re basically where we want to be! I made a few extra cosmetic adjustments…
I deleted the Total line from the legend by clicking until just “Total” was selecting and pressing Delete on the keyboard.
I moved the bars closer together in the chart by double-clicking one of the blue Total bars and setting the Gap Width to 0% in the Format Data Series dialog.
I set the y-axis to focus on the difference between the bars rather than showing from zero to the maximum value. This is possible by changing the Axis Options once you double-click the y-axis.
Finally, I made the Average line a dashed line by double-clicking on the solid red line and choosing Line Style from the Format Data Series dialog.
That’s it! You can use this technique to add any guide line to a bar-type chart, whether you want an average, a maximum and minimum bound, or last period’s figure.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- Save a Broken Chart with Switch Columns/Rows in Chart Tools
- Build a Better, Cleaner, More Professional Line Chart
- How To Add an Average Value Line to a Bar Chart
- How to Add a Vertical Line to a Horizontal Bar Chart
- How to Add Totals to Stacked Charts for Readability
- How to Show Percentages in Stacked Bar and Column Charts
- Building Charts with Multiple Series and Custom X-Axis
- How to Create Waterfall Charts in Excel