Control Charts in Excel

What are Control Charts in Excel?

Control chart is a type of chart which is used to analyze how the data changes in time to time, it is also known as behavioral chart or Shewhart chart in excel, it is used in statistics in business which helps user or the viewers to analyze how any process changes, its components are control line and upper and lower control line and the means of making it is with the help of calculations of average and standard deviation of the data.

“Control Chart” is the visual graph used to analyze the change of process over a period of time. To plot this excel graph, we need to arrange the data in chronological order with current data sets. To create a control chart in excel, we need to have what is the average of the data, what is the upper control limit of the data, and what is the lower control limit of the data.

All these lines or drawn by using historical data sets, and we compare the current set of data with average, upper limit, and lower limit lines in the graph to draw conclusions whether the process variation is consistent or inconsistent and also to understand whether the data is unpredictable. If all the data points come within the control limit lines (averageAverageThe average value represents the set of data values; the average from the whole data is calculated by adding all the set values and dividing them by the number of values. Average = (a1 + a2 + …. + an)/nread more, upper, lower), then variations may be due to general cause and process said to be in control. Similarly, if the data points are plotting above or below the limit line, then variations may be due to special cases, and process said to out control”.

Control Charts in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Control Charts in Excel (wallstreetmojo.com)

Types of Control Charts in Excel

We have four kinds of Control Chart with excel. Below are the types of Control Chart with excel.

  1. High – Low – Close
  2. Open – High – Low – Close
  3. Volume- High – Low – Close
  4. Volume – Open – High – Low – Close

Each chart requires a specific set of data. Let us have a look at how each type of control chart is created.

You can download this Control Chart Types Excel Template here – Control Chart Types Excel Template

Type #1 – High – Low – Close Control Chart

To create this type of Control Chart, we need to have three sets of data, i.e., what is the high price in the day or high-value point, what is the low-value point, and what is the actual closing number.

  1. Copy and paste the below data sets to your worksheet.

    Control Charts Types Example 1

  2. Once you have copied the data to the worksheet, select the data, and go to the Insert tab.


    Example 1.1

  3. In the insert, go to charts area > Stock Chart > High-Low-Close Chart.

    Control Charts Types Example 1.2

  4. We will have a chart below.

    Example 1.3

  5. We cannot identify anything with this chart, so we need to color each line in our own way. By selecting the chart to go to “Format” > Current Selection > and from the drop-down, select “Series High.”

    Control Charts Types Example 1.4

  6. This would select the high line from the chart. So, now again, under FORMAT, choose “Shape Outline” and choose the color as per your wish.

    Example 1.5

  7. Now we can see the High line is colored with a green color.

    Control Charts Types Example 1.6

  8. Now we need to repeat the same task for the other two lines as well, i.e., “Low” & “Close.”

    Example 1.7

So, this shows how “High, Low, and Close” points fared in each day.

Type #2 – Open – High – Low – Close Control Chart

To create this, we need a copy table to the worksheet.

Control Charts Types Example 2

This time insert Open – High – Low – Close chart.

Control Charts Types Example 2.1

Now we will have the below chart.

Control Charts Types Example 2.2

Type #3 – Volume – High – Low – Close Control Chart

To create this, we need to have one additional data point, i.e., “Volume” data.

Control Charts Types Example 3

Copy the above table to worksheet; this time, insert Volume – High – Low – Close chart.

Example 3.1

Now we will have the below chart.

Control Charts Types Example 3.2

Type #4 – Volume – Open – High – Low – Close

The final type of control chart is this. For this, we need one more additional data, i.e., what is the opening value of the data point.

Control Charts Types Example 0.4

Copy the above table to worksheet; this time, insert Volume-Open-High-Low-Close chart.

Control Charts Types Example 4.1

So, now we will have below the designed chart below.

Example 4.2

You can do all the settings, formatting, and many other things as per your wish.

Recommended Articles

This has been a guide to Control Charts in Excel. Here we discuss the top 4 types of control charts in excel along with how they are created with practical examples and a downloadable excel template. You can learn more about excel functions from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>