EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Charts Candlestick Chart in Excel
Secondary Sidebar
Excel Functions
  • Excel Charts
    • Excel Data Visualization
    • Dashboard in Excel
    • Bullet Chart in Excel
    • Excel Animation Chart
    • Plots in Excel  
    • Interactive Chart in Excel
    • Surface Charts in Excel
    • Excel Calendar Template
    • Thermometer Chart in Excel
    • Box Plot in Excel
    • S CURVE in Excel
    • Venn Diagram in Excel
    • Project Management with Gantt Chart
    • Change Chart Style in Excel
    • 3D Scatter Plot in Excel
    • Box and Whisker Plot in Excel
    • Radar Chart in Excel
    • Control Charts in Excel
    • Dot Plots in Excel
    • Marimekko Chart Excel
    • Combo Chart in Excel
    • Contour Plots in Excel
    • Candlestick Chart in Excel
    • Map Chart in Excel
    • Pie Chart Examples
    • Excel Stacked Bar Chart
    • Excel Flowchart
    • Excel Types of Graphs
    • Excel Normal Distribution Graph
    • Excel Bell Curve
    • Legends in Excel Chart
    • Excel Chart Templates
    • Excel Organization Chart
    • Excel Doughnut chart
    • Excel 3D Plot
    • Excel Scatter Chart
    • Excel Line Chart
    • Excel Combination Charts
    • Excel Gauge Chart
    • Excel Pivot Chart
    • Excel Clustered Column Chart
    • Excel Dynamic Chart
    • Excel Stacked Column Chart
    • Excel Bubble Chart
    • Histogram Chart Excel
    • Excel Gantt Chart
    • Excel Stacked Area Chart
    • Excel Waterfall Chart
    • Excel Area Chart
    • Excel Clustered Bar Chart
    • Excel Column Chart
    • Excel Pie Chart
    • Comparison Chart in Excel
    • Pareto Analysis in Excel
    • Add a Secondary Axis in Excel
    • Grouped Bar Chart
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED COURSE
  • Excel Data Analysis Course
  • Excel for Marketing Course

Candlestick Chart in Excel

By Madhuri ThakurMadhuri Thakur

Candlestick Chart in Excel

Candlestick Chart in Excel (Table of Contents)

  • Introduction to Candlestick Charts in Excel
  • How to Create Candlestick Chart in Excel?

Introduction to Candlestick Charts in Excel

If you are not familiar with the Candlestick chart in excel, it is a chart that can be used to represent the stock data in excel. Analyzing stock data in a simpler manner is a big task. Because you always will have a lot of history getting generated. Fortunately enough, excel has inbuilt stock charts of different types for this purpose. One of those charts is the Open-High-Low-Close chart which is also known as the Candlestick chart. This chart is usually helpful in describing the stock, currency, capitals, derivatives movements over time. It allows the user to have a clean and simple, visually attractive output that is easy to understand as well. This graph allows you to have an analysis of stock and price patterns.

There are two types of Candlestick chart in Excel:

  • Open-High-Low-Close chart: It is a conventional candlestick chart that uses stock values arranged in order as Opening value, Highest Value, Lowest Value, Closing Value.
  • Volume-Open-High-Low-Close Chart: This is the chart that is similar to the first one. However, it adds the stock volume as well under graphical representation to have a better visualization output.

How to Create a Candlestick Chart in Excel?

Candlestick Chart in Excel is very simple and easy. Let’s understand how to Create Candlestick Chart in Excel with some examples.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You can download this Candlestick Chart Excel Template here – Candlestick Chart Excel Template

Example #1 – Candlestick Chart

Now, we will see how to create a conventional Open-High-Low-Close stock chart, a.k.a. Candlestick chart.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

Suppose we have data as shown in the given screenshot below. This data consists of an opening value, the highest value, the lowest value, and closing value for stock in US dollars.

candlestick chart in excel 1-1

Now, we will see how we can create a candlestick chart using this information step by step:

Step 1: Click on the Insert tab present on the uppermost corner of the ribbon. Select Recommended Charts under it.

candlestick chart in excel 1-2

Step 2: Once you click Recommended Chart option, it opens up with a list of charts suitable for your data. Click on the All Charts option, and you’ll see all the charts list. Select Stock in the left-most corner. It has all the charts associated with stock data.

candlestick chart in excel 1-3

Step 3: On the right-hand side, you’ll see all recommended charts under the stock charts section. Select Open-High-Low-Close chart under it, which is nothing but a second graph out of four.

candlestick chart in excel 1-4

Step 4: After doing this, right-click on the graph layout and choose the Select Data option from the list of options.

candlestick chart in excel 1-5

Step 5: A new pop up window will appear named Select Data Source. In this window, on the right-hand side, go to the Edit option under Horizontal (Category) Axis Labels.

candlestick chart in excel 1-6

Step 6: Again, a new dialog box will appear called Axis Labels. Under ”Axis label range:”, select the range of data which you wanted to be under axis. In this example, select A3:A7

candlestick chart in excel 1-7

Step 7: Click the OK button twice, once for Axis Labels and the second time for the Select Data Source window. Add chart title as “Candlestick Chart” under Chart Title option on graph generated. You should be able to see the graph output as below.

candlestick chart in excel 1-8

If you can see, this chart has boxes with lines extending upwards and downwards the boxes. If the box is empty, then the opening value is the same as the lower value for the stock, and you ideally have gained in your stocks. If the box is filled, the opening value is nothing but the upper bound/high value of the stock, and you ideally have been at a loss.

Example #2 – Candlestick Chart with Volume

Suppose you have data as shown below, which contains volume traded for stocks, opening value, highest value, lowest value, and closing value.

Chart with Volume 1

We will see step by step how to create a candlestick chart from this data with volume.

Step 1: Select the data from cell B3:B7 which needs to be included in the chart.

Chart with Volume 2

Step 2: Click on the Insert tab present on the uppermost corner of the ribbon. Select Recommended Charts

Chart with Volume 3

Step 3: On the right-hand side, you’ll see all recommended charts under the stock charts section. Select Volume–Open-High-Low-Close chart under it. Last graph in the list.

Chart with Volume 4

Step 4: Hit the OK button, and you will be able to see the initial graph layout as below.

Chart with Volume 5

Don’t get confused with the layout. It’s halfway done. We are yet to add the axis labels under it and format the same.

Step 5: Right-click on graph layout and choose the Select Data option from the list of options.

candlestick select data

Step 6: A new pop up window will appear named Select Data Source. In this window, on the right-hand side, go to the Edit option under Horizontal (Category) Axis Labels.

Chart with Volume 7

Step 7: A new dialog box will appear once again, called Axis Labels. Under ”Axis label range:”, select the range of data which you wanted to be under axis. In this example, select A3:A7.

Chart with Volume 8

Step 8: Click OK twice, and you should be seeing the graph layout as below:

Chart with Volume 9

Step 9: Change the chart title to “Candlestick Chart with Volume” and save the sheet.

Chart with Volume 10

This is how you create the Candlestick chart in Microsoft Excel with stock data for better visualization, and that is it from this article on how to create the Candlestick Chart in Microsoft Excel. Let’s wrap the things with some things to be remembered.

Things to Remember About Candlestick Chart in Excel

  • It is absolutely necessary to put your data column-wise in sequences. As for Candlestick Chart, the sequence in which data should be added in columns is Date, Open Value, High Value, Low Value, Close Value. The same is the case for Candlestick Chart with volume. If the data is not sequential, you will not be able to add the chart. Ideally, the name of the chart indicates the sequence of the data to be inputted.
  • Candlestick chart is known as Open-High-Low-Close chart or Volume-Open-High-Low-Close chart as well in excel. Actually, these are the conventional names for the graphs, and Candlestick Chart is the name that is used because the graph looks like candlesticks standing tall on the surface.
  • Every box has elongated tales with them. These are helpful in figuring out whether the stock value is going up or down, and eventually, you are making money or losing the same.
  • This chart is pretty simple to understand, and with naked eyes itself, the user can decide whether the stock is moving up or down.

Recommended Articles

This is a guide to Candlestick Chart in Excel. Here we discuss How to Create Candlestick Chart in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Map Chart in Excel
  2. Histogram Chart Excel
  3. Interactive Chart in Excel
  4. Surface Charts in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Candlestick Chart Excel Template

EDUCBA

Download Candlestick Chart Excel Template

EDUCBA

डाउनलोड Candlestick Chart Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more