You Should Use SPC
Software Instead of Excel
by Jeffery L. Cawley
During the past few years, Quality Digest has presented several articles on using Microsoft Excel for performing a variety of statistical functions. As the
authors of those articles pointed out, Excel gives users low-cost, no-frills SPC with software they probably already own--if they know how to go about using it. Of
course, several producers of specialty SPC software were quick to contact us and point out that, although Excel may indeed get the job done, most off-the-shelf SPC
packages will do it faster and with better graphics. So, in the interest of equal time …
et's say you need a detailed picture of a machine. Which would you rather use to create it, a pencil or a camera? The pencil is less expensive and you
already own one. But using it for your picture requires drawing skills, trial and error, and plenty of time. With the camera, you just point and click.
Remember this analogy when you're deciding whether to use Excel or a software package specifically designed for statistical process control. Sure,
you already own Excel, and you can do some types of SPC analysis with it. But do you have the necessary expertise, the patience for trial and error, and plenty of time?
Why would you put yourself to all that extra trouble when technically stringent, inexpensive SPC software can help you do the job quickly and easily? Here
are seven reasons SPC software is a better choice than Excel.
I Time traps
Let's face it, you always have more work to do than you have time in which to
do it. Excel takes a significant amount of time to set up for SPC use. So why use precious hours when SPC software sets up quickly and easily, and allows
you to do a huge amount of analysis with "point and click" efficiency? Instead of wrestling with the software, you can spend your valuable time making process improvements.
II Macro monkeys
Setting up Excel to automate your analysis involves a significant amount of
macro writing and programming. (Most people don't think of creating a macro as programming, but doing so is actually an inefficient way of coding formulas
and procedures.) You'll have to ensure that everything's done properly. Even if you know how to write macros in Excel, it's easy to make a mistake. Then
you'll have to thoroughly debug and validate the logic. With SPC software, the programming and logic is built in, and the better software packages even help you automate routine tasks with wizards.
III People problems
What happens when others need to use your SPC program? Everyone agrees
that the number one hurdle in successfully implementing SPC is getting employees--especially operators--to use it. But most spreadsheets don't show
users what they need to do to perform a task. SPC software helps overcome their natural resistance by making the interface easy, friendly and accessible,
even to those without advanced computer skills. Excel may be comfortable for computerphiles, but to newbies, it can be confusing and even threatening.
IV Training travails
Once you've overcome employees' resistance to using SPC, what resources
are you prepared to invest to train them? SPC software is not only easier to train people to use, but it comes with manuals, tutorials and online help files
that simplify training and even help users learn SPC techniques as they go along. Even if you put together an outstanding SPC application in Excel, how
would you like to continue training people to use it for the next five years? Or, if you don't plan to have other people using it, what happens when you go
back and look at your analysis in six months--will you clearly remember how you created the tables and arrived at your analysis?
V Reprogramming roulette
What if you don't care about any of the aforementioned reasons? You've
invested the time to learn and customize Excel. You've convinced and trained your people to use it. But now your customer changes specifications or adds a
new product, or your largest customer wants a new chart. With most SPC software, the data files are separate from the programming, so a few quick
modifications to the data files would have you right back in action. With Excel, your settings, macros and data are all in one place--you would have to rewrite.
Hope you have the time before your customer needs the product.
VI Gruesome graphics
One of the most important uses of SPC analysis is demonstrating to customers that your product meets specifications. This is where some SPC software
packages completely outdo Excel. It's a simple fact that high-quality graphics are more convincing than poor-quality ones, and most SPC software packages
are designed to produce attractive, easy-to-read charts in varying combinations. You can get the same effect in Excel, but it would take a tremendous amount of effort (as well as graphic design skills) to get what you
can achieve with one mouse click in SPC software.
VII Egregious errors
Programming Excel to do SPC can be rather complicated and requires substantial knowledge of statistical techniques. SPC software, on the other hand, has the logic built into the program, so you don't have to worry about
whether you've chosen the right control chart constants, written the right equation or placed the equation in the correct cell. Picture yourself presenting
your analysis to the management team. How confident of your results do you want to be before you present them or use them to make process changes or other key decisions?
Control charts: 14 steps vs. four
In the October 1997 issue of Quality Digest
, William Dorner laid out a 14-step operation for creating a set of control and X-bar charts in Excel. Let's compare this with NWA Quality Analyst, a commonly used SPC package.
In Dorner's Excel example, he asks you to do the following steps (abbreviated from the October 1997 article):
"Enter the following formula into cell F27: =AVERAGE(F2:F26) and then
copy it from F27 to G27. Then compute the control limits and center line, which involves putting the following in cell I2: =$F$27. Consult a table of
control chart constants to find that A2 is 0.577, and then [to calculate the upper control limit], in cell J2, type: I2+0.577*$G$27. Similarly, the lower
control limit will [go into] cell J2; type: =I2-0.577*$G$27. Then copy cells H2:J2 to cells H3:J26. For the X-bar chart, highlight cells F1:F26. Because the
control limits are not in contiguous columns, hold down the Control key as you also highlight cells H1:J26. Next use the ChartWizard to create a line chart of
the four data series. Eliminate the gray background and border around the chart. Change the UCL and LCL lines to bold dashed lines with no markers.
Change the center line to a lighter solid line with no markers. Change the X-bar data series to a bold solid line with large visible markers. Tweak the fonts, titles, headers, footers and margins as desired."
Here is a similar four-step operation in Quality Analyst:
Open the appropriate data set by clicking on the icon labeled OPEN AN
EXISTING DATA SET. Double-click on the data set you want to use. (Note that the software links with many types of databases and spreadsheets,
including Excel.) Select the variable you want to chart by clicking the heading of any column. Click the X-Bar/Range button.
The chart appears instantly, with full color and labels. Note that the chart includes both out-of-control conditions and pattern rule violations. Each
out-of-control subgroup appears as a red pound sign (#), while each pattern rule violation is indicated by a yellow asterisk (*).
That's it. Four clicks of the mouse and 15 to 30 seconds later, you're done. But what if you want to add a process capability histogram? Just click the
Histogram button on the toolbar. And if you want to compare all the charts together, a couple more clicks will give you the entire picture in high-quality graphics you can provide to senior management or your customers.
What if you want to apply your analysis to a different situation? With Excel, it's back to the 14 steps, making sure you entered everything correctly. With
SPC software, it's just another point-and-click.
Why struggle when you can point and click?
Although Excel's capabilities for analyzing data are vast, most of us have limitations on the time and energy that we can spend getting our jobs done.
These are resources that could otherwise be dedicated to making process improvements.
Of course, this comparison is not meant to criticize Excel, which is a powerful
spreadsheet program. In fact, some SPC software is designed to connect directly with Excel to mine the data it contains. So if you need to store data in
Excel or use it for a special analysis, you can make the most of both types of software. But it's not fair to compare Excel with a tool that is specifically designed to make SPC analysis painless and easy.
So why put yourself through a lot of exasperation? The do-it-yourself mentality is a trap when it comes to reinventing already-existing software
applications that are high-quality, flexible and affordable. You may already own Excel, but the purchase price of SPC software is more than reasonable
when you consider the reduction of errors and savings in time, training and usage. So work smarter, not harder. Put down that pencil and go get the right tool for the job.
About the author
Jeffery L. Cawley is vice president of Northwest Analytical Inc., maker
of NWA Quality Analyst offline SPC software and NWA Quality Monitor online software. Northwest Analytical Inc. (NWA) provides analytical software for understanding processes and improving quality. NWA's suite
of quality management software also includes NWA Quality Analyst Web Server and NWA LIMS (an information management system for the analytical laboratory). Contact Cawley at email@example.com .