Highlighting Data Points in Scatter and Line Charts

Posted on November 11th, 2010 in Charts and Graphs , Excel Howtos , Posts by Hui - 24 comments

Meet our new guest author, Ian Huitson, or Hui.

Hui will share excel tutorials, implementations with us once a week. Please visit About – Hui to learn more about him.

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

Your email address is safe with us. Our policies

Written by Hui...
Tags: , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

24 Responses to “Highlighting Data Points in Scatter and Line Charts”

  1. Jon Peltier says:

    It might make more sense to call the two chart classes “X Value” and “X Label” classes, since both have X axes.
     
    Surface, Area, and Radar charts should be included in the X Label class, not the X Value class.
     
    Some members of the X Label class can display a value-type X axis when the X entries are dates: the X values are plotted proportionally to the dates they represent. These types include Line, Area, Column, and Bar.

  2. Hui... says:

    Thanx Jon
    I have updated the post to reflect you comments.

  3. Stevo says:

    Excellent post; I had tried without sucess to highlight a point. For your interest the point I want to automatically highlight is the MAX, so I can modify your formula (remove the slider, etc) to do that. Thanks!

  4. Jon Peltier says:

    This setup is fine for XY charts if you have one point to highlight. But the approach used for the Line chart is more flexible. Suppose you are highlighting the maximum value: if you write a formula to show a value if it matches the maximum, and #N/A if it doesn’t, then you can accommodate multiple points that meet the criterion.
     
    This is the approach I took when I wrote about conditional charts (http://peltiertech.com/Excel/Charts/ConditionalChart1.html). Your ability to highlight different points is limited by your ability to capture criteria in the formulas you write.

  5. cALi says:

    Dear all,
    Please find my contribution in the link below:
    https://www.yousendit.com/download/dklyQ1ZnaFJHa05jR0E9PQ

    I know this is not what you expected, since it is not a scatter or Line Chart , however the topic is highlighting and considering next week scheduling is the main topic, I hope you find this file interesting.

    Highlighting is made with two different technics, conditional formating to highlight activities (plain text, horizontal), and 2nd-series bar chart for date highlight (vertical).

    Regards,
    cALi

  6. Impiorl says:

    Great post – How would you change the code to highlight a point and then move it to another series so that you could remove it from a regression ? This would be most useful in removing outliers. This other series would still be visible but plotted with different symbol parameters.

  7. Chandoo says:

    @Impiorl: Good idea. You can use data filters on source data to exclude data points from your chart. This should technically exclude the points in regression lines too (I have not tested this yet..). See this: http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/ for an implementation of this technique…

  8. Hui... says:

    @Impiorl:
    Another interactive way is to set up a number of Highlight series for each chart series as described in the Line Chart section above.
    Change the formula so that a point is highhlighted by 2 criteria, The First is as described above and the second a Radio Button which you could use to select the series to highlight.
    .
    I have put an example at: http://rapidshare.com/files/430905675/Chart_Point_Highlighter2.xlsx

    • Itzel says:

      Hi,
      Interesting, I’m new in this kind of things. I don’t know how to change the formula to do the radio button, could you explain it?? I tried with if, but I only can put one if statement. One more question, How can I do it for XY data??
      Thank you

      • Hui... says:

        @Itzel

        I’m confused

        The 4 Buttons besides the Chart on Pg4 are “Radio Buttons”, although Microsoft calls them Option Buttons

        To change the chart to an X-Y or Scatter Chart right click on the Chart

        Change Chart Type

        Select the appropriate X-Y Scatter chart to suit your purposes

        Pg 1 to 3 are all scatter charts

        If this doesn’t help please clarify your request?

  9. Matt Healy says:

    See this for an example of several such techniques:
    http://mdhealy.home.sprynet.com/WorldPopulationInteractiveEstimates.xls

  10. Mark de Vries says:

    Hello all,
    Here a question from the Netherlands.
    I like this post very much, but in my case the chart is a pivotchart. Is this also possible for a pivotchart? If yes, how? If no, is there an other solution to my problem?
    I have a pivotchart that shows the price of a product during a year. I want to show the value of the most recent billed price. The chart updates every week, so the most recent billed price is every week a new ‘dot’ in the chart.

  11. anna says:

    My offset is returning 0 instead of value in cell. Why?

  12. Leon Voisey says:

    Is there a way to get it to use the selected cell in the source table rather than a slider?

    I have done something similar to this but it curently requires a selection change sheet sub and 2 custom functions in a module in vba

  13. helen says:

    Hi, I am searching for a way to visually indicate within the dataset where items are shown ona linear Scatter chart so I can indentify the ‘blob’ more easily within the data to see what it relates to! There are 14 categories contributing to the dataset. Any ideas please? Excel 2010

  14. Tom says:

    Great tips – thanks!

  15. patrick says:

    Thank you for all the great tips, chandoo and Hui.

    I’ve more or less gotten the hang of the techniques discussed in this post and have been able to apply them to situations that are more or less similar to the examples given.

    But when I tried to extend it to something I’m working on right now, I’ve been hitting a brick wall.

    I have an XY table with text identifiers for each XY pair (based on which the table is sorted).

    For the X component of the highlight, I used:

    =SMALL(Xrange, COUNTER)

    COUNTER being the cell that the slider links to.

    I used the INDEX-SMALL-IF-ROW array formula to then retrieve the corresponding Y values (my table has duplicate X values):

    =INDEX(Ycolumn,SMALL(IF(Xcolumn=X,ROW(Xcolumn),""),k))

    My problem is I can’t figure out how to relate the COUNTER to k and retrieve the next corresponding Y value when I scroll the slider to the next duplicate instance of X.

    Is this doable? Or is my only option resorting the original table according to X values?

    Thank you in advance.

    • Hui... says:

      @Patrick
      Can you post a sample file for us to see the issue

      • patrick says:

        Thank you for your reply, Hui.

        I am attaching a sample as requested:

        https://www.dropbox.com/s/rbv8gsezjzp5z9c/sample.xlsx

        I highlighted in yellow the formula I was having trouble with.

        Some clarifications on my previous post:
        - the array for SMALL in the first formula should be XColumn, not Xrange (sorry)
        - the second formula is an array formula (forgot to include the curly braces, sorry again)

      • Patrick says:

        Figured out something, although it’s most probably not the best solution:

        {=INDEX(YColumn,MATCH(SMALL(RANK(XColumn,XColumn,1)+RANK(YColumn,YColumn,1)/COUNT(YColumn),COUNTER),RANK(XColumn,XColumn,1)+RANK(YColumn,YColumn,1)/COUNT(YColumn),0))}

        Basically assigned RANK values to each data point with X as major criteria and Y as minor, then uses the COUNTER from the slider to scroll through the data points in order based on the RANK.

        Feels rather roundabout to me, but it works. If anyone can offer a cleaner solution I’ll be much obliged.

        Credits to Mr Girvin and D Dalgleish for their great RANK tutorials.

  16. patrick says:

    also, could it be possible to do this with stacked bars/columns without using VBA?

Leave a Reply