Control Chart Excel Template |How to Plot Control Chart in Excel | Download Template

Control Chart Excel Template |How to Plot Control Chart in Excel | Download Template:

Hi! Reader, today we will guide you on how to plot control chart in Excel with an example. To take more concentration on Process Improvement, control chart always takes vital rules to identify the Special causes and common causes in Process Variation. Control Chart Excel Template is available here; just download it by click on the below link.

Download Control Chart Excel Template

Control Chart Excel Template

[Figure 1-X- Bar Control Chart Excel Template]

control chart excel

[Figure 2-R-Control Chart Excel Template]

Control Chart is a graphic representation of a characteristic of a process, showing plotted values of some statistic gathered from that characteristic, a centerline, and one or two control limits. It has two basic uses as a adjustment to determine if a process has been operating in statistical control, and to aid in maintaining statistical control.

Control Chart Approach for Continual Process Improvement:
  • Data Collection.
  • Control.
  • Analysis and Improvement.
control chart excel
  • Data Collection:-
  1. To Collect Data and Plot the Control Chart.
  • Control:-
  1. Calculate control limits from process data.
  2. Identify Special Causes of Variation and Act upon them.
  • Analysis & Improvement:-
  1. Quantify Common Cause Variation, take action to reduce it.

You will also like to read theCAPA Process

7QC Tools for Problem Solving | What are 7 QC Tools

How to Plot Pareto Chart in Excel ( with example)

How to Create Control Chart Excel Template| Step by Step Guides (X-Bar & Range Chart) with Example:

Step-1: Collect The Data day wise/shift wise.

control chart excel

As you can see in the above figure, we have collected data with sample size 5 for A-Shift with frequency (5 samples per 2 hours). So we are having only one shift data for 5 days. Totally 100 number observations.  You are supposed to collect the data as per Control Plan or Quality Assurance Plan.

Step-2: Select the Data types and applicable Control Chart.

So we are having variable type data and the sample size is 5. Hence the applicable Chart is Average and Range Chart (X-Bar & Range).

Step-3: According to data type and Sample size, presently we are going to plot the X-Bar & R-Chart. So individually we will plot both chart (X-Bar Chart & Range Chart). First we will plot X-Bar Chart and then R-Chart.

3.1 X-Bar Chart:
Control Chart Excel

 Before we start, just you go through the green highlighted terms in above figure as: [1] Average

[2] X-Double Bar means average of average. [3] Standard Deviation. [4] UCL. [5] LCL.

Calculation:

[1] Average:
Control Chart Excel

Make sure that your attention is now at right side corner of above figure. To calculate the average value of individual subgroup size. You have to type as (=average)and then double click on average function and next to select the sample value from x1 to x5.

[2] X-Double Bar: After calculating the Average value of all Subgroup (Individual Date wise), now we have to calculate the average of Average (Average of X-Bar).  

[3] Standard Deviation: Standard Deviation of Average (X-Bar),

control chart excel

Type as (=Stdev) and select all X-Bar Data to Calculate the Std. Dev. of Average.

[4] UCL: 

UCL=X Double Bar +3*Sigma

UCL= X Double Bar +3*Standard Deviation

For calculation the UCL in excel, put the above formula.

[5]LCL:

LCL=X Double Bar -3*Sigma

LCL= X Double Bar -3*Standard Deviation

Use above Formula in excels.

3.11 Plot X-Bar Chart: This is the last Step to plot the X-Bar Chart by using Line Graph in Excel, follow the below steps as:

control chart excel
control chart excel

Simply Follow Sl. No.1 to 4.

In Sl.No.1, Select X-Bar, X-Double Bar, UCL, LCL and then select Insert Option and next to Line Chart. After selecting the Line Graph/Chart, The X-Bar Control Chart Excel Template will be ready as below.

control chart excel
3.2 Range Chart:
control chart excel

To Plot the R-Control Chart, we have to calculate the [1] Range. [2] R-Bar (Average of Range). [3]UCL. [4]LCL.

[1] Range: R=Max. Value – Min. Value of Subgroup.

control chart excel

[2] R- Bar (Average of Range): Put the excel formula of average.

[3] UCL:

UCL= D4 x R-Bar

UCL= 2.114 x R-Bar Value of individual Subgroup. (Note for Subgroup Size 5, D4=2.114).

Use this formula in Excel to calculate the UCL.

[4] LCL:

LCL=D3 x R-Bar

LCL=0 (Note Foe subgroup size 5, D3=0)

Simply put the “0” in excel sheet.

3.22 Plot R-Chart: Just follow the step 1 to 3, and select the line chart.

control chart excel

In step-1, you have to select the “Range, R-Bar, UCL, and LCL” simultaneously and then select the Line Chart, after selecting the line chart R-Control Chart Excel Template will be ready as below 

Control chart excel
FAQ:

Q1: What are control chart rules?

A1: Read the full articles as What is SPC”.

Q2: How to add upper and lower control limits in excel?

A2: Carefully read the aforesaid Articles.

Q3: How to create a control chart in excel 2013?

A3: Step by Step guide is described in above with Statistical process control charts examples. Please go through it.

Q4: How to create a six sigma control chart in excel?

A4: Control chart are classified into two types as [1] Variable type and [2] Attribute Type. Both two types are further classified into several as

[1]Variable types

  1. X and MR Chart
  2. X-Bar and Range
  3. X-Bar and S

[2] Attribute Chart

  1. np-chart
  2. p-chart
  3. u-chart
  4. c-chart

In the above articles we have described only on how to create X-bar and Range type Control Chart in excel with process control chart example. As you can see all these above types of control chart are used in six sigma projects but the applicable of chart depends on Data type and Subgroup size (Sample size).

Q5: How to calculate upper and lower control limits (ucl & lcl) in excel?

A5: For X-Bar Chart-UCL: 

UCL=X Double Bar +3*Sigma

UCL= X Double Bar +3*Standard Deviation

For calculation the UCL in excel, put the above formula.

LCL:

LCL=X Double Bar -3*Sigma

LCL= X Double Bar -3*Standard Deviation

Use above Formula in excels.

For R-Chart:

UCL:

UCL= D4 x R-Bar

UCL= 2.114 x R-Bar Value of individual Subgroup. (Note for Subgroup Size 5, D4=2.114).

Use this formula in Excel to calculate the UCL.

LCL:

LCL=D3 x R-Bar

LCL=0 (Note Foe subgroup size 5, D3=0)

Simply put the “0” in excel sheet.

Q6: What are the types of control charts?

A6: [1] Variable types

  • X and MR Chart
  • X-Bar and Range
  • X-Bar and S

[2] Attribute Chart

  • np-chart
  • p-chart
  • u-chart
  • c-chart
Download Free Template.
Corrective and Preventive Action Format
CAPA FORMAT

Thank you for reading…….keep visiting Techiequality.Com

6
Leave a Reply

avatar
6 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
0 Comment authors
Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
trackback

[…] Control Chart Template Risk register Format […]

trackback

[…] Case Study CC Excel Template Download Risk Register Format Concept of CAPA QMS Risk […]

trackback

[…] Control Chart is the popular tools to identify the process Variations and causes (Common or Special Cause). If you would like to know more about different types of Control Chart then read “What is SPC?”. You will also like to read on “how to plot a control chart in Excel’. […]

trackback

[…] Control Chart by Minitab-18 Control Chart Excel Template How to Create histogram in excel CAPA […]

trackback

[…] Control Chart Template 8D Format with Example OEE Calculation Template Root Cause Analysis […]

trackback

[…] RCA Risk Management Online registration of net-banking Mobile Number Portability Airtel Prepaid to Postpaid CC Template […]