A scatter chart helps to plot data with two or more variables. One great advantage that a scatter chart has is the ability to show a relatively large number of data points in a small area. However, as the number of points plotted within the same space increases, it sometimes becomes difficult to distinguish individual data sub-sets.
One quick solution to group all the points within the desired data sub-set is to draw a rectangle or a circle over the chart using excel’s drawing toolbar. Another solution, though a little more involved, is to add a dummy X-Y data series to create a rectangle within the chart itself.
The advantage of the second approach is that the highlighted area in the scatter chart retains its coordinates when fresh data is added to the chart. Also one doesn’t need to worry about having the adjust about having to redraw or adjust the highlighted area when the chart area is dragged around. Another advantage is that the total area covered under the highlighted portion remains constant once set, irrespective the changes in the vertical or horizontal scales (their maxima and minima).
Here how you can incorporate this effect on your scatter charts:
Source data for the Scatter Chart
The source data for scatter chart consists of a number of X,Y values each representing one data point. We can use these to plot an X-Y chart in Excel as shown above. If the data consists of two or more categories, we can plot them as separate series and color them differently.
Create two dummy series and add X and Y Error Bars
Once we have the basic scatter chart ready, let’s add an additional series – this one consists of two points (each point with its own X and Y value). We’ll place the first point at the top left and the second one diagonally across at the bottom left.
Now comes the slightly tricky part. We are going to add x and y errors bars to both the points. The values for the x and y error bars will have the same magnitude but in opposite direction. So while for the lower point, the x error bar will go from left to right, for the upper one, it will move from right to left. The same logic is extended to the y error bars. Here is how the x,y coordinates and the data for the respective error bars will look like.
Format the chart, the grid lines and the two dummy series data points. Turn on the label for the top right point (or the lower one if you prefer that). If needed, you can even adjust the x,y coordinates of the points which will not only move the box around but will increase or decrease the area of the box as well.
You can download a sample worksheet with an example of scatter chart with highlight box to group data here or click on the button below: