Leila Gharani - Excel Max Min Chart Template

Download Leila’s Max/Min Chart Template

Get the exact workbook from the video. Plug in your data and the Max/Min markers highlight automatically.

Step 1: Start With a Basic Line Chart

Let’s begin by creating a simple line chart using your data.

Excel dataset with monthly sales values used as the source data for the max and min line chart example
  1. Click anywhere in your dataset.
  2. Go to the Insert tab.
  3. In the Charts group, select Insert Line or Area Chart → Line with Markers (top row, second from the right).
Excel Insert tab showing Line with Markers chart option selected in the Charts group

What Happens If You Try to Color the Max Value Manually?

If you want to highlight the highest value manually, here’s what you’d do:

  • Click once on the line to select the entire series.
  • Click a second time on the data point with the highest value to select just that point.
  • Right-click the selected data point → choose Format Data Point.
  • In the right-hand panel, go to Fill & Line (paint bucket icon).
  • Click on Marker, then expand the Fill section.
  • Choose a new fill color (e.g. green) for the marker.
Excel Format Data Point panel showing marker fill color set to green for the max value

The problem?
If your data changes and the highest value moves to a different point, Excel won’t update the color automatically. You’d need to:

  • Remove the color from the old point
  • Add it to the new one
Excel line chart showing manually colored max point that won't update when data changes

Step 2: Automatically Highlight Max and Min Points

To make our line chart highlight the highest and lowest values dynamically, we’ll use two helper columns. These will tell Excel which points to color—and update automatically when the data changes.

Create the Helper Columns

We’ll add two new columns to your dataset: one for the MAX value, and one for the MIN value.

  1. Next to your original data, add two new headers: MAX and MIN.
Excel dataset with MAX and MIN helper column headers added next to the original sales data
  1. In the MAX column (e.g., cell C5), enter this formula:
=IF(B5=MAX($B$5:$B$16), B5, ””)
IF formula in Excel returning the max value or blank for the MAX helper column
  1. In the MIN column (e.g., cell D5), enter this formula:
=IF(B5=MIN($B$5:$B$16), B5, ””)
  1. Fill both formulas down for all rows.
MAX and MIN helper columns filled down for all rows in the Excel dataset

Add the MAX and MIN Series to Your Line Chart

Now, let’s add these helper series to the chart.

Method 1: Drag to Expand Data Range

  1. Click the edge of your chart to select it.
  2. You’ll see a blue border around the data range.
  3. Drag the bottom-right handle to include the MAX and MIN columns.
Blue data range border on Excel chart being dragged to include MAX and MIN helper columns

Method 2: Copy & Paste (Alternative)

  1. Select the helper data columns, including the headers.
  2. Press Ctrl + C to copy.
  3. Click the edge of the chart.
  4. Press Ctrl + V to paste the series into the chart.

Ignore Zero Values in Excel Chart

Blank cells are plotted as zero on the chart. We will use the NA() function which tells Excel to skip those points completely—no line crashing to the bottom.

Excel line chart showing helper series crashing to zero because blank cells are plotted
  1. Select the IF formula for January’s MAX and update it as follows:
=IF(B5=MAX($B$5:$B$16), B5, NA())
IF formula updated with NA() to prevent blank MAX values from plotting as zero in Excel chart
  1. For the MIN cell of January, update the formula as follows:
=IF(B5=MIN($B$5:$B$16),B5,NA())
IF formula updated with NA() for the MIN helper column to skip empty cells in the chart
  1. Fill both formulas down for all rows.
Excel line chart after NA() fix showing only the max and min data points as additional markers

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

Format the Highlight Points

Let’s style the helper series to make them stand out.

  1. Click once on the MAX series (e.g., orange point).
    • ❗ Important: Clicking once selects the full series. Clicking twice selects only one point—don’t do that here.
Excel line chart with MAX series selected showing one highlighted data point marker
  1. Press Ctrl + 1 to open the Format Data Series panel.
    • If you are uncertain if you have the correct series selected, use the Series Options dropdown list to select the proper data series.
Format Data Series panel in Excel with Series Options dropdown for selecting the correct series
  1. In the panel, choose the Fill & Line icon (paint bucket).
  2. Expand the Marker section:
    • Set marker type to Built-in → Circle
    • Set size to 10
Format Data Series marker options set to built-in circle with size 10 in Excel
  1. Under Fill, choose No Fill
Format Data Series marker fill set to No Fill for the MAX highlight point in Excel
  1. Under Border, choose Solid Line
    • Color: Green
    • Width: 2.25 pt
Format Data Series border set to solid green line at 2.25pt for the MAX marker in Excel
  1. Repeat for the MIN series:
    • Same marker and size
    • Border color: Red

Clean Up the Original Line

Let’s simplify the main data series so the highlights pop:

  1. Select the Sales series (the original line).
  2. In the Format panel:
    • Set marker fill to dark gray
    • Set marker border to none
    • Set line color to dark gray
Excel line chart with main Sales series styled in dark gray with no border on markers

You can also:

  • Make gridlines light gray to soften the background.

Add Data Labels

Want to show the values directly above/below the max and min points?

For the MAX point:

  1. Click the MAX series once.
  2. Right-click → Add Data Labels
  3. Right-click label → Format Data Labels
    • Under Label Options, set Label Position to Above
Format Data Labels panel showing label position set to Above for the MAX point in Excel

For the MIN point:

  1. Click the MIN series once.
  2. Right-click → Add Data Labels
  3. Under Label Options, set Label Position to Below
Format Data Labels panel showing label position set to Below for the MIN point in Excel

Test It

Try changing a value in your data—for example:

  • Set May to 300
  • Set November to 100

The highlights will automatically move. No manual updates needed.

Excel line chart with max and min highlights automatically moving after data values are changed

Want to Hide the Helper Columns?

You can hide or group the MAX and MIN columns, but by default Excel won’t plot hidden data.

Excel line chart with MAX and MIN helper columns hidden, causing highlight markers to disappear

Fix that with these steps:

  1. Right-click on the chart → Select Data
  2. Click Hidden and Empty Cells (bottom left)
  3. Check: Show data in hidden rows and columns
Hidden and Empty Cells dialog box with "Show data in hidden rows and columns" option checked

If You Have Duplicate Max or Min Values

You may see a connector line between the duplicate points.

Excel line chart showing unwanted connector line between two duplicate max or min values

Option 1: Prevent line for hidden columns

  • Go to Select Data → Hidden and Empty Cells
  • Check: Show #N/A as an empty cell
Hidden and Empty Cells dialog with "Show #N/A as an empty cell" option checked in Excel

Option 2: Remove line manually

  • Click the series line
  • Open Format panel → Fill & Line → Line
  • Select: No Line
Format Data Series Fill & Line panel with No Line selected to remove connector between markers

The result should appear as follows.

Excel line chart with duplicate max values displayed cleanly without a connector line

Get Creative

This trick doesn’t just work for line charts.

You can use it to:

  • Highlight the top bar in a column chart
Excel column chart with the highest bar automatically highlighted using a helper series
  • Mark the top 3 values in a time series
Excel time series line chart with the top 3 values marked using helper series and colored markers

Download the Free Chat Template

Get the Max/Min Highlight Template

Automate your “Conditional Formatting” in charts. Grab the finished template and plug in your numbers.

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.
Learn More
Excel Black Belt Bundle XelPlus

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.