#How to separate data in scatter chart excel series#
Setup a Cell Reference cell by putting a 1 in D43īesides the sample data, add a new series HighlightĬopy D2 down to D27, Don’t worry about the errors #N/A, you put them there. Type any values into the X Column and watch as the offset happily maintains the highlighted point.Īs 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.įor 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. The Offset only cares about how far it has to go to get the value, not the value.īy 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. 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. Why use Offset instead of Vlookup or Index/Match? The offset retrieves its offset value from a Cell Reference cell which in turn is controlled by a slider. The coordinates for the new point are retrieved from the My Data list by using an offset from the top of the list. 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 Select the Label and change the Font to a Bold and Increase Size so that it stands outĪs 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. On the Labels Options Tab, Tick the X & Y values Right Click the New Series and select Format Data Labels Right Click the New Series and select Add Data Labels Select a larger Marker Size and make it a Bold Red to stand out Right Click the new point and Format Data Series You will now have a new data point which will be at point 1 on the chart Set the Sliders Cell Link, Min, Max and other details Right click on the chart and goto Select Dataĥ. Note that both these formula retrieve a value that is the value in the Cell Reference cell, B44, below B1 and C1 respectively. In C43 put the equation =OFFSET(C$1,$B$44,0) In B43 put the equation =OFFSET(B$1,$B$44,0) The new series will be the 2 cells at B43:C43 To plot a single point it is a matter of adding a new data series to the chart 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. We will use this add a new series with just one point. It is worth noting that chart series for Scatter Charts don’t have to have an equal number of entries in each series. These types include Line, Area, Column, and Bar (Thanx Jon) Y value vs X value (Scatter Charts)Īs 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. 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. 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. Examples of the X Label charts are Line, Column, Surface, Area, Radar and Bar charts. It is a great technique for complex scientific and engineering charts where you may have hundreds or thousands of points.Įxcel charting basically has 2 styles of charts with these being Y value vs X Value charts and Y value vs X Label charts.Įxamples of the X Value charts are Scatter and Bubble 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. 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. Please visit About – Hui to learn more about him. Hui will share excel tutorials, implementations with us once a week. Meet our new guest author, Ian Huitson, or Hui.
0 kommentar(er)
