How to create stock chart in Excel: Guide

How to create stock chart in Excel

Quick read: This guide explains how to create stock chart in Excel for US equities and cryptocurrencies, what data you need (OHLC, HLC, VHLC), how to import and prepare data, step‑by‑step chart creation, customization, indicators, automation options and common troubleshooting. Following the steps below helps you visualize price and volume trends clearly and build reusable templates.

As of 2024-12-31, according to Microsoft Support, the STOCKHISTORY function is available to Microsoft 365 users and can simplify historical equity imports into Excel.

Why create stock charts in Excel

If you want to create stock chart in Excel you gain a flexible way to visualize Open, High, Low, Close (OHLC) price action, candlesticks, and volume together. Excel stock charts are useful for: daily performance reporting, preparing charts for research notes, prototyping indicators (moving averages, Bollinger Bands), and building dashboards that combine price, volume and custom metrics. For crypto traders and analysts, Excel enables working with OHLC data from multiple exchanges and applying custom aggregation or timezone normalization before visual display. Bitget users can export trade-history and market CSVs for direct use in Excel and use Bitget Wallet data to reconcile holdings before building charts.

Types of stock charts supported by Excel

When you set out to create stock chart in Excel, know the basic visual types Excel supports and when to use each.

• Open-High-Low-Close (OHLC): Traditional bars that show opening and closing prices with ticks indicating open/close and a vertical line for the high-low range. Use OHLC for intraday and daily equity analysis where open price matters.
• High-Low-Close (HLC): Omit the open value; the bar shows only the high-low range and a tick at close. Use HLC when open values are unavailable or not meaningful (aggregated endpoints).
• Volume-High-Low-Close (VHLC): Adds volume bars together with the price bars. Useful when you want volume context next to price in a combined chart.
• Candlestick-style: Excel shows candlestick visuals (filled/unfilled boxes) that represent open and close with color coding — commonly used for quick up/down visual cues. Use candlesticks for price pattern reading and technical analysis.

Visual differences and when to pick:

• If you have full OHLC, prefer OHLC or candlestick for clarity.
• If you lack Open, use HLC.
• If you need volume shown, use VHLC or add volume as a secondary series.

Required data and data layout

To successfully create stock chart in Excel you must supply properly ordered columns and consistent types. Excel expects a particular column order to map data automatically when you insert a stock chart.

Minimum columns (order matters for automatic mapping):

1. Date (or time) — formatted as Excel Date/Time, sorted ascending (chronological).
2. Open — numeric. Required for OHLC and candlesticks.
3. High — numeric.
4. Low — numeric.
5. Close — numeric.
6. Volume — numeric (optional; required for VHLC).

Notes on format and behavior:

• Date column: use real Excel date/time values (not plain text). If dates are text, convert using DATEVALUE or Power Query.
• Chronology: Excel expects earliest to latest in ascending order; some users prefer descending for dashboards but mapping can break — stick to ascending.
• Numeric types: ensure there are no stray text characters (commas in CSVs from some locales); convert columns to Number format.
• Missing fields: If Open is missing, Excel will only allow HLC charts. If Volume is missing you cannot build VHLC without adding it as a separate series.

How missing or misordered fields affect chart type selection:

• Missing Open -> use HLC or provide a placeholder derived from previous Close.
• Missing Close/High/Low -> chart will fail or plot incorrectly; ensure completeness before charting.

Example data tables

Below are short sample tables formatted to illustrate correct column order.

OHLC example (daily):

| Date | Open | High | Low | Close | Volume | |------|------|------|-----|-------|--------| | 2025-01-02 | 132.50 | 135.20 | 131.80 | 134.10 | 5,200,000 | | 2025-01-03 | 134.10 | 136.00 | 133.00 | 135.50 | 4,800,000 |

HLC example (no Open):

| Date | High | Low | Close | |------|------|-----|-------| | 2025-01-02 | 135.20 | 131.80 | 134.10 | | 2025-01-03 | 136.00 | 133.00 | 135.50 |

VHLC example (volume added):

| Date | Volume | High | Low | Close | |------|--------|------|-----|-------| | 2025-01-02 | 5,200,000 | 135.20 | 131.80 | 134.10 | | 2025-01-03 | 4,800,000 | 136.00 | 133.00 | 135.50 |

(When using VHLC, note the Volume column position: Excel expects the volume column first for that specific stock chart subtype if you use the built-in VHLC mapping.)

Preparing and importing price data

There are several practical ways to get price data into Excel to create stock chart in Excel. Choose based on your refresh needs and data source.

1. CSV import (manual)

• Export CSV from an exchange, broker, or data provider. In Excel: Data → Get Data → From File → From Text/CSV. Verify delimiter and regional settings (decimal separator). Convert date column to Date type.

1. Power Query (From Web / From API)

• Data → Get Data → From Other Sources → From Web. Use REST API endpoints that return JSON/CSV. Use Power Query transformations to parse, rename, change types, remove duplicates and pivot/unpivot. Schedule refreshes in Excel if you have Office 365 and configured credentials.

1. Excel STOCKHISTORY function (equities, Microsoft 365)

• Use STOCKHISTORY to pull historical price series for supported equities directly into a sheet (subject to availability in your Excel version). Example formula: =STOCKHISTORY("MSFT", "2025-01-01", "2025-02-01", 0, 1, 0, 1, 2, 3, 4) — returns date and requested columns. STOCKHISTORY is firmware dependent and may not support crypto tickers.

1. Cryptocurrency data retrieval

• For crypto you typically use exchange APIs or third-party data providers. Export CSV from Bitget account trade and market data pages for on-chain history or use Bitget API (or data endpoints exposed by Bitget) to pull OHLCV. Import via Power Query or a scheduled script that writes to CSV.
• If you prefer no-code, Bitget’s CSV exports for market data and account history are easy to import into Excel.

Practical tips for API imports:

• Use ISO 8601 timestamps and convert to local timezone if needed.
• Pull data at the aggregation you need (minute/hour/day). If the API only provides ticks, aggregate using Power Query group operations.
• Authenticate securely when using API keys; avoid embedding keys in shared workbooks.

Handling crypto-specific issues

Cryptocurrency data differs from equities in ways that affect how to create stock chart in Excel:

• 24/7 markets and irregular hourly spikes: Crypto trades nonstop; choose aggregation (minute/hour/day) thoughtfully to remove noise.
• Multiple exchanges, price discrepancies: Crypto markets are fragmented; decide on a primary source (Bitget recommended) and document it. When combining exchange data, normalize symbol naming and handle liquidity differences.
• Missing open/close for aggregated buckets: For intervals where no trades occurred (rare for major tokens), derive open from previous close or mark as N/A. Be consistent—Excel chart types may not accept blanks.
• Timezone handling: Convert all timestamps to a single timezone (UTC recommended) before charting to maintain consistent daily candles.

Bitget recommendations: use Bitget market CSV exports or Bitget API as a primary source for OHLCV to reduce cross-exchange discrepancies and to have consistent liquidity representation.

Step‑by‑step: create a stock chart in Excel

Follow these steps once your data is prepared to create stock chart in Excel.

1. Prepare the worksheet

• Ensure your table columns are correctly ordered and typed. Remove totals or subtotal rows inside the data range.

1. Select the source range

• Select the cells containing your Date and price columns. Exclude the header row if using some versions of Excel — selecting headers is OK in newer Excel versions but safe practice is to select pure data first.

1. Insert the stock chart

• Excel Ribbon: Insert → Charts group → Other Charts (or the Stock chart icon) → choose the appropriate stock chart type (Open-High-Low-Close, High-Low-Close, Volume-High-Low-Close, or Candlestick).

1. Excel behavior and mapping

• Excel will attempt to map the selected columns to date, high, low, open, close, and volume based on their order. If the mapping is wrong, use Select Data to adjust series order.

1. Add a date axis

• If dates appear as category labels instead of an axis, format axis type to Date axis. Right-click axis → Format Axis → Axis Type → Date axis.

1. Add volume (if not using VHLC)

• To add volume manually: copy the volume column and paste it into the chart as a new series, then change the series chart type to Column and place it on a secondary axis. Format axis scale so volume bars are readable.

1. Final touches: titles, legend, colors

• Add a descriptive chart title, adjust candlestick fill and border colors for up/down, and add gridlines as needed.

Version differences (Excel 2013, 2016, 2019, 365)

• Excel 2013: Stock chart types are present but chart gallery layout differs. Use Insert → Other Charts to find stock types. STOCKHISTORY is not available.
• Excel 2016/2019: Improved chart gallery and better formatting panes. Power Query (Get & Transform) integrated in 2016/2019 updates. STOCKHISTORY still limited to Microsoft 365.
• Excel 365 (Microsoft 365): Best experience — STOCKHISTORY function available, dynamic arrays, and smoother data connections. Chart options are in Insert → Charts → Recommended Charts → All Charts → Stock.

If you don’t see Stock chart options, ensure you have at least 4 columns of numeric price data in proper order and correct data types.

Customizing and formatting the chart

After you create stock chart in Excel, customization makes it readable and publication-ready.

Key customization steps:

• Title: Click the chart title and enter a concise title with source and timeframe.
• Axes: Format date axis with appropriate major/minor units (days, weeks) and Y axis with number format (two decimals or currency). Use fixed min/max if you need consistent comparison across charts.
• Candlestick colors: Right-click a candle → Format Data Series → Fill & Line. Set fill to transparent for down candles or choose contrasting colors (e.g., green for up, red for down). Use consistent colors across dashboards.
• Line/stick widths: Thicker sticks improve visibility for screen presentations; thinner for dense intraday charts.
• Gridlines: Remove excessive gridlines; keep a light horizontal gridline for reference.
• Data labels: Avoid crowding; use labels for key dates only. Alternatively add a data table beneath the chart for exact values.

Format the Y axis to avoid overlapping series:

• If the chart includes volume on the same axis it will dwarf price series. Use a secondary axis for volume and set its scale manually (for example, volume axis max set to a round number) so the price series remains legible.

Adding volume and secondary axes

To add volume as a separate series and scale it:

1. Right-click the chart and choose Select Data.
2. Click Add, pick Volume column for Values, set the series name to "Volume".
3. Once volume is added, right-click the volume series in the chart → Change Series Chart Type → select Clustered Column for the Volume series and set it to Secondary Axis.
4. Right-click the secondary vertical axis → Format Axis → set appropriate bounds and number format (for example, show millions with custom format).
5. Reduce column gap width for visual compactness and set transparency so price bars are not obscured.

Tip: Use VHLC stock chart type if you have volume in the correct position; Excel will automatically handle the volume display for some versions.

Adding technical overlays and indicators

Excel is ideal for prototyping indicators and plotting them over price when you create stock chart in Excel.

Common overlays and how to add them:

• Moving averages (SMA/EMA): Compute SMA using AVERAGE over a rolling window (use Excel’s AVERAGE with OFFSET/INDEX or use dynamic ranges). For EMA use the recursive formula: EMA_today = (Price_today * k) + (EMA_yesterday * (1-k)), where k = 2/(N+1). Add the MA series to the chart as a Line chart type on the primary axis.
• Bollinger Bands: Compute SMA and rolling standard deviation (STDEV.P or STDEV.S), then create Upper = SMA + (k * SD) and Lower = SMA - (k * SD). Plot Upper and Lower as line series; optionally use a shaded area via an area series between them.
• Trendlines: Use Excel’s built-in trendline for single-series regression (right-click series → Add Trendline). For more advanced trends (channel, multiple segments) compute values in cells and add as line series.

Best practices:

• Use separate columns for each indicator and label headers clearly.
• Keep axis scales consistent — indicator lines should align to the price axis; do not plot price and indicator on different scales unless intended.
• Use lighter colors for overlays to avoid obscuring candles; thicker widths for key indicators like 50/200 SMA.

Advanced techniques and automation

To maintain repeatable workflows for how to create stock chart in Excel consider automation for data refresh and chart updates.

• Power Query automated refresh: Build your import and transforms in Power Query and enable workbook refresh on open or schedule via Excel Online/Power Automate. Power Query preserves column types and sorting rules so chart mapping remains intact after refresh.
• VBA macros: Write a macro that clears old data, imports new CSV, recalculates indicators, and refreshes the chart. Use named ranges and table objects (ListObject) so the chart references auto-expand.
• Third‑party add-ins: Use vetted add-ins to streamline live data pulls and chart creation. For crypto workflows, Bitget-related tooling or certified connector add-ins can simplify authenticated API calls and CSV exports. Always vet add-ins for security and compliance.

Automation tips:

• Use structured Tables (Insert → Table) for data ranges; charts bound to table columns will auto-extend when new rows are appended.
• Store API keys securely (Windows Credential Manager or environment variables) and avoid embedding keys in shared files.

Common problems and troubleshooting

When attempting to create stock chart in Excel you may encounter issues. Here are frequent problems and fixes:

1. Chart shows wrong series mapping

• Cause: Column order or selection incorrect. Fix: Use Select Data → Edit Series and map correct ranges manually; ensure columns are numeric.

1. Dates plotted as categories vs. axis values

• Cause: Date column stored as text. Fix: Convert to Date using DATEVALUE or re-import and set type to Date in Power Query. Then change Axis Type to Date axis.

1. Missing data points after refresh

• Cause: Source query changed column order or removed column headers. Fix: Make Power Query transformations robust (rename columns explicitly) and use column names rather than positional references where possible.

1. Overlapping series or unreadable volume

• Cause: Volume on primary axis. Fix: Move volume to secondary axis and rescale; use VHLC built-in if available.

1. Candlestick colors not updating

• Cause: Excel reset formatting after data refresh. Fix: Create a chart template after formatting (right-click chart → Save as Template) and apply it after refresh.

Best practices for financial charts in Excel

To produce reliable, repeatable charts when you create stock chart in Excel follow these recommendations:

• Select appropriate data frequency: intraday analysis needs minute data; strategy backtesting commonly uses daily closes.
• Timezone consistency: standardize timestamps to UTC or a documented local timezone.
• Document data source and refresh cadence on the chart or workbook metadata.
• Use table objects and named ranges for robust chart references.
• Avoid using volatile Excel functions that force frequent recalculations in large datasets.
• For live trading or low-latency needs prefer dedicated charting platforms; Excel best suits analysis, reporting and prototyping rather than live order execution.

Bitget users: for production charting tied to trading consider using Bitget charts and APIs; for research and reporting Excel remains a strong choice.

Examples and templates

Starter templates to build quickly:

• OHLC template: date + open + high + low + close + volume table with a stock chart and a 20/50 SMA overlay.
• Candlestick + Volume template: candlestick chart with volume on secondary axis, 14‑day RSI calculated below.
• Dashboard sample: multiple charts (daily, weekly, monthly) and a summary table of returns and volatility.

Where to find walkthroughs and example files:

• Check Microsoft Support for Excel chart tutorials and example files available in the official documentation. Also search for video walkthroughs that demonstrate STOCKHISTORY and Power Query workflows. For crypto-specific CSVs and API examples, use Bitget’s developer documentation and CSV exports to seed templates.

Security, data licensing, and reliability considerations

When pulling external price data to create stock chart in Excel, be mindful of:

• API terms of service: ensure your usage (rate limits, storage, redistribution) complies with the provider’s license.
• Data reliability: cross-check samples with official exchange records. For critical decisions do not rely solely on free/unvetted sources.
• Authentication: treat API keys like passwords. Do not store them in plain text in shared workbooks.
• Privacy: avoid embedding PII in chart files you share publicly.

For crypto, free aggregated feeds can be useful for research but may diverge from the exchange execution price. Prefer Bitget market data for consistency if using Bitget for trading.

References and further reading

• Microsoft Support: Excel chart and STOCKHISTORY documentation (refer to Microsoft for function availability and examples).
• Power Query documentation and tutorials for Web/API imports and transformations.
• Bitget developer resources and CSV export guides for crypto OHLCV and trade history (check Bitget platform resources for exact API endpoints and data formats).

As of 2024-12-31, Microsoft Support documents the availability of STOCKHISTORY for Microsoft 365. For exchange-specific metrics (market cap, daily volume, chain activity), consult the exchange or on-chain analytics provider directly and cite their reported figures when needed.

Appendix: quick checklists

Pre-chart checklist (before you create stock chart in Excel):

• [ ] Data columns present and in correct order (Date, Open, High, Low, Close[, Volume])
• [ ] Date column formatted as Date/Time and sorted ascending
• [ ] Numeric fields are Number type (no stray text)
• [ ] Timezone normalized and aggregation decided (minute/hour/day)
• [ ] Missing values handled or documented

Post-chart checklist (after chart creation):

• [ ] Chart title, source and timeframe added
• [ ] Axis scales checked and volume on secondary axis if needed
• [ ] Colors and legend set; up/down candle colors consistent
• [ ] Chart template saved for reuse
• [ ] Refresh logic validated (Power Query, VBA or manual)

If you want: export a copy of your prepared OHLCV table from Bitget and import using Power Query to create a reusable template. Bitget Wallet users can reconcile holdings before charting to ensure P&L calculations match exported trade history.

Further exploration and next steps: if you need a ready Excel template or a guided video walkthrough for how to create stock chart in Excel with Bitget CSVs and Power Query steps, consider downloading our Bitget Excel starter pack (check your Bitget account resources) or request a template from Bitget support.

Thank you for reading — build your first OHLC or candlestick workbook, save it as a template, and automate refreshes to keep charts up to date with minimal effort.