Excel Chart: Always Markup the Maximal and Minimal Value in a Line Chart.

See all post here

Sometimes, we need markup some special values from a line chart such as the maximal or the minimal value. For example, we usually highlight the highest and the lowest quote from a stock price.

The question is How to markup the maximal and minimal value from a line chart automatically in Excel. 

We need two auxiliary data series to solve this issue. The first data series has relation with the maximal value. If raw data is not the maximal value, the relevant value in the first series will be "#NA", or the value will be the maximal value. After this rule, there are lots of "#NA" and one maximal in the first auxiliary series. The second data series is similar with the first one but for minimum.

Step 1: Build auxiliary data sequence. Suppose we have a raw data series A2:A6.

Select cell B2 and input the following formula:
   =IF(A2=MAX($A$2:$A$6),A2,NA())

Select cell C2 and input the following formula:
  =IF(A2=MIN($A$2:$A$6),A2,NA())

Drag the cell handle and fill B2:B6 with the same formula as that in B1. Fill C2:C6 with the same formula in C1.

Notice: The function NA() always returns "#N/A", which means the cell does not contain any valid value. It's reasonable, because there usually is only on maximal or minimal value.

Step 2: Create Line Chart. Select A1:D6 and insert "Line Chart with Markers" from ribbon bar. The following line chart appears. Try to update the raw data, you can find the maximal and minimal value always be highlighted automatically.

Step 3: Display data label for the maximal and minimal value. Click the maximal value and "Show data label". In the data label formatting dialog, check "Series Name" and "Value" at the same time. The final label is "Max,600".

Step 4: Let's go more. The example chart looks professional. Can we create it in Excel? The answer is sure. But we need two additional operations:

1)    Combine a line chart and area chart together to get an area chart with a highlight border.
2)    Customize data label with error bar and picture.
 

I just demo the first issue in this post. In fact, this issue is not very complex. We just need create a line chart and an area chart with the same data sequence.
 

Insert a blank area chart into the work sheet. Select the data series (Let’s use data from above example) and press ctrl+c to copy them to clipboard. Click the blank chart and press ctrl+v twice. Notice we need press ctrl+v twice to get two overlapped area chart.
 

Click series 2 and change its type to “line chart”. OK an area chart with highlight border appears.

If you like, you can apply some special color to it. I fill the following chart with these colors from above example. Does it look better?