Indian Journal of Medical Microbiology IAMM  | About us |  Subscription |  e-Alerts  | Feedback |  Login   
  Print this page Email this page   Small font sizeDefault font sizeIncrease font size
 Home | Ahead of Print | Current Issue | Archives | Search | Instructions  
Users Online: 1383 Official Publication of Indian Association of Medical Microbiologists 
 ~  Similar in PUBMED
 ~  Search Pubmed for
 ~  Search in Google Scholar for
 ~  Article in PDF (347 KB)
 ~  Citation Manager
 ~  Access Statistics
 ~  Reader Comments
 ~  Email Alert *
 ~  Add to My List *
* Registration required (free)  

 ~  References
 ~  Article Figures

 Article Access Statistics
    PDF Downloaded335    
    Comments [Add]    

Recommend this journal


  Table of Contents  
Year : 2011  |  Volume : 29  |  Issue : 4  |  Page : 448-449

Use of the microsoft excel for automated plotting of Levey Jennings charts

Department of Lab Medicine, Max Super Speciality Hospital Phase 6, Mohali, India

Date of Submission11-May-2011
Date of Acceptance22-Sep-2011
Date of Web Publication24-Nov-2011

Correspondence Address:
D Sharma
Department of Lab Medicine, Max Super Speciality Hospital Phase 6, Mohali
Login to access the Email id

Source of Support: None, Conflict of Interest: None

DOI: 10.4103/0255-0857.90201

Rights and Permissions

How to cite this article:
Sharma D. Use of the microsoft excel for automated plotting of Levey Jennings charts. Indian J Med Microbiol 2011;29:448-9

How to cite this URL:
Sharma D. Use of the microsoft excel for automated plotting of Levey Jennings charts. Indian J Med Microbiol [serial online] 2011 [cited 2021 Jan 16];29:448-9. Available from:

Dear Editor,

Levey-Jennings charts (LJ charts) are crucial for the internal quality control of a clinical laboratory. [1],[2],[3],[4] I found a novel way for automated plotting of monthly LJ charts by using Microsoft excel.

In the LJ chart, quality control data is plotted in the form of a graph, in which time (dates) is represented on the x-axis and numerical value of quality control test is represented on the y-axis. The LJ chart contains several reference lines, including one reference line for mean and three reference lines on either side of the mean representing standard deviation limits (1SD, 2SD and 3SD). [1],[2]

To plot LJ chart, dates, and control test data were entered in the upper two rows of the excel sheet. In the subsequent seven rows (reference data rows), values of mean and SD limits (reference values) were entered. Each reference data row was dedicated to one reference value, and the same reference value was entered in all the cells of the row.

To do it conveniently, first the formulae for calculating the Mean ' = AVERAGE(B2: AF2)' and SD ' = STDEV(B2: AF2)' were entered in the control data row, after leaving two cells blank from the last data-cell of the month. (Here, B2 and AF2 are the references for the first and last cells of the control data. Here the letters are indicating the columns and numbers are indicating rows. One can change the cell reference to include or exclude the data cells in the formula.) After that, appropriate formulae were entered in the first cell of each reference data row for automated calculation of respective reference value (e.g. formula for mean + 1SD is ' = AI2 + AJ2'. Where AI2 is the reference for the cell containing formula for mean and AJ2 is the reference for the cell containing formula for SD. Similarly for calculating mean-2SD the formula is ' = AI2 - (2*AJ2)'. Here the reference of cell containing SD is multiplied by 2 and subtracted from the reference of cell containing mean. In the same way other formulae were given. Care was taken to enter the references correctly.). Then after, the reference of the first cell was entered in the subsequent cells of the respective reference data rows up to the last column of the month by using the fill handle feature of the excel sheet (e.g. ' = $B$3' is the reference of the first cell of 3 rd row which was filled in the subsequent cells of the row. Column A was used for entering data labels 'M, M+SD, M-SD, M+2SD, M-2SD etc' and cells of column B were assumed as first cells for the data). Fill handle is the lower right corner of any cell in excel sheet, when we click over it and drag it over a range of cells, excel automatically copies the same value into these cells.

After doing this, when the quality control test data was entered in the designated row, the excel sheet automatically calculated the values of mean and SD and filled the values of mean and SD limits in the respective reference data rows. With each new data entry, all these values were updated.

Now after selecting this data, the LJ chart was prepared by simply pressing the F11 key, and by selecting the chart type as line chart. Formatting of the chart was done for better presentation.

To increase the accuracy of SD, 15 extra columns were inserted on the left side of the first column of the data. Now in the initial 15 cells of control data row, data of 15 days of last month was fed. These 15 extra cells were included in the formula for calculating SD, but were not included in the chart. After inserting these extra cells, the references for SD calculating formula were adjusted to include these cells, references for other formulae were automatically adjusted by excel.

After plotting the LJ charts, reference data rows and columns containing last month's data were hidden by using the hide command of excel. By doing so the reference rows were not visible and worked in the background to plot the LJ charts. [Figure 1] shows one of the LJ chart plotted using this method.
Figure 1: Example of Levey-Jennings chart produced by the microsoft excel

Click here to view

 ~ References Top

1.Levey S, Jennings ER. The use of control charts in the clinical laboratory. Am J Clin Pathol 1950;20:1059-66.  Back to cited text no. 1
2.Henry RT. Use of the control chart in clinical chemistry. Clin Chem 1959;5:309-19.  Back to cited text no. 2
3.Westgard JO, Groth T, Aronsson T, Falk H, de Verdier CH. Performance characteristics of rules for internal quality control: Probabilities for false rejection and error detection. Clin Chem 1977;23:1857-87.  Back to cited text no. 3
4.Carroll TA, Pinnick HA, Carroll WE. Probability and the Westgard rules. Ann Clin Lab Sci 2003;33:113-4.  Back to cited text no. 4


  [Figure 1]


Print this article  Email this article


2004 - Indian Journal of Medical Microbiology
Published by Wolters Kluwer - Medknow

Online since April 2001, new site since 1st August '04