In the last part of this mini-series I’m showing how to draw attention to high and low values in Excel charts by using background fill. The first part of this mini-series showed how to present minimum and maximum values of a chart by simply sorting the data values in ascending or descending order. Part 2 looked at conditional formatting the columns themselves if they were the highest or lowest value in the chart. In Part 3, an image was used as a background fill on a stacked series.
So let’s look at how to use a background fill for the highest and lowest data points. This can be achieved by plotting a column chart on the secondary axis. First, we need to prepare the data.
The minimum values are identified with
The formula for the maximum values is
The prepared data table looks like this:
Plot all three data columns as a clustered column chart. Then select the value series and send it to the secondary axis. Format the “min” and “max” series to have 100% overlap and a very small gap, like 10%.
Format the primary Y axis to a maximum of 1.
The tricky thing with this variant is to have the secondary Y axis appear on the left. To do this, follow these steps:
- On the Layout tab, select the Axes drop-down and show the secondary X axis. Now all the columns will be hanging upside down.
- Format the secondary X axis and let the vertical axis cross at category number 1
- Select the primary Y axis and format it to have no tick marks and no labels
- Format the secondary Y axis (which is now positioned at the left of the chart), and format it to let the Horizontal axis cross at axis value 0 (zero), which will put the columns on the base line again.
- Delete the primary X axis and fine tune the chart formatting as desired.
The result will look something like this:
Take your pick
I hope you have found this mini-series useful and will be able to apply the techniques to your charts.
The attached Excel workbook illustrates all charts above on separate sheets.