Highlight Max & Min Values in an Excel Line Chart

Examining the problem

We will begin by creating a standard line chart in Excel using the below data set.

Click anywhere in the data and select Insert (tab) -> Charts (group) -> Insert Line or Area Chart (button) -> Line with Markers (top row, second from right).

Using the newly created line chart, if we were to manually change the color of the highest value on the line, we would perform the following actions:

1. Click once on the line to select series
2. Click a second time on the highest marker on the line
3. Select Chart Tools (tab) -> Shape Styles (group) -> Shape fill (button) and select the color of our choosing

Although this works, the drawback to the manual method is that if the data source changes and a new data point value is entered that is larger than our previous largest value, the chart will not automatically update.  It would be our responsibility to reset the color on the “old” largest value and recolor the “new” largest value’s data point.

We want the chart to automatically color the marker with the largest value green and the marker with smallest value red.

Unfortunately, the built-in Conditional Formatting tool in Excel cannot apply this desired look to our chart.

When you wish to dynamically alter the look of a data point in a series, you need to create a new series in the chart.  This “helper series” will be used to create imagery that will be placed in front of the appropriate data point.  This will serve as a mask and hide the original data point appearance in favor of a new appearance.

Creating the additional data series

Since we will create two different color accents, we need two additional series in our data.

Create two new series headings in the chart data labeled MAX and MIN.

In the MAX column, we want to repeat the value on the month that has the largest value in the set, but we do not wish to have anything show for all the other months.

In the MIN column, we want to repeat the value on the month that has the smallest value in the set, but we do not wish to have anything show for all the other months.

We will accomplish this with an IF logical test.

In the MAX cell for January, enter the following formula:

=IF(B5=MAX($B$5:$B$16),B5,””)

Fill the formula down for the remaining months of February through December.  Observe the results.

For the MIN cell of January, enter the following formula:

=IF(B5=MIN($B$5:$B$16),B5,””)

Fill the formula down for the remaining months of February through December.  Observe the results.

Updating the chart with the new series

To incorporate the MAX and MIN data series into the existing chart, perform the following steps:

Select the chart (the edge of the chart is the safest place to select)

Select the lower-right handle of the blue border surrounding the chart’s data and drag the handle from column B to column D.

BONUS METHOD:

If you wish to incorporate new data into an existing chart, the following method works as well.

1. Highlight the series names and the new data (if the data is separated by existing data, you’ll need to perform a CTRL select to highlight the separate sections of data)
2. Click COPY
3. Click in the corner of the chart
4. Click PASTE

A very odd thing will occur in the chart.  Notice that wherever we have a blank cell in the MAX/MIN columns, we are plotting a 0 (zero) on the line chart.  This is because Excel interprets an empty cell as zero.

Our goal is to only plot the two data points where a value other than zero occurs.

Updating the crashed lines in the line chart

To prevent our line from “crashing” to the bottom of the plot area whenever a zero is encountered, we will employ the assistance of the NA() function.

Select the original IF formula for January’s MAX and update it as follows:

=IF(B5=MAX($B$5:$B$16),B5,NA())

For the MIN cell of January, update the formula as follows:

=IF(B5=MIN($B$5:$B$16),B5,NA())

Fill the formulas down for the remaining months of February through December.  Observe the results.

We are replacing the zeros with a user generated error of #N/A.  Excel will not plot these errors on the line chart which yields the desired result.

All the “crashed” points have disappeared leaving us with only two additional data points.

Formatting the accent data points

Click on the orange data point that represents the largest value in the set.  IMPORTANT: Make sure you only click ONCE on the data point; not twice.  Clicking once selects ALL data points in the series.  Clicking twice selects only a single data point which defeats our purpose.

Press CTRL-1 to open the Format Data Series panel on the right.

HANDY TIP:

If you are uncertain if you have the correct series selected, use the Series Options dropdown list at the top of the Format Data Series panel to select the proper data series.

Select the Fill and Line button (paint bucket), select the Marker sub-category and expand the Marker Options section.

Select the Built-In marker type of a circle and set the Size to 10.

Expand the Fill section and set the fill to No Fill.

Expand the Border section and set the option to Solid Line with a Color of Green and a Width of 2.25pt.

Repeat the process for the MIN series using the same steps.  The only difference is to select the MIN series from the Series Options dropdown and set the Border color to Red.

Cleaning up the original line

To make the line that displays all the data points more visually appealing, perform the following steps:

1. Select the Sales series
2. Set the marker fill color to dark gray
3. Set the marker border color to none
4. Set the line color to dark gray
5. Select the gridlines and set their line color to a light gray

Adding data labels

To make the chart easier to read we will add data labels to the plot points, but only to the points of the MAX and MIN values.

For the MAX data label:

1. Select the MAX data point
2. Right-click on the MAX data point and select Add Data Labels
3. Place the data label above the MAX data point by selecting Format Data Labels (right panel) -> expand Label Options -> set the Label Position to Above

Since this will always be highest point on the line, it makes sense to display it above the data point.

For the MIN data label:

1. Select the MIN data point
2. Right-click on the MIN data point and select Add Data Labels
3. Place the data label below the MIN data point by selecting Format Data Labels (right panel) -> expand Label Options -> set the Label Position to Below

Since this will always be lowest point on the line, it makes sense to display it below the data point.

A nice touch would be to color the MAX/MIN data labels green and red respectively.

Testing the chart

If we change the Sales value in May to 300 and November to 100, the chart appears as follows.

Hiding the helper columns

If you don’t wish the user to see the MAX/MIN helper columns, you can either hade the columns or group the columns and then hide them.

Unfortunately, this has a negative effect on our chart.

By default, Excel does not chart hidden data.  Luckily, we can change a setting to adjust this behavior.

1. Right-click on the chart and click Select Data…
2. In the Select Data Source dialog box, click the Hidden and Empty Cells button (lower-left corner)
3. In the Hidden and Empty Cell Settings dialog box, place a check in the option labeled Show data in hidden rows and columns

IMPORTANT NOTE:

In the event of duplicate MAX or MIN values, a connector line will appear between the duplicate values.

To suppress this line, perform one of the two actions.

If the helper columns are hidden:

1. Right-click on the chart and click Select Data…
2. In the Select Data Source dialog box, click the Hidden and Empty Cells button (lower-left corner)
3. In the Hidden and Empty Cell Settings dialog box, place a check in the option labeled Show #N/A as an empty cell

If the helper columns are visible:

1. Right-click on the line to be hidden and select Format Data Series…
2. In the Format Data Series panel on the right side of the screen, select the Fill & Line category
3. Select the Line sub-category
4. Expand the Line section (if necessary) and select No Line

The result should appear as follows.

Thank you Jon Peltier for this tip.  Jon has an amazing website filled with great Excel charting tips and answers to common (and uncommon) charting problems.  Visit him at peltiertech.com

Get creative

This technique can also be used highlight the largest and smallest bars in a column chart…

…or, draw attention to the Top 3 sales in a data series.

You just need to get creative with the formulas for the helper columns.

Practice Workbook

Feel free to Download the Workbook HERE.

LinkedIn Twitter YouTube

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

More About Leila Join 400,000+ professionals in our courses