How to Make a Levey Jennings Chart in Excel

Creating a Levey-Jennings chart is a fundamental skill for anyone involved in quality control, especially in clinical and manufacturing labs. This simple but powerful graph helps you visually track the performance of a process over time, making it easy to spot shifts, trends, and errors before they become critical. This guide will walk you through building a professional Levey-Jennings chart from scratch using Microsoft Excel.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly Is a Levey-Jennings Chart?

A Levey-Jennings chart is a type of statistical process control (SPC) chart that plots quality control (QC) values against time. Its core purpose is to verify that a measurement process, like a laboratory test, is operating within its expected limits of precision and accuracy.

The chart is built around a few key statistical values calculated from a set of your own QC data points:

• The Mean (Average): This forms the center line of your chart, representing the target value.
• Standard Deviation (SD): This measures the amount of variation or dispersion in your data.

Using these two values, you create control limit lines on the chart at +1 SD, +2 SD, and +3 SD above the mean, and -1 SD, -2 SD, and -3 SD below the mean. As new QC data is generated, it gets plotted on the chart. If the process is stable, or "in control," the points will be randomly scattered around the mean. If a point falls outside of the control limits, or if a non-random pattern emerges, it signals a potential problem with the process that needs investigation.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Gather and Organize Your QC Data

Before you can build the chart, you need a baseline set of data. This typically involves running the same quality control material at least 20 times to get a reliable calculation for your mean and standard deviation. The more data points you have, the more statistically robust your control limits will be.

For this tutorial, let’s assume we are tracking a Level 1 Glucose control and have collected the following 25 data points. First, set up a simple table in Excel with a column for the Run Number and a column for the QC Value.

Your initial spreadsheet should look something like this:

Example Data Setup:

Step 2: Calculate the Mean and Standard Deviation

Next, you’ll calculate the mean and standard deviation for your baseline data set. It's a good practice to place these calculations in a separate area of your sheet so you can easily reference them.

1. Calculate the Mean (Average):

Find an empty cell (e.g., E2) and use the AVERAGE function. If your QC values are in cells B2 through B26, the formula would be:

=AVERAGE(B2:B26)

For our example data, the Mean is 90.56.

2. Calculate the Standard Deviation:

In another empty cell (e.g., E3), use the STDEV.S function. The ".S" signifies that Excel is calculating the standard deviation for a sample of data, which is appropriate for QC applications.

=STDEV.S(B2:B26)

For our example data, the Standard Deviation is 3.49.

Let's round these for simplicity: Mean = 90.6, SD = 3.5.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Define the Control Limits in Your Table

Now, we’ll expand your data table to include columns for the mean and each of the control limits. These lines will be straight across your chart, so the value in each of these columns will be the same for every run number.

Create new columns for: Mean, +1 SD, -1 SD, +2 SD, -2 SD, +3 SD, and -3 SD. Then, calculate the values for these limits based on the mean and SD you just figured out.

• Mean = 90.6
• +1 SD = Mean + (1 * SD) = 90.6 + 3.5 = 94.1
• -1 SD = Mean - (1 * SD) = 90.6 - 3.5 = 87.1
• +2 SD = Mean + (2 * SD) = 90.6 + 7.0 = 97.6
• -2 SD = Mean - (2 * SD) = 90.6 - 7.0 = 83.6
• +3 SD = Mean + (3 * SD) = 90.6 + 10.5 = 101.1
• -3 SD = Mean - (3 * SD) = 90.6 - 10.5 = 80.1

To populate these columns in Excel efficiently, use absolute cell references (with dollar signs) for your mean and SD statistics. For example, if your mean is in cell E2 and your SD is in E3:

• In cell C2 (your Mean column), enter: =$E$2
• In cell D2 (+1 SD), enter: =$E$2+$E$3
• In cell E2 (-1 SD), enter: =$E$2-$E$3
• And similarly for +2 SD, -2 SD, +3 SD, -3 SD.

Once you have the formulas in the first row, you can click and drag the fill handle (the small square in the bottom-right corner of the cell) down to populate the rest of the columns. The absolute references ensure that the formulas always point to your calculated mean and SD.

Your expanded table should now look like this:

Step 4: Create the Line Chart in Excel

With all the data prepared, it's time to build the visual chart.

1. Select Your Data: Highlight your entire data table, including headers. This includes the Run Number, QC Value, and all seven of your limit columns.
2. Insert a Chart: Navigate to the Insert tab on the Excel ribbon. In the Charts section, click on Insert Line or Area Chart, and choose the Line with Markers option.

Excel will generate a chart, but it will probably look a bit cluttered and incorrect. Don’t worry, we'll clean it up in the next step.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 5: Format the Chart for Clarity

A default Excel chart is a good start, but a proper Levey-Jennings chart requires specific formatting to be easily readable.

1. Format the QC Data Series:

This will leave just the individual data points on the chart without a connecting line, which is the standard visualization for a Levey-Jennings chart.

1. Format the Control Limit Lines:
2. Add Titles and Labels:

After these formatting steps, your Levey-Jennings chart is complete and ready for interpretation!

Step 6: Interpret the Levey-Jennings Chart

The real power of this chart is in what it tells you. It helps you apply rules, like the popular Westgard Rules, to detect errors.

Here are a few common patterns to look for:

• Out of Control (Outliers): A single QC point falling outside the ±3 SD limit is a clear sign of a problem, often a random error. Looking at our sample data, the last point (101) is very close to the +3 SD line (101.1), acting as a warning. If another point landed there, an investigation would be needed.
• Shifts: This happens when several consecutive data points fall on one side of the mean. For example, six points in a row above or below the mean line can indicate a systematic error, such as an instrument needing recalibration.
• Trends: If you see six or more points steadily increasing or decreasing, this indicates a gradual loss of reliability. This could be due to aging reagents or a deteriorating instrument component.
• Warning Rules: Two consecutive points falling outside the same ±2 SD limit is another major indicator of a systematic error that needs immediate attention.

By monitoring the chart daily, lab personnel can catch these issues early, ensuring that the results they produce are consistently reliable.

Final Thoughts

Building a Levey-Jennings chart in Excel is a perfectly manageable process of calculating stats, setting up control limits, and formatting a line graph. It’s an effective method for monitoring process performance, empowering you to make data-driven decisions and maintain high quality standards.

Although Excel is great for one-off charts, it can be time-consuming to manually update them across multiple tests or instruments. When we built Graphed, we focused on making this an automatic process. Instead of downloading CSVs and wrangling spreadsheets, you can hook up your data sources and simply ask questions like, "Create a Levey Jennings Chart for my Glucose L1 control data for the last 30 days." We generate a live dashboard instantly, so you can spend less time building reports and more time acting on the insights.