Control Chart In Excel

What Is Control Chart In Excel?

The Excel Control Chart helps us analyze the progress or performance of a Process over a time. The Control Chart in Excel can be generated using a Line Chart, Scatter Chart, Stock Chart, etc. if we have upper and lower control limits for the data. The charts demonstrates if the Process details are within the upper and lower limits, i.e., controlled, or outside the lines.

For example, we will create a Control Chart in Excel for the values of three items. In the table, the data is,

Column A shows the Item.
Column B contains the Value.

Select cell range A1:B4, and insert the Control Chart or Line Chart from the charts group. We get the Comparison Chart in Excel, as shown below. We will get the below Control Chart in Excel.

What Is Control Chart In Excel?

The Control Charts understand the variations that are always present in processes. If the deviations are within the upper and lower control limits it indicates that the process is working. However, If the deviations are outside then that indicates improvement plans.
We can analyze the problem areas that need improvement and take corrective measures working towards possible solutions.
The charts can be generated using a Line Chart, Scatter Chart, Stock Chart, etc. Its components are the control line and upper and lower control line, and we can create it with the help of the calculations of the average and standard deviation of the data.

How To Create Control Chart In Excel?

We can Create Control Chart In Excel by inserting the required chart from the Charts Group in the Insert tab such as a Line Chart, Scatter Chart, Stock Chart, etc.

Let us understand the steps with the help of an example.

We will create a High-Low-Close Control Chart In Excel.

In the table, the data is,

Column A shows the Date.
Here, column B contains the High Value.
Column C contains the Low Value.
Column D contains the Close Value.

The steps to create the Control Chart in Excel are as follows:

Choose the table data, A1:D5 – select the “Insert” tab – go to the “Charts” group – click the “Insert Waterfall, Funnel, Stock, Surface or Radar Chart” option drop-down – select the “High-Low-Close” chart type from the “Stock” group, as shown below.
Click on the formed chart, the “Format” tab appears on the ribbon.
Select the “Format” tab – go to the “Current Selection” group – click the “Chart Elements” option drop-down – select the Series “High” option, as shown below.

We will get the High-Low-Close Control Chart with a shape outline of the High values, as shown below.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

We will insert Control Chart in Excel for some advanced scenarios.

Example #1 – Open-High-Low-Close Control Chart

We will create an Open-High-Low-Close Control Chart from the given data.

In the table, the data is,

Column A shows the Date.
Column B contains the Open Value.
Here, column C contains the High Value.
Column D contains the Low Value.
Column E contains the Close Value.

The steps to create the Control Chart in Excel are as follows:

Step 1: Choose the table data, A1:E5 – select the “Insert” tab – go to the “Charts” group – click the “Insert Waterfall, Funnel, Stock, Surface or Radar Chart” option drop-down – select the “Open-High-Low-Close” chart type from the “Stock” group, as shown below.

Step 2: Click on the formed chart, the “Chart Design” tab appears on the ribbon.

Select the “Chart Design” tab – select the desired styles from the “Chart Styles” group, as shown below.

The Open-High-Low-Close Control Chart is shown above.

Example #2 – Volume-High-Low-Close Control Chart

We will create the Volume-High-Low-Close Control Chart from the given data.

In the table, the data is,

Column A shows the Date.
Column B contains the Volume Value.
Here, column C contains the High Value.
Column D contains the Low Value.
Column E contains the Close Value.

The steps to create the Control Chart in Excel are as follows:

Step 1: Choose the table data, A1:E5 – select the “Insert” tab – go to the “Charts” group – click the “Insert Waterfall, Funnel, Stock, Surface or Radar Chart” option drop-down – select the “Volume-High-Low-Close” chart type from the “Stock” group, as shown below.

Step 2: Click on the formed chart, the “Chart Design” tab appears on the ribbon.

Select the “Chart Design” tab – select the desired styles from the “Chart Styles” group, as shown below.

The Volume-High-Low-Close Control Chart is shown above

Uses Of Control Chart In Excel

The Control Charts understand the variations that are always present in processes. If the deviations are within the upper and lower control limits it indicates that the process is working. However, If the deviations are outside then that indicates improvement plans.
The Control Charts help us analyze the problem areas that need improvement and take corrective measures working towards possible solutions.

Important Things to Note

Gather the required data and ensure it is correctly formatted.
Organize the data by labelling the required parameters or performance indicators, arrange the numbers in numerical order with 1 being the first.
Get the Upper and Lower Limits – The first cell of the Range column, select the cell and drag it down to copy the formula to the end of our data. In the Lower column, do the same, and in the Upper column.

Frequently Asked Questions

What does Control Chart do in Excel?

The Control Chart in Excel analyses how the data changes from time to time. It is also known as a Behavioural Chart or Shewhart Chart in Excel.

We use the Control Chart in Excel is in statistics in business, which helps the users analyze how any process changes. Its components are the control line and upper and lower control line, and we can create it with the help of the calculations of the average and standard deviation of the data.

How to create Control Chart in Excel?

We will create the Control Chart in Excel using the given data. The sales and purchase of items are shown in columns B and C.

FAQ

The procedure to create the Control Chart in Excel is,

Choose the table data, A1:C4 – select the “Insert” tab – go to the “Charts” group – click the “Insert Line or Area Chart” option drop-down – select the “Line” chart type from the “2-D Line” group, as shown below.

We get the Control Chart in Excel or the Line Chart in Excel, as shown below.

Why is Control Charts not working?

The Control Charts may not work,

• If the dataset is incorrectly selected.
• The chart is plotted without selecting the cell range.
• We select the wrong chart type which will display the wrong chart.

Download Template

This article must help understand the Control Chart in Excel examples. We can download the template here to use it instantly.

Control-Chart-in-Excel Download

Control Chart In Excel