Creating a Run Chart in Microsoft Excel

in Health Care, Measurement, Tools & Methods, Variation, Videos

By David M. Williams, PhD

Harvardx course Practical Improvement Science in Health Care with the Institute for Healthcare Improvement.

Now you can build a run chart by hand, and I actually strongly encourage that, because it’s really useful to sit down and have that relationship with putting your own data in and knowing where the changes are occurring as you’re plotting your dots on a piece of paper. But most people use a spreadsheet program, whichever one they prefer, and you can actually build it in any program that does spreadsheets.

What you need to do is to create three columns. One is the time series, so whether that’s days or months or events. The second column is your data. And the third column is for your median. And what you’ll do is you’ll enter your time data. So is it day one, two, three, four, five — vertically down column one. And then in column two, next to the day when you’ve collected data, you’ll enter vertically your data for each of those data points. And then finally, what we need to do is to create a median.

So once we reach 12 data points, this is where we can add the median. So to build that median, what we need to do is select the cell underneath the word “Median” that you’ve put in as a header in column three and put in this simple formula that will highlight your data from column two. Now select the second cell in column three and type =C2 All this does is it actually, instead of having to recopy the formula all the way down, it just copies what the formula was in the first cell that we built. Now grab the corner of the C3 cell and drag it down until the median copies next to all of your data. So eventually you should see the same number go all the way down the column right next to the data that you entered in the middle column. I also at times will, when you have your chart, recommend extending the median out ahead of your data so you can use it to make predictions.

Now select all three columns of data, including the headings, and in your menu bar find the place where it says Insert or Build A Line Chart and select that. So by following these simple steps you should be able to build a run chart with your data.

If this was helpful, share and include me @DaveWilliamsATX. Sign up here to receive a monthly email from me that includes all my blog posts and other Improvement Science resources I think you’d appreciate.