home | about kurtosis | courses | course calendar | booking information | ideas | technique | work in progress | clients | contact us  

How to draw a funnel plot in Microsoft Excel

Static statistical process control



Let's suppose that you want to compare the referral rates of different GP practices to a Cardiology chest pain clinic at Anytown Royal Infirmary. One way of approaching the task is to draw a funnel plot. One of the advantages of this technique is that it enables you to take account of the fact that GP practices have widely-ranging practice population sizes.

Here is an example of such a funnel plot:


Referral rates of 23 GP practices for new Cardiology Chest Pain

appointments, 2009-10

Source: Patient Administration System

How to read the chart

The horizontal axis in this chart measures the size of practice population. The bigger the practice, the further to the right will be its figure (represented by a red dot); the smaller the practice, the further to the left its red dot will be. The vertical axis measures the referral rate, expressed as a rate per 1,000 practice population. The red dots show each individual practice's referral rate; and the horizontal centre line shows the overall (all practices combined) referral rate: in this example it is 2.72 referrals per 1,000 practice population. The dashed lines constitute the funnel. They are the upper and lower control limits that represent the boundary between "normal variation" and "special cause variation".

Taking the smallest practice as an example, it had a practice population of 1,429 and a referral rate of 3.5 referrals per 1,000 practice population. The largest practice had a practice population size of 14,801 and a referral rate of 4.26 referrals per 1,000 practice population.

Here's how to draw the funnel plot using Microsoft Excel. Use the data in this Excel workbook if you want to follow the steps methodically:

Funnel Plot Data.xls

Step One

The Data worksheet shows that you've got two pieces of information for each of the 23 GP practices. You have the practice population in column B and you have the no. of referrals made to the chest pain clinic in 2009-10 in column C. The practices have already been sorted from smallest population to largest population. For the funnel plot to work, you always need to have your data sorted in this way. You don't have to do it at this stage, you can do it later, but you will need to remember to do it.

Step Two

You first of all need to calculate each practice's referral rate expressed as a percentage. The final funnel plot will show the rate as a rate per 1,000. But we need to do it as a percentage (effectively, a rate per 100 practice population, but we hardly ever represent GP referral rates as percentages). The reason we are doing it as a percentage is that it makes the calculation of standard error (in Step Four below) less problematic. More on that later.

So in column D, use the formula =C6/B6 to calculate the rate for Practice A and then copy this formula down to all of the other rows in the worksheet. Format the column so that it shows the percentages to two decimal places.

The end result of this is shown in Data (2).

Step Three

You now need to create a column in the worksheet that shows the overall GP referral rate. This should already have been calculated in cell D30, so in your new column E, you can simply refer to this value so that you have 23 iterations of this value (which in this example is 0.27%).

The end result of this is shown in Data (3).

Step Four

As an intermediate step towards calculating the values for the upper and lower control limits you now need to calculate 23 standard error values in column F. This is a 'special kind' of standard error, and if you want to know more about this (and there is a lot more to know about this), then you need to attend one of our courses: either Visualizing Statistics or Health Service SPC.

The formula you are going to use in column Fand we'll do cell F6 first, to calculate the standard error for Practice Ais =SQRT((E6*(1-E6))/B6). A useful 'sensible check' on whether you've calculated your standard errors correctly is that the standard errors should be larger for the smaller practices and smaller for the larger practices.

The end result of this is shown in Data (4).

Step Five

You can now calculate the lower and upper control limits in columns G and H. We'll do the lower control limit for practice A first. The formula you need to type into cell G6 is =E6-(3*F6). By doing this you are calculating a value that is three standard errors below the centre line. It doesn't have to be three standard errors, but the standard convention for funnel plots is that you calculate control limits as the process average +/- 3SE. Repeat this calculation in cell H6, but use a plus sign instead of a minus sign. Hence your formula will be =E6+(3*F6). Copy these formulae down to the other rows in the worksheet.

The end result of this is shown in Data (5).

Step Six

The last calculations you need to do are all about converting your percentages (rates per 100) into rates per 1,000. Create four new columns (in this example we've done it in columns J, K, L and M) and simply multiply the relevant measure by 1,000 in order to arrive at the rate per 1,000. If you try to do this step earlier on in the process you will likely run into problems with the way that standard error is calculated, since you may well end up trying to calculate the square root of a negative number, which is not something Excel will take kindly to.

The finished worksheet should now look like the one in the sheet called Data (6).

Step Seven

You are now ready to draw the funnel plot.

Specify the following cell ranges:

  • B5:B28 (you need the practice populations for the horizontal axis)

  • J5:J28 (these cells will give you the y-co-ordinates of the red dots)

  • K5:K28 (this will plot the horizontal centreline)

  • L5:L28 (the lower control limits) and

  • M5:M28 (the upper control limits)

With these ranges selected, use the chart wizard to select an X-Y (Scatter) chart. In its unformatted state, this chart will look something like this:

...and you now need to format it so that it looks more like the one at the top of this page.

One of the things we recommend you do is to screen the negative values from the reader by scaling the vertical axis so that it starts at zero. This will help to prevent comments along the lines of "How can you possibly have a referral rate that is a minus number?"

A finished chart is shown in the workbook as the Funnel Plot sheet.



  Kurtosis 2010 | 99 Giles Street, Edinburgh EH6 6BZ | Tel 0131 555 5300 | email info@kurtosis.co.uk