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:
30 Responses to “Highlighting Data Points in Scatter and Line Charts”
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.
Thanx Jon
I have updated the post to reflect you comments.
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!
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.
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
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.
@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...
@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
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
@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?
See this for an example of several such techniques:
http://mdhealy.home.sprynet.com/WorldPopulationInteractiveEstimates.xls
[...] Highlighting points in line charts & scatter plots [...]
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.
My offset is returning 0 instead of value in cell. Why?
@Anna
Can you please post your formula or better still your workbook
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
I have uploaded my workbook here: https://docs.google.com/open?id=0B8WUgnRd1Oj0NUtWUDBBMkZpRHM
Another problem besides the one above is that the new data series doesn't show up on chart so I can't get to its editable features. Perhaps when the prior problem is fixed maybe this will simultaneously be as well?
Also need to know where to find slider within Excel. The slider in my workbook was copy/pasted from the sample file above.
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
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
Great tips - thanks!
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.
@Patrick
Can you post a sample file for us to see the issue
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)
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.
also, could it be possible to do this with stacked bars/columns without using VBA?
After couple days of working on it, I finally made the line work!! I didn't use the OFFSET formula, but rather the IF formula in a separate row than my original numbers.
My issue for not getting it to work right away was not paying attention to all the parentheses!!
Fantastic, Thanks a lot!
This is great stuff. Thank you. It really helped me highlight scatter-plot data in sequence. It's a real sharp addition to charting.
Hello,
I was successful in plotting a highlighted duplicate point as a Series2, however my "highlighted point" won't show up with Series1 on top. I made sure that the highlighted point is much larger that the Series1 points but it still doesn't show. As soon as I click on Series1 data in the graph and delete it, my Series2 highlighted point suddenly appears where it should be.
It is as if excel is suppressing my "Highlight" point because it has identical coordinates as the Series1 data.
Any suggestion?
Thanks
Answered my own question just now.
For anyone troubleshooting why their highlighted point series doesn't show up, make sure it is plotting on the same axis (either primary or secondary) as your source data.
Awesome tutorial thanks!
@Jordan
You can have them on separate axis, but you must set the axis scales to be the same