Excel - Show High/Low Points on a Line Chart
Sometimes it is obvious where the high and low points on a line chart are, sometimes not.
Wouldn’t it be nice if you could automatically show the high and low points on a chart and perhaps use a custom marker to make them stand out? Well you can….using dummy data.
Taking some basic data as an example….
The high point is clear to see but the low point is not so obvious and we need to check the data to see the figures to see that November is lower than June.
You can manually change the appearance of the marker in the chart but if the numbers change for any reason you will then have to manually change the marker, so we need some dummy data to help us identify high and low values in our data which we then need to superimpose on top of our real data.
In a new column add an IF statement to identify the highest value(s) in the data.
=IF(B2=MAX($B$2:$B$13),B2,NA())
This will test the value of cell B2 against all the other cells and if it is the highest value it will be displayed in cell C2. If not, then it will display #N/A.
IMPORTANT: By using NA() it means that nothing will be displayed in the chart. If you use 0, then it will try to plot it. This is a useful little trick to know when charting and is used in many custom type charts using dummy data.
Then, in the next column do the same but this time to identify the lowest value(s).
=IF(B2=MIN($B$2:$B$13),B2,NA())
You should now have this:
Click into the data and create a line chart using ALL the data including the dummy series. This will then give us the following chart:
Straight away we can see there are now 3 series on the chart, but because of our formula using NA() only the MAX and MIN points are visible on the two dummy series.
CAUTION: If you have multiple high or low points, because we have used a line chart it will try to join them with a line. Make sure that you go into FORMAT DATA SERIES, LINE COLOR and set this to NONE and avoid the problem below.
Now to create a custom marker to make the high and low points to make them more than obvious.
Select SHAPES from the INSERT tab, and select a shape of some sort. In this example I am going to select an upwards pointing arrow.
Size and format your shape as appropriate. While the shape is selected press Ctrl + C, then click on your chart and click on the series that represents the high value(s) to select it. Press Ctrl +V – job done. Do the same for the other series and the end result will be like this:
Once the custom marker is in the chart you can delete the original shape.
As well as using shapes you can use any image you like but make sure you save it as a small image before copying it to your chart otherwise the picture will appear in its original size in the chart not the size you reduced it to by clicking and dragging the grab handles.
Now, if the values are changes, the high and low point markers will automatically adjust according the values in the table.
Follow me on Twitter @excelmate