Charts and Dashboards: Learning our Lines – Part 3

Welcome back to our Charts and Dashboards blog series.  This week, we offer a tip to enhance a chart that shows the deviation from the average.

We have some simple sales data:

In Part 1, we created a Line chart to view the trend of the Total by Month.  We looked at how to remove the gap between the end of the line and the ends of the horizontal axis:

Last time, we created this chart:

This time, we’ll look at how we can create a variation of this chart to highlight the negative and positive deviation:

We start with the data again, but this time, in addition to the columns we added last week, we create an extra column:

Average2 is a duplicate of Average.  We have arranged them in this order to make them easier to select for our chart, as we will show later.

We then select the Month column, and the four [4] new columns, and choose a Line chart, as we did last week:

We can select the chart and change the chart type by using the option on the ‘Chart Design’ tab:

We change to a Combo chart:

We then change Average to a Line, and Max, Average2 and Min to Area charts.  This means that we have a Line chart and an Area chart for the average values.

Next, we select the Max data series and double-click to access the ‘Format Data Series’ pane:

We change the Fill to ‘Solid fill’ and choose a green colour:

We can then use the dropdown next to ‘Series Options’ to select each of the other series.

We change each Area series to ‘Solid Fill’ and change the colour of Average2 to red, and set the Transparency to 70%.   This is how we will highlight the negative deviation.  We change the colour of Min to white:

Note that we needed to select the data in the correct order to get the correct overlapping here.  We change Average to a black solid line and reduce the Width:

We also apply the fix we looked at in Part 1 to make sure our data reaches the edges of our chart by formatting the horizontal axis so that the ‘Axis position’ is ‘On tick marks’:

We can then delete the legend and change the title:

Our chart is complete, and we should probably check what is happening in March and November!

That’s it for this week, come back next week for more Charts and Dashboards tips.