Quality Management 3: Create Run Charts in Excel

Run charts are simple line charts drawn against time. The idea is to visualize the pattern and trend of captured data. The data could be about defects, issues, observations or variability. Let us see how to manage this using Excel.

Create run charts in Excel

Photo credit: tudedude / Foter / CC BY-NC-SA


Observing data across time helps us prioritize items. It can also help us elucidate underlying causes. Variability can be analyzed very easily using these charts.

I am assuming here that you already the know about the usage scenarios of Run Charts. The objective is to show how it can be created more easily and faster. More importantly, I will illustrate some additional useful stuff you can do.

Create a Run Chart

It is just a simple line chart. It may also have the Mean (average), the upper and lower Control levels plotted as well for visual reference.

Data has to be in the format we have discussed in the earlier article.


I have considered only two parameters: Rusting and Leakage. But you can have data for more parameters as long as it is in this tabular format. Download Sample File.

Now a pivot table will create the data exactly the way it is required. Date in Rows, Category in Columns and Count of Category in Value area.

image    image

Let us clean-up the look and feel of the pivot table first.  Go to Pivot Table Tools – Design tab and choose No Grand totals. Also change Report Layout to Tabular. Now create a simple Pivot Chart – Line chart.

Pivot chart - Line Chart

If you have numbers in the same scale, this will work fine. If you have totally different scales, you can use a different Y axis for the series.

Analyze ONE parameter at a time

However, if you want to add the Mean and other limits to the chart, it is better to see ONE parameter at a time. This is best done by dragging the Category column to Filter area. From the Filter dropdown – choose one of the categories.

image    image

This gives you the base chart.


Incorporating Average

Average needs to be calculated in the raw data. NOT inside the pivot. This is done by adding a column with AVERAGEIF function as shown here.


Notice the syntax. This syntax works in Office 2010 onwards. Field name in square brackets refers to the entire column – like Category and Count above. Column name with @ sign means take the value from current row.

In short, this formula means give me the average of count for each item in the category column. The output looks like this. Yes, there is repetition of the same average value but it is ok. We will see a more optimal method some other time.


Now the chart for Leakage is seen. Change the filter to another item (Rusting) and the chart will change accordingly.

image  image

Adding UCL and LCL will require similar approach.

Continue further

This gives you what Run Charts are supposed to give. But remember, that you are using Pivot Table. There are hundreds of more useful features there. Continue your analysis and do not get stuck to the traditional analysis the way it was taught to you in the Quality management textbook. Go beyond it.

Here are some ways to go beyond routine analysis. Enjoy.


One thought on “Quality Management 3: Create Run Charts in Excel”

Comments? Suggestions? Wish list?