Data Analysis 101

For some reason, figuring out where to begin seems to be everyone's biggest problem. Over the years, I've developed a simple method for looking at Excel-based data and deciding how to process it.

The Problem Solving Process

The problem solving process begins with a line graph of current performance. You then use the detail behind the performance data to create a Pareto chart.

1. Line Graph

Line graphs show performance over time. So I'm always looking for some orientation of the data that goes from first to last. This could be production sample numbers, dates, times, or whatever.

The problem solving process also implies that there is some kind of error, mistake or defect. So I'm looking for attribute data about defects (i.e., mistakes, errors, or out of spec). I'm not looking for variable data like money or cycle time or length or weight. Let's look at some sample defect data from the AIAG:

Data to be Analyzed

This example shows defects, over time, by sample. There were 62 samples taken and the number of defects counted. The natural inclination of most people is to subtract the defects from the sample size and show how many were produced correctly (e.g., 62 -2 = 60 good in the first sample), but this inclination draws your attention away from the problem. To solve a problem, you need to understand the problem.

The line graph or np chart of defects can be drawn easy with QI Macros. Select the data and click on QI Macros - np chart and enter 62 as the sample size:

Line Graph to Analyze Data

2. Pareto Chart

Since there are no red points or lines, the process is stable. The next step is to look for detail about the types of defects. If you look at the data, you'll want to look for totals by type of defect: undersize, cold weld, missing, or off-location.

Pareto chart data

Just select the titles in column A and then hold down the Control-key on your keyboard and select the totals in column AA as shown. Then you can use QI Macros to draw a pareto chart:

Pareto chart to Analyze Data As you can see, undersize contributes most of the problem. This is clearly a pareto pattern: one defect accounts for 91% of the defects. Because the part is undersized, it probably isn't possible to rework the part, so it may have to be scrapped.

If we had more detailed data about the undersize error, we might be able to draw another more detailed pareto chart of the data inside this one bar, but we don't. So we continue by creating an Ishikawa or fishbone diagram.

3. Ishikawa - Fishbone - Cause and Effect Diagram

Just click on QI Macros and choose Fill-in-the-blank Templates. Select Ishikawa diagram and then change the problem statement to match the pareto pattern:

Ishikawa Fishbone for root cause analysis

Your improvement story is now ready for root cause analysis. Knowing that the problem is undersized, you can more easily choose the right team members to help analyze the problem.

4. Move the Fishbone

If you want, you can move the Ishikawa template into the data workbook to continue developing your improvement story in one Excel Workbook.

Just click on EDIT-Move or Copy Sheet to get this dialog box. Then change the "To Book" to the main data workbook (in this case AIAG SPC.xls) and click OK:

Move or copy sheets in Excel 
This will move the template into the workbook along with the original data, np chart, and pareto chart:

Improvement Story in Excel

Barriers to Success

If you don't have the data to draw the pareto, can you use a check sheet to collect a week's worth of data about the type of defects? (Click on QI Macros-Fill in the Blanks Templates and select checksheet to get a template.) A week's worth of data will be more than enough to analyze a stable process.

Checksheet to Capture Data for Analysis

Analysis is Easy . . . if you know what to look for

The process is simple:

  1. Look for data of defects over time. Draw a line graph or control chart of performance over time.
  2. Draw a pareto chart of the types of defects found.
  3. Use the biggest bar of the pareto chart to create a fishbone diagram for root cause analysis. The problem statement should reflect the problem identified in the pareto chart. You now have enough insight into the problem to choose the right root cause analysis team.
  4. Analyze the root causes and verify that you have found the true root causes using data.
  5. Show performance before and after implementing the improvement using a control chart.
  6. Continue to monitor and improve the process.

I don't know why, but most people try to make it a lot harder than this. You don't have to. You can let your data lead you to dramatic improvements.


Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart
histogram
Histogram

QI Macros add-in for Excel makes creating smart charts a snap.