• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Control Charts in Excel

Home » Excel » Excel Charts » Control Charts in Excel

By Harshada Khot Leave a Comment

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 Charts in Excel (Table of Contents)

  • What is the Control Chart?
  • Types 
  • How to Create?

What are Control Charts in Excel?

“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 (average, 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”.

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.

Top 4 Types of Control Chart in Excel

We will see examples for each type in the below section of the article.

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.

Control Charts Types Example 1

Copy and paste the above data sets to your worksheet.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

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

Example 1.1

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

Control Charts Types Example 1.2

We will have a chart like below.

Example 1.3

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 drop down select “Series High”.

Control Charts Types Example 1.4

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

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

Control Charts Types Example 1.6

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.

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

To create this we need 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 below chart.

Control Charts Types Example 2.2

Example #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 above table to worksheet, this time insert Volume – High – Low – Close chart.

 Example 3.1

Now we will have below chart.

Control Charts Types Example 3.2

Example #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 data point.

Control Charts Types Example 0.4

Copy 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 designed chart like the 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 chart in excel along with how they are created with practical examples and downloadable excel template. You can learn more about excel functions from the following articles –

  • Animate Excel Chart
  • 3D Scatter Plot in Excel
  • Excel Marimekko Chart
  • Create an Excel Template of Family Tree
  • Create Tornado Chart in Excel
  • Create Funnel Chart in Excel
  • Create Scatter Plot in Excel (Chart)
  • Create Doughnut Chart in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Excel Charts

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download Control Chart Types Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More