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
- 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())
- Add the max and min columns to the chart
- Select a series on the chart
- Right-click, Format Data Series or CTRL+1 shortcut
- 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.
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())
Next, we add the Maximum and Minimum columns to our 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.
Select the chart series and right-click, Format Data Series (or press CTRL+1). Under the “Series Option”, move the “Series Overlap” to 100%.
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!
Thanks for reading and I hope you found this helpful!
Other Excel tutorials you may also like: