ICAEW.com works better with JavaScript enabled.
Exclusive

Highlighting Chart Data

Author: Liam Bastick

Published: 02 Mar 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

When creating financial models, charts are often an effective way to present data. Sometimes, you might wish to highlight specific data points, in order to emphasise them. For example, I have the following historical data for income for a company, summarised quarterly:

Excel screen shot 1

From the provided data, I plot a Line Chart such as the one below, which has a clear minimum and maximum in Sep-16 and Dec-19, respectively:

Excel screen shot 2

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, e.g.

Excel screen shot 3

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):

Excel screen shot 4

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

=IF(G15=MAX($G15:$T15),G15,NA())

The Min series is calculated with this formula:

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

This results in the series only displaying the Maximum and Minimum value in the original chart data, and #N/A for everything else. Using formulae 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. I can do that by clicking on the original chart, and Excel will highlight the relevant data series:

Excel screen shot 5

I can then drag the values down:

Excel screen shot 6

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

Excel screen shot 7
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:

Excel screen shot 9

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 is now shown as the one below, with Max and Min are now chart series and will be changed dynamically depending on the data:

Excel screen shot 10

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

Topics
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250