Target Charts are a type of scatter chart with concentric circles which allow you to see more clearly the distribution of the points around a central point, commonly the origin (0,0) or the centre of the cluster.

For example, if you had two series on a scatter chart that looked like this:

Standard Scatter Chart

In this example, both series have been generated at random from two separate normal distributions. It isn’t especially easy to estimate the centre of the cluster, nor to immediately to identify the most extreme outliers. But by plotting it as a target chart, both of these become a bit more obvious.

Data-Centred Target Chart

In this case, the axes have been redrawn to pass through the centre of the cluster, the chart area has been made square, and three evenly spaced concentric circles have been added to the chart. As a result, the outliers and the distribution of the points are much clearer.

The chart can also be created with the axes fixed through the origin (0,0). In this case the circles are still evenly spaced, but have a larger radius.

Zero-Centred Target Chart

Whether the circles are centred around the data midpoint or the origin, the number of circles drawn, the precision of their location and a number of other parameters can all be adjusted in the Target Chart Settings dialog box (see Settings below).

The above charts can be simply created with the single-click Target Chart button on the Excel ribbon toolbar. The series points are still “live” (i.e., they update with changing data) but the circles are fixed at the size and position as they were originally created. In some cases, particularly the data-centric target chart, this is not ideal, especially if the data changes significantly. In this case it might be better to use the Target Chart formula “=QC.TARGET(…)”

Using the “QC.TARGET(…)” formula

Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.

The first parameter to be selected is the original data range (you can choose whether to include series headings in this selection or not).

The next two parameters set the X and the Y co-ordinates for the chart centre. These can be fixed values or formulas themselves so, for example, you could set these to be the average of the data points using the Excel “=AVERAGE(…)” function.

The next parameter is a range which includes the radii of the circles you want to appear on the chart. In this case, there are five circles, increasing by 2 each time. Again, these could be formula results themselves.

The final parameter is the number of data points that you want to comprise each circle. The more points you have, the smoother the circles will be, but there’s no need to select an excessive amount – about 30 is usually sufficient.

The output from the function is then a copy of the original data, but with extra series to represent the circles. These additional series extend below the original data – in this example, there are 5 circles of 30 points each, so the formula requires 150 extra rows to draw the circles – this is a good reason not to use too many points to draw the circles.

Now if you select a cell within this formula range and press the Target button, a Target Chart will be created which will have the circles as separate data series connected to the range. This means they can update with changing data.

See the Target Chart worksheets in the Example Workbook to see it in practice.

Settings

The Settings for Target Charts are accessed via the Target drop-down menu on the Excel ribbon toolbar.

Target Chart Settings
  • Zero-Centred / Data-Centred – For the single-click charts, this determines whether the centre of the circle is set at the centre-point of the data or at the chart origin (0,0)
  • Number of Circles – How many circles appear on a single-click chart
  • Max Circle Significant Figures – The precision to which circles are set, e.g., “3”, “3.2”, “3.19”, etc.
  • Keep Chart Square – In order to show the target lines as perfect circles, the plot area should be square, but there may be cases where plotting them as ovals is better.
  • Lock Axes – For the live charts, this keeps the axes fixed at their original creation size so that changing data doesn’t cause it to resize. Be cautious, though, as new data points could be plotted outside the visible range.
  • Visible Axes – This determines whether the X and Y axes are still visible on the chart, or whether just the circles are visible.
  • Join Points – This will connect the points of the data series – useful if you want to plot a path around the chart, but usually not required.