Highlighting Data Points in Scatter and Line Charts

This week I am going to introduce a method for allowing single points to be highlighted and interactively moved in Excel Scatter / X-Y Charts and Line Charts.

You will see a lot of these style charts in various places where you want to highlight various aspects of the chart to your audience. It is a great technique for complex scientific and engineering charts where you may have hundreds or thousands of points.

Introduction

Excel charting basically has 2 styles of charts with these being Y value vs X Value charts and Y value vs X Label charts.

Examples of the X Value charts are Scatter and Bubble charts. Examples of the X Label charts are Line, Column, Surface, Area, Radar and Bar charts.

The basic differences between these is that the former has a variable X Axis and the later has a fixed X-Axis spacing between subsequent data points.

Some members of the X Label charts can display a value-type X axis when the X entries are dates, ie: The X values are plotted proportionally to the dates they represent. These types include Line, Area, Column, and Bar (Thanx Jon)

Y value vs X value (Scatter Charts)

As these charts are plotting Y vs X directly onto the chart, it is simple to add a series which contains the points you want to highlight.

It is worth noting that chart series for Scatter Charts don’t have to have an equal number of entries in each series. We will use this add a new series with just one point.

Method:

Goto Pg1 of the sample file. Sample File

My Data is an X-Y set of data in B2:C41, each Y value in Column C is plotted on the chart against the corresponding X value.

To plot a single point it is a matter of adding a new data series to the chart

The new series will be the 2 cells at B43:C43

1. Setup 2 lookup cells

In B43 put the equation =OFFSET(B$1,$B$44,0)

In C43 put the equation =OFFSET(C$1,$B$44,0)

Note that both these formula retrieve  a value that is the value in the Cell Reference cell, B44, below B1 and C1 respectively.

2. Setup a Cell Reference cell

Put a value in B44 for now say 1

3.Add a new Data Series to the Chart

Right click on the chart and goto Select Data

Add a New Series

Series Name  Highlight

X Values  =’Pg1′!$B$43

Y Values  =’Pg1′!$C$43

4. Add a slider

The slider is already installed

5. Set the Sliders Cell Link, Min, Max and other details

You will now have a new data point which will be at point 1 on the chart

6. Format the New Data Series

Right Click the new point and Format Data Series

Select a larger Marker Size and make it a Bold Red to stand out

7. Add a data Label to the series

Right Click the New Series and select Add Data Labels

8. Format the Data Label

Right Click the New Series and select Format Data Labels

On the Labels Options Tab, Tick the X & Y values

Select the Label and change the Font to a Bold and Increase Size so that it stands out

Use:

As you move the slider the Highlighted point will move back and forwards across the screen and show both the location and X & Y Values of the data point.

How Does This Work?

The chart contains a second series consisting of a single point (x,y)  which has been formatted to make it stand out on the chart

The coordinates for the new point are retrieved from the My Data list by using an offset from the top of the list.

The offset retrieves its offset value from a Cell Reference cell which in turn is controlled by a slider.

Why use Offset instead of Vlookup or Index/Match?

We aren’t concerned with looking up the actual value of the highlighted point, we are interested in retrieving for example the 9th data point from the list and the the 10th or 8th as we move the slider. The Offset only cares about how far it has to go to get the value, not the value.

By doing this we can mix up the X values, as Scatter charts allow you to do, and offset will happily retrieve data in order and doesn’t care about duplicates or having sorted data. Type any values into the X Column and watch as the offset happily maintains the highlighted point.

Line Charts

As these charts are plotting Y vs the position of the value on the X-Axis, a slightly different method is employed to highlight a point of interest.

For Line Charts we will add a new series to the chart and then use a method for hiding the non-highlighted points  so that only the highlighted point is visible.

Method

Goto Pg2 of the sample file. Sample File

1. Setup a Cell Reference cell

Setup a Cell Reference cell by putting a 1 in D43

2. Add a New Data Series

Besides the sample data, add a new series Highlight

D1:  Highlight

D2: =IF(ROW()-1=$D$43,C2,NA())

Copy D2 down to D27, Don’t worry about the errors #N/A, you put them there.

3. Add a new Data Series to the Chart

Right click on the chart and goto Select Data

Add a New Series

Series Name – Highlight

Y Series =’Pg2′!$D$2:$D$27

Note there is no X Value as the Y values are plotted in order against the existing X Values

You will now have a new data point which will be at point 1 on the chart

4. Format the new Data Series

Right Click the new point and Format Data Series

Select a Bigger marker size and make it a Bold Red to stand out

5. Add Data Labels

Right Click the New Series and select Add Data Labels

Right Click the New Series and select Format Data Labels

On the Labels Options Tab, Tick the X & Y values

Select the Label and change the Font to a Bold and Increase Size so that it stands out

6. Add a slider

The slider is already installed

7. Set the Sliders Cell Link, Min, Max and other details

Use:

As you move the slider the Highlighted point will move back and forwards across the screen and show both the location and X & Y Values of the data point.

How Does This Work?

The chart contains a second series consisting of a Column of #N/A error messages and a single cell containing teh Y value for the corresponding data point

Excel ignores and doesn’t plot the cells with the error message and so only the highlighted cell is plotted

The coordinates for the new point are retrieved from the My Data list by comparing the current Row to the Cell Reference cells value and if they are the same retrieving the Y value, all others rows have an error message inserted.

The slider is connected to the Cell Reference cell and so when the slider is moved the Cell reference cell updates and the new highlighted cell retries its value.

Quick Tip #1:

You can change the highlight from a standard marker to pretty much anything you like

Insert an Icon on your worksheet, Insert Menu, Insert Icon

Format the icon as you wish, Color, Size and Copy the icon

Select the Chart and select the Highlighted data point and Paste

To apply the picture/icon to all points in a series select the series and paste

Quick Tip #2:

You can add multiple highlights using the same techniques described in this post ie: for showing Min and Max values.

Instead of linking the Cell Reference cell to a slider link it to the Minimum or Maximum value of the data: =Min(Range), =Max(range)

Checkout the example on Pg3 of the Sample File: Sample File

FUNCTIONS USED:

Offset: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

Row: =Row() returns the Row number of the Current cell

=Row(M10) returns the Row Number of Cell M10 = 10

NA: = Returns the Error Message #N/A

How do you like to highlight your data? Let us all know in the comments below:

What would you like to see discussed as a How To? Let me know in the comments below:

NEXT THURSDAY: Scheduling Resources