Quality 101: Tracking Gage Calibration with a Spreadsheet

Shown here is the conditional formatting of cell backgrounds. The operator clicks on Format to select the cell color. Source: Levinson Productivity Systems P.C.

Problems can arise in factories that use 3 by 5 cards to keep track of gages. In fact, calibration problems are one of the top sources of nonconformances during ISO 9000 audits. Commercial software is available for managing a calibration program, but for small shops an Excel spreadsheet may do just as well. Learn how to program a spreadsheet to remind personnel when calibrations are due.

The Spreadsheet

Table 1 shows the layout of a simple spreadsheet for tracking gages and their calibration status. Colored cell backgrounds serve as visual controls that make each gage’s status easily visible to the person who manages calibration control.

The current date in cell B3 is given by the today function in Microsoft Excel. The lead time is entered by the operator, and it is the desired advance warning prior to the due date. In this case, gages may be calibrated up to 30 days before their deadlines. DUE with a yellow background appears when the gage is within 30 days of its calibration deadline.

The gage status is calculated by the following formula in cell D6, which also was copied into the subsequent cells.

  • 2*(C6<$B$3) equals 2 if the due date is less than (i.e., earlier than) today’s date. If this is true, the gage calibration is overdue.


  • C6-$D$3<=$B$3 if the due date is within $D$3 days (in this case 30 days) of today’s date. C6 must also be greater than or equal to (later than or equal to) today’s date or else the gage is overdue. If both conditions are met, then 1*(C6-$D$3<=$B$3)*(C6>=$B$3) is equal to 1.

Quality System Considerations

The Excel spreadsheet is easy to program and is usable on any computer with Microsoft Office. Corel QuattroPro can be programmed similarly if the factory uses this software.

It is important to remember, however, that the spreadsheet is a quality record: a fourth-tier document under the control of a third-tier work instruction or second-tier procedure. For example, additional fields (columns) might be added to the spreadsheet to show the gage’s location, or the name of the person performing the calibration. It is vital to remember that a column heading is an implied instruction to enter information. Therefore, the spreadsheet and its controlling document must ask for exactly the same data to comply with ISO 9000’s provisions; conflicting instructions from controlling and subordinate documents constitute a nonconformance.

The same principle applies to how early the calibration may be performed; the controlling work instruction or policy must specify how many days ahead of deadline the gages may be calibrated. If this differs from gage to gage, a separate column must be added to give each gage a unique lead time. The cells in what is currently column D must then be reprogrammed to reference each gage’s lead time instead of a universal lead time. As a quality record, the calibration tracking spreadsheet also is subject to ISO 9000’s record retention provisions. The importance of backing up this file cannot be overemphasized, and the backup file must be kept in a place where it cannot be destroyed by an accident that erases the primary file, such as an offsite computer or in a computer media fire safe.


Did you enjoy this article? Click here to subscribe to Quality Magazine. 

Recent Articles by BNP Media Staff

You must login or register in order to post a comment.




Karen Spencer, Clinkenbeard's quality manager, discusses what makes the plant stand out, advice for other plants, and looks to the future.
More Podcasts

Quality Magazine


2015 May

The May 2015 edition of Quality Magazine includes articles on cloud technologies, depth gages, ISO 9001, digital inspection and cool new products.

Table Of Contents Subscribe

Topics to Talk About

What topics would you like to see Quality cover more?
View Results Poll Archive

Clear Seas Research

qcast_ClearSeas_logo.gifWith access to over one million professionals and more than 60 industry-specific publications,Clear Seas Research offers relevant insights from those who know your industry best. Let us customize a market research solution that exceeds your marketing goals.


Facebook2015_40 twitter_40px.png  youtube_40px.pnglinkedin_40px.png