How to Do 6 Sigma Calculation in Excel – 2 Easy Methods

What Is 6 Sigma?

The term “6 Sigma” refers to a quality measurement that aims for absolute flawlessness. 6 Sigma is a methodical, computation approach for reducing errors in any process.

The calculation of 6 sigma involves two different approaches. One for discrete data, and another for continuous data.

Method 1 – Calculation of 6 Sigma with Discrete Data


The sample dataset showcases the Number of Units, Number of Opportunities, and Total Number of Defects in the C4:C6 range.

Calculation of 6 Sigma when Data is Discrete

Here, U, O, and D represent the Number of Units, the Number of Opportunities, and the Number of Defects.
To calculate the DPO, which is defects per opportunity from the previous data, use this generic formula.

DPO = D / ( U * O )
  • Create an output range in C8.

  • Select C8 and enter the following formula.
  • Press ENTER.

Calculation DPO to get 6 sigma in Excel

The universal formula to get the value of 6 Sigma from DPO is the following.

6 Sigma = – NORMSINV (DPO) + 1.5


  • Go to C10 and enter the formula below.

The NORMSINV function returns the inverse of the normal distribution.

  • Press ENTER.

6 sigma calculation in excel

Method 2 – Calculation of 6 Sigma With Continuous Data in Excel

This is a continuous dataset,  including  the Sample numbers and their corresponding Sizes in columns B and C.

Calculating 6 Sigma when Data is Continuous



The LSL (Lower Specification Limit) and USL (Upper Specification Limit) were applied in this dataset. The ideal size is 32. A size variation of +/-1 is assumed.  LSL and USL should be 31 and 33.

LSL and USL of dataset

  • Select F7 and enter the following formula.

Use the AVERAGE function to calculate the Mean. Here, C5:C24 represents the entire data range.

  • Press ENTER.

Calculation of Mean

  • Go to F8 and use the formula below.

The STDEV function calculates the standard deviation.

  • Press ENTER.

Calculation of standard deviation of dataset

You calculate Cpu and Cpl for this dataset. Based on the system’s upper specification limit, the Cpu is an indicator of its conceivable competence.  Cpl is based on the lower specification limit.

  • Select F10 and enter the following formula.
  • Press ENTER.

The value of Cpu is 0.07263.

  • Go to F11 and use the formula below.
  • Press ENTER.

The 6 Sigma Value would be the minimum value between the values of Cpu and Cpl divided by the standard deviation.

  • Go to F13 and enter the following formula.

The MIN function returns the minimum value from a range of cells.

  • Press ENTER.

Calculation of 6 Sigma when Data is Continuous

The value of 6 sigma for continuous data is displayed.

Read More:How to Calculate Sigma Level in Excel

How to Calculate 3 Sigma in Excel


  • Calculate the mean like before.
  • Select F5 and enter the following formula.

The VAR function returns the variance of a sample taken from population data.

  • Press ENTER.

How to Calculate 3 Sigma in Excel

  • Select F6 and use the following formula.

The SQRT function returns the square root of any number. The standard deviation is found from the square root of the variance.

  • Press ENTER.

calculating 3 Sigma in Excel

  • Go to F8 and enter the formula below.
  • Press ENTER.

Calculation of 3 Sigma in Excel


  • Add the Mean value with the 3 Sigma Value to get the High End Value.

  • You can extract the Low End Value by subtracting the Mean from the 3 Sigma Value.

Calculating 3 Sigma in Excel

Practice Section

Practice here.Practice section

Download Practice Workbook

Download the following Excel workbook and practice.

Related Article

<< Go Back to Calculate Sigma in ExcelExcel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF