Indzara

How to Create a Line Chart with Highlighted Min/Max Points?

Learn to create a simple line chart with highlighted maximum and minimum points, as shown here.

Line Chart with Highlighted Min/Max Points
Line chart with highlighted max/min points

This line chart shows the monthly trends of sales data with max and min points highlighted for emphasis.

To do this, let us take a sample sales data as shown below:

Line Chart with Highlighted Min/Max Points sample sales data
The sample data

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Check our dedicated video explaining these steps in detail:

The following are the steps we’d follow in creating this chart:

  1. Insert a Line Chart with Markers
  2. Add additional columns to the data
  3. Add new data to the chart
  4. Format the chart
  5. Format the max/min points

Step 01: Insert a Line chart with markers

Select the whole data and (1) go to Insert and (2) Select a Line chart with markers:

Line Chart with Highlighted Min/Max Points insert 2D line chart with markers
Insert a Line chart with markers

This creates a simple line chart:

Default Line Chart
Simple line chart

Step 02: Add additional columns to the data

Our process involves adding series to this chart, specific to the minimum and maximum values.

To do this, add additional columns to the data to extract these Min and Max values.

First, to get the maximum value, use an IF function as shown below:

This fetches only the maximum value in the dataset and all other values as #N/A.

Similarly, to get the minimum value use an IF function:

Note: Drag the formula to all the necessary cells to update.

With this our additional MAX and MIN columns of data are ready.

Line Chart with Highlighted Min/Max Points data with max min columns
Updated data

Step 03: Add new data to the chart

Our additional series is ready, let’s add this to our existing chart. Right-click on the chart and Select Data to add this.

Line Chart with Highlighted Min/Max Points select data

Add a New series, the Max as shown here along with the horizontal axis as the Months.

Line Chart with Highlighted Min/Max Points add max series
Add MAX series
Line Chart with Highlighted Min/Max Points add axis label
Add horizontal axis series

Similarly, add the Min series:

Line Chart with Highlighted Min/Max Points add min series
Add MIN series

With this, our modified line chart will look like this:

Modified line chart
The modified line chart

This chart is dynamic to changes in the data, try modifying the min/max values to see the chart getting updated automatically.

Step 04: Format the chart

A key part of creating a chart is to make it visually readable and appealing. Let’s apply some formatting to our chart to achieve this.

i. Include a chart title. To get this, click anywhere on the chart and then the “+” icon that appears in the top right corner.

Add a suitable name that explains the chart.

Line Chart with Highlighted Min/Max Points add chart title
Add Chart title

You can also format the chart title’s size, color, etc. as needed.

ii. Let’ ‘s add a legend to our chart to understand the series.

Move it to the top to accommodate more space in the chart.

Line Chart with Highlighted Min/Max Points add legend position
Add legend to the top

iii. Include axis titles in our chart and give appropriate names: MONTH and SALES to the X and Y Axes respectively.

Line Chart with Highlighted Min/Max Points add axis titles
Add axis titles

iv. Make the gridlines less obvious by changing the color to a lighter one (you can also remove them altogether). To get this, right-click on the guidelines and go to the Format Gridlines option.

Line Chart with Highlighted Min/Max Points format gridlines

This opens up a format pane to the right, where you can change the grid line color accordingly.

Line Chart with Highlighted Min/Max Points gridline color
Format gridlines

At this stage, our chart looks something like this:

Line Chart with Highlighted Min/Max Points after formatting gridlines

Step 05: Format the max/min points

Since we do not want the maximum and minimum series as a line but as points, we’ll have to format these series as well.

i. This can be achieved by formatting the “Markers” in these series. Click on the max series, go to the format pane, and modify the size of the marker, its fill, and border color as shown (these styling options can be modified per your requirement):

Line Chart with Highlighted Min/Max Points after formatting marker
Format the max series markers

Since, this is only a point, in the Line formatting, change to No Line:

Line Chart with Highlighted Min/Max Points change to no line

ii. In the same way, format the MIN series:

Line Chart with Highlighted Min/Max Points format min series
Format the min markers

Here too, remove the line in the Line formatting since we require only the point.

iii. Let’s format the sales series as well for better readability. Click on the Sales series and in the format pane:

Start by changing the color of the Line

Line Chart with Highlighted Min/Max Points change line color
Format the line

We’ll also modify the markers for this series, to distinguish them from the Max/Min series: modify the fill and border color alone:

Line Chart with Highlighted Min/Max Points modify marker fill & border color
modify the markers

iv. We’ll also include a rounded border for the chart: click on the chart, under Chart Options, Fill & Line:

Line Chart with Highlighted Min/Max Points chart border

At this, we have the line chart with highlighted minimum and maximum ready for analysis:

Line Chart with Highlighted Min/Max Points
Line chart with highlighted max/min points

Test the dynamic nature of this chart by modifying the minimum and maximum in your sample data.

This method of adding a series and including a formula to bring in specific data points can be extended to multiple scenarios where specific points are to be highlighted.

If you have any feedback or suggestions, please post them in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *