How to Do an OHLC Chart in Excel

Microsoft Excel 2010 can create an “Open-High-Low-Close” chart using four series of values on a worksheet. Use the “Insert” command to convert this fluctuating data into a “Stock” chart often referred to as an “OHLC chart.” This chart resembles a bar or column chart displaying the range of values over time. Customize the chart with the “Chart Tools” for better readability.

Instructions

1. Open the Excel worksheet.

2. Type the data with four series of values in this order: open, high, low and close. For example, row 1 can include the five column headers: “Date,” Open,” “High,” “Low” and “Close.”

3. Click and drag the cursor to select the categories and data values you wish to convert to a chart.

4. Click the “Insert” tab on the command ribbon.

5. Click the arrow for the “Other Charts” button in the “Charts” group. A gallery of thumbnail images for charts and subtypes displays.

6. Click the “Open-High-Low-Close” image link in the “Stock” section. The “Open-High-Low-Close” button displays vertical lines and rectangles. The data converts to an embedded chart on the Excel worksheet. The “Chart Tools” ribbon displays three tabs: “Design,” “Layout” and “Format.”

7. Customize the chart with the commands on the “Chart Tools.” For example, the “Design” tab contains options for “Chart Layouts” and “Chart Styles.” Adjust the fonts with the “Font” commands on the “Home” tab for better readability.

Tips & Warnings

• You can also click the lower right arrow in the “Charts” group to launch the “Insert Chart” dialog box. Click the “Open-High-Low-Close” image link in the “Stock” section. Click “OK.”

References

• Investopedia: OHLC Chart
• Microsoft Office: Excel 2010, Available Chart Types, Stock Charts
• Microsoft Office: Excel 2010, Create a Chart from Start to Finish