How To Highlight Highest And Lowest Value In Excel Chart

Since you’ve stumbled across this page, I don’t need to convince you of the benefits of formatting a chart properly. A picture says a thousand words!

Here’s the quick step-by-step guide and step-by-step example further below.

Quick Steps To Highlight The High And Low Points On A Chart

  1. Add a max and min column to your data. Use the MAX and MIN functions. For example, =IF([@[Market Cap]]=MAX([Market Cap]),[@[Market Cap]],NA())
  2. Add the max and min columns to the chart
  3. Select a series on the chart
  4. Right-click, Format Data Series or CTRL+1 shortcut
  5. Under “Series Option”, change the “Series Overlap” to 100%

Write Formulas To Calculate The High And Low Points

Let’s start with the example below. We have some data and created a simple column chart.

Basic chart example

We need to add a formula that determines if the particular row is the maximum or minimum value. Here are the two formulas for this example:

  • Maximum value – =IF([@[Market Cap]]=MAX([Market Cap]),[@[Market Cap]],NA())
  • Minimum value – =IF([@[Market Cap]]=MIN([Market Cap]),[@[Market Cap]],NA())
Max Min Formula

Next, we add the Maximum and Minimum columns to our chart.

Add data to chart

Pro tip – Another quick way to add data to your chart is by highlighting the column of data, copying, click on the chart, and pasting.

Format The Chart For Highest And Lowest Points

We’ve now got a chart that looks like this. But this doesn’t look right! We want the orange and grey bars stacked on top so it highlights the max and min values.

Separated Chart

Select the chart series and right-click, Format Data Series (or press CTRL+1). Under the “Series Option”, move the “Series Overlap” to 100%.

Final chart

And that’s it!

Instead of showing high and low points, you can also adjust it to highlight bars based on criteria like certain thresholds or user selection.

You can also apply this methodology to other chart types like bar charts or line charts. For a line chart, you’ll need to add a “Marker” under the Format Data Series to highlight the min/max points. Play around and have fun with it!

Video Guide

Thanks for reading and I hope you found this helpful!

Other Excel tutorials you may also like:

Leave a Comment