# Showcase high and low values in Excel charts – Part 4

## Introduction

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.

## Background fill

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

`=IF(B2=MIN(Value),1,0)`

The formula for the maximum values is

`=IF(B2=MAX(Value),1,0)`

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: 