Highlight Max & Min Values in an Excel Line Chart (Conditional Formatting in Charts)

Discover the fascinating technique of dynamically highlighting maximum and minimum values in Excel line charts. Learn how to insert a line chart in Excel and how to conditionally format the data points of the line chart to emphasize the maximum and minimum points of the line.

Key Learning Points:
- Conditional Formatting in Charts: Learn how to conditionally format the highest and lowest points in a line chart, making them stand out visually.
- Dynamic Chart Updating: Understand how to make your chart respond dynamically to changing data, ensuring the max and min points are always accurately highlighted.
- Creating Additional Series: Explore the process of adding new series to your chart specifically for highlighting these points.
- Using IF Logical Test: Gain insights into how IF logical tests can be used to identify max and min values in your data set.
- Customizing Chart Markers: Step through the customization of chart markers for max and min points, including changing colors and sizes.
- Avoiding Common Pitfalls: Learn how to avoid common issues like line charts crashing to zero and how to use the NA function effectively.
- Practical Application: See the concepts applied to real sales data, with practical examples and step-by-step guidance.
- Extra Tips: Discover additional tricks like hiding error values and grouping data to keep your charts clean and professional.

We apply the conditional formatting in a dynamic way so that whenever the source data changes, the position of the highlighted markers changes on the chart. With this method you can conditionally format the series of any chart you'd like. For example, to highlight the months that had the highest values or to highlight the data points in red if they are below target and green if above target - or to add conditional data labels to the chart series.

This chart is a good addition to corporate reports and Excel dashboards.

LINKS to related videos:

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[martarutkowska]

Leila, you are the best teacher! Great trick as usual!

[edwardkeyaazenga]

Thank God I found Leila, watching your videos is massively improving my skills as an analyst big time!! Thank you so much!

[paulozagatto]

Leila, I love your videos. You are super fantastic! For this wonderful chart, I would just add something else. In case you have more than one max or min values, you should get rid of a line that appears. So, you should select the series max, under Color select Line instead of Marker, then select No line. Do the same for series min. Thank you so much for sharing you knowledge with us. Greetings from Brazil!

[wayneedmondson]

Hi Leila.. love this video and technique of adding additional data series and using NA() to isolate just the min/max to get the desired effect. Also, your chart enhancements are so creative and for those of us with no right brain skills, the lessons are invaluable for getting more impact out of the the results than just the standard defaults. Judging from the number of thumbs up on this video already.. more than 600 in less than 24 hours since you posted.. you have a lot of fans.. myself included. Keep them coming. Thanks and thumbs up!!

[craigvoss]

Thank you for always sharing your abilities and knowledge with us.

[simdog]

Brilliant, very clean graph and taught perfectly, thanks

[abdullahiqbal]

Thank you, for everything you do... you are "The Best"...

[rinaful]

Oh wow! I feel I am the Excel-genius after watching this. 😁Thanks Leila!

[saikrishnakanthprasad]

You are simply awesome in the way you teach Excel!!!!

[antoniobacic]

Such intelligent solutions.
Such beautiful end result.
I’m loving it.

[meongcaem]

Thank you Leila... you gave another great tutorial for my self development...

[shipingli]

You always bring something useful. Thank you very much.

[chipturner]

Nice! I like using dynamic formatting wherever possible.

[alaoibukun]

Thank you Leila for this video. You really saved me a lot of stress.

[Lunatyk]

I am using exactly the same technique for forecast vs sale charts to
display if forecast was accurate (thanks to two extra columns, first
with values if forecast = +/- X% of sale - which means it's accurate,
second column with values if first column contains error).

I think that it is funny thing, that some time ago, after watching your
other video ("This Excel Chart will grab your attention") I had a little
fun and figured how to do this "trick" to conniditionally formate some
values on chart, and now you are sharing exactly what i have figured
form these video. :)

Anyway, this video is much easier to adapt than previous for most
people, so thanks for sharing it! It's much better to send link to video
and let someone to learn something by himself (with possibility to
watch video again everytime he needs it), than explaining how to do it
every time he needs to highlight somehting on his new chart.

[ayeshan]

Thanks Leila.. you are amazing.. !! Came across ur channel today was great learning .. !!

[mando]

Thank you so much, realy i use your lesons in my work and i built many programes by excel that helping me too much in my work

[ursvenky]

Beautiful lady came up with beautiful trick. 😍😍😍😍
Thanks Leila

[ckokse]

Very nice. I already have a graph in mind at work where I can implement this. Thank you!

[gauravjaiswal]

Thank you, it is really worth it to watch and learn.