Very often the question in business is to identify minimum and maximum values. For example, if you are the manager of a hotel you'd like to know the seasonality of demand across the year and when hotel occupancies show peaking of demand and when they bottom out as per the chart below.

4. Highlight the range A1:C13 and then, on the Insert tab, in the Charts group, click Insert Column or Bar Chart > Clustered Column. The chart should look like this:

First, we need to prepare the setup for the Min/Max values. For that we need to create a new series where its values are the maximum and Minimum values, and #N/A otherwise.

1. Enter the following labels "Min/Max", "Min" and "Max" in cells C1, E1 and E2 respectively.
2. Enter the following formulas in the respective cells:
F1 -->  = MIN(B2:B13)
F2 -->  = MAX(B2:B13)
C2 -->  = IF((B2 >= $F$2) + (B2 <= $F$1),B2,NA())
3. Select cell C2 then drag the handle to copy the formula into cells C3:C13.

The worksheet should look like this:

5. In the chart Right-click the Max/Min data series and, on the shortcut menu, click Format Data Series.
6. In the Format Data Series pane, with Series Options selected, set the Series Overlap to 100% and the Series Width to 50%.
The chart should look like this:

Lets do some cleaning.

8. Delete the Legend and the Chart Title.
9. Turn off the border of the Chart Area by setting its Shape Outline to No Outline.
10. Next, select the horizontal axis and set its Shape Outline, under the Format tab, to Black and its Weight to 1.5 pt..
11. Change the chart font to Trebuchet MS, its color to black and size to 9.
12. Change the fill color of the OCC (%) data series to light grey and that of the Min/Max to light orange.
13. Set the Font of the labels of the Min/Max data series to Bold and its color to orange.
14. Set the line type of the Vertical Axis Major Gridlines to Dashes.
15. Set the Major Units of the Vertical Axis to 0.1.
The chart should look like this:

Let’s build this chart in Excel.

The dataset for the graphic above is in the range A2:B13 as shown in the screenshot below.

Views > Excel charts > How to highlight min and max values in a bar chart in Excel

CREATE AWSOME CHARTS

7.In the chart Right-Click the Max/Min data series and, on the shortcut menu, click Add Data Labels. The chart should look like this:

16. Finally add the chart title and data source, adjust the size of the chart and the chart should look like this:

You’ll notice that the chart is dynamic and can adjust automatically. Try it. Replace Feb OCC value with a higher value such as 56% and Dec Occ value with a lower value such as 10%. As expected, the chart will adjust and will look like this:

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.