Office hacks: Do the trick with the average line of the bar chart

Many bar charts are used for data comparison. Not only can each data be compared with each other, but each data can also be compared with the overall situation. For example, the average of each data and the overall situation can be represented by a bar chart with an average line (Figure 1).


First, add the auxiliary column “Average Sales” to the data area and enter the formula “=AVERAGE($B$2:$B$6)” and fill it down to the desired row. Select A1:C6 to create a “Cluster Bar Chart”. Select the series representing “Average Sales” in the chart, select the “Design” tab, click on “Change Chart Type”, and in the pop-up window place the “Average Sales” cluster bar. Average Sales” cluster bar chart to “Line Chart with Data Markers” and check its secondary coordinates. Right-click the line chart in the chart, select “Set Data Series Format”, and in the Set Data Series Format window, expand “Fill & Line→Marker”, and select “None” under the Data Marker option (Figure 2). ” (Figure 2).


Select the chart, choose the “Design” tab, click “Add chart element → Axis → Secondary horizontal axis”, double-click this horizontal axis, and in the “Set axis format” window Select the “Axis Options” tab, and choose “On Scale” for “Axis Position”; select the vertical axis, and in the “Axis Set the maximum value to 100, the minimum value to 50, and the unit to 10. Set the maximum and minimum values of the vertical axis of the secondary coordinate in the same way (Figure 3).


Select the vertical axis of the subscale, and in the Axis Options tab, in the Axis Options, expand Scale and select “None”; expand “Label” and select “None” for the label position; in “Fill and Line”, expand In “Lines”, expand “Lines” and select “No lines”. This way, the vertical axis of the secondary axis is gone. Similarly, the horizontal axis of the secondary axis is also hidden. Next, select the rightmost data point of the “Average” series in the chart, and in the “Fill and Line” tab, expand the Right-click the data point, select “Add Data Label → Add Data Label”, in the “Label Options” tab, select “Built-in”, select the type you need, size 10. In the “Tag Options” tab, expand the “Number”, select “Number” at the category, the number of decimal places for 1 (Figure 4).


Finally, just embellish the chart accordingly to suit your needs.

Leave a Comment