Highlighting chart data in Excel

When creating financial models, charts are often an effective way to present data. Sometimes, you might wish to highlight specific data points to emphasise them.

Consider the following example. I have the following historical data for income for a company, summarised quarterly:

From the provided data, I plot a line chart such as the one below, which has a clear minimum and maximum:

If I wish to highlight these points on the graph, it would be easy enough simply to highlight them and change the format of the points, as shown in the screenshot below.

But what if the data changes? I would have to go back and edit the chart points each time there is a new maximum and minimum point. This is not the way to do it!

In this situation, let’s create dynamic highlights for the chart. To do that, I am going to use two “helper” data series: one series is to calculate the maximum and the other, the minimum (surprise, surprise):

The Total Income series represents the original series data that I used to construct the chart. Formulas are used to construct the Max and Min series; the Max series is calculated with the following formula:

=IF(G$15=MAX($G$15:$T$15),G$15,NA())

The Min series is calculated with this formula:

=IF(G$15=MIN($G$15:$T$15),G$15,NA())

This results in the series displaying only the maximum and minimum value in the original chart data, and #N/A for everything else. Using formulas allows these two series to be dynamic, so when there is new data, the Max and Min series will update accordingly. The #N/A error is deliberate, as it prevents data being plotted as zero, depending upon the type of chart being selected (I have deliberately used a line chart here to demonstrate this point).

Now that I have the data series, I can include them in the chart. When I click on the original chart, Excel highlights the relevant data series:

I can then drag the values down:

Alternatively, I can right-click on the original chart and select the Select Data option. Then, the Select Data Source dialog box will appear. I can then add new series by clicking on the Add button on the left side of the dialog box:

The next step is to fill out the Edit Series dialog box accordingly for Max and Min series:

The two additional series are now added and shown in the Legend Entries (Series) box:

Do remember to reference the Horizontal (Category) Axis Labels if the dates are replaced with sequential integers, as illustrated above; otherwise, this may cause charts to display incorrectly in alternative scenarios.

The chart now appears as shown below. Max and Min are now chart series and will be changed dynamically depending on the data:

In essence, this is “conditional formatting” for a chart.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.