This is a simple check sheet. Usually done on paper. But with ubiquitous tablets being available, it can be done on Excel as well.
Photo credit: The U.S. Army / Foter / CC BY
Contents
The format
It is a simple grid format. Categories in rows and time (usually days) in columns. The grid just contains the number of observed defects in each category on each day. Here is a sample check sheet.
“Check sheet for motor assembly” by Daniel Penfielt Own work.
Licensed under CC BY-SA 3.0 via Commons.
As this was originally a paper form, a mark was put for every instance of a defect. Now, let us use Excel and do a better and simpler form.
To start with we will introduce Spinner controls to increase or decrease the value. Create one Spinner and copy paste it to all cells. Make sure the spinners are connected to the correct cells. This will need to be done manually and repeatedly. But it is a one-time effort. It simplifies data entry significantly.
Download sample check sheet file.
The sum will happen automatically vertically as well as horizontally. This is as simple as it gets. From portability point of view, this Excel format will work on Windows tablets only. On Android and iPad tablets, the Spinner control does not work. (Real work requires Windows and Office).
Difficult to analyze
Assume that multiple people are filling such sheets everyday. Now we want to analyze the data across a longer period of time – say a year (instead of a week), what will be the effort involved? Lot of copy paste and manual work.
What is the problem? The data is not in GOOD format. The solution is to convert it into GOOD format.
Convert data in good format
Good format simply means tabular data.
The data we have is in bad format called Cross-Tab. No problem.
We have seen two methods of converting cross-tab data to tabular:
Pivot Consolidation or Power Query.
You can use either method and convert it to good format – which should contain the following columns: Category, Date, Defect Count, Data Captured by, any additional columns
Further simplification is possible
The core information we need is the category and the defect count for a specific day. This can be done by creating a simple custom list in SharePoint (Office 365 sites). This list should have the following columns at the minimum: Date, Category, Count.
A SharePoint list can be used on any device on any browser. This will simplify data capture and eliminate the cost of writing custom applications for multiple devices.
Practical Applications
Once data is tabular format, you can use it for various purposes. The simplest form is to plot the items being counted across time and analyze the variability. The commonest way to do this is Run Charts – which we will cover in next article.
But that is not enough. If we have large amount of data, across multiple processes or locations, much more sophisticated analysis as well as forecasting can be done using Pivot Table, Power Pivot, Power View and Power Map.
***
