The best chart for time-based data in Excel is a line chart, for one simple reason: most time-based data is a single trend, and a line chart shows direction and rate of change better than any other type. A column chart, an area chart, or a candlestick chart is the right call only when your data breaks that basic pattern.
Excel's own "Recommended Charts" feature does not always get this right. It reads your data's shape but not always what you're trying to show with it, so it can suggest a column chart for data that's really a trend, or a line chart for data that's really a set of unrelated categories that happen to have dates attached.
This guide covers the four chart types that actually fit time-based data, how to tell which one you need in under a minute, the exact steps to build it in Excel, and where Excel's charting tools run out of road.
The Short Answer, by Data Shape
Match your data to one of these before opening the Insert tab:
- Line chart: a single trend measured at regular intervals, like daily traffic, monthly revenue, or weekly signups. This is the default choice for time-based data.
- Area chart: same shape as a line chart, but the cumulative total matters as much as the direction, like stacked usage across products or a running total. See our area charts guide for when to stack and when not to.
- Column chart: you're comparing separate periods rather than tracking continuous movement, like quarterly totals or year-over-year snapshots.
- Candlestick chart: open, high, low, and close values for the same period, almost always financial data. Our candlestick chart guide covers the exact column layout Excel needs.
All four live under Insert → Recommended Charts, or Insert → Charts if you already know which one you want. The chart type matters less than a setting most people never touch: whether the horizontal axis is a date axis or a text axis. Get that wrong and even the right chart type will misrepresent the timeline.
Why Excel's Chart Suggestions Get Time Data Wrong
Excel treats a column of dates as text unless it's formatted as an actual date value. When that happens, two things go wrong at once.
First, the horizontal axis becomes a category axis instead of a date axis. A category axis spaces every entry evenly, no matter the real gap between dates. Five daily readings followed by a thirty-day gap look identical to five evenly spaced days, because Excel can't tell a label from a date.
Second, "Recommended Charts" leans toward column charts more often than it should. Columns are a safe default for almost any two-column dataset, so if Excel can't confirm you're looking at a trend, it hedges toward a comparison chart instead.
The fix: format the date column as a date first (Home → Number Format → Short Date), then check Format Axis and confirm Axis Type reads "Date axis," not "Text axis," once the chart exists.
Which Chart Type Fits Your Time-Based Data?
Use this table if you're still unsure after the short answer above:
| Your data looks like... | Best chart | Why |
|---|---|---|
| One number per date, tracking up or down | Line chart | Shows direction and rate of change clearly |
| Several related numbers per date (multiple products, regions) | Multi-series line chart | One line per series, same date axis |
| A running or cumulative total over time | Area chart | The filled area reinforces the size of the total |
| Totals for distinct periods, not a continuous flow | Column chart | Bars invite period-to-period comparison |
| Open, high, low, close for each period | Candlestick chart | The only chart type built for OHLC data |
| A project timeline with start and end dates per task | Gantt-style bar chart | Shows duration and overlap, not just one value |
That last row is easy to miss: a task list with start and end dates is time-based data, but it needs a horizontal bar chart built as a Gantt chart, not a line or column chart. Excel can build one with a stacked bar chart and manual formatting; see our Gantt chart guide for the setup, or the Gantt chart maker if you only need one.
How to Build a Time-Based Chart in Excel, Step by Step
- Format your date column as an actual date: select the column, then Home → Number Format → Short Date (or a custom date format).
- Select both the date column and the value column(s), including headers.
- Go to Insert → Recommended Charts, or pick the type directly under Insert → Charts if you already know which one you need.
- Right-click the horizontal axis, choose Format Axis, and confirm Axis Type is set to "Date axis." Switch it manually if Excel picked "Text axis."
- Under Axis Options, set the base unit (days, months, or years) to match how often your data is actually recorded, not how it happens to display.
- Add a chart title and axis labels that name the actual metric and time period, not the default "Column1" and "Column2."
That covers most sales, traffic, and operational reporting. Two things commonly trip people up after this point. A gap in the data, like a missing week, shows up as a flat line unless you handle it explicitly. Mixed granularity, like daily figures plotted next to monthly totals on the same axis, distorts both series at once.
When Excel Isn't the Right Tool for the Job
Excel handles the four chart types above well once the date axis is set correctly. It gets harder in a couple of specific situations.
Dates that don't arrive in a clean format are the most common one. Exports from other tools often mix date formats, store dates as text, or drop a few non-date rows into the same column, and Excel's Short Date formatting doesn't fix any of that on its own.
CleanChart's upload step detects and corrects inconsistent date formats along with other common issues (duplicates, missing values, type mismatches) before a chart gets built, so a messy export doesn't need a manual cleanup pass first. See our Excel alternative for charts guide for a fuller comparison, or the broader Excel vs online chart makers breakdown if you're deciding between the two approaches generally.
The other common case is styling. Excel's default colors, gridlines, and fonts need a manual pass to look publication-ready, every time. CleanChart applies a recommended chart type with a confidence score, which you can override, and produces a styled result without that manual pass. You can export it as a PNG or SVG, with PDF available on a paid plan.
If your data is already clean and you're comfortable with Excel's axis settings, there's no reason to leave Excel for a straightforward line or column chart. If you're starting from a messy CSV or Excel export and want a clean result without the formatting step, try converting your Excel file to a line chart directly.
Frequently Asked Questions
What's the difference between a line chart and an area chart for time-based data?
A line chart shows direction and rate of change. An area chart shows the same trend but fills the space below the line, which emphasizes cumulative size or volume rather than just movement. Use a line chart when the trend itself is the point; use an area chart when the total amount matters too.
Why does my Excel chart space out dates evenly even though they aren't evenly spaced?
Excel is treating your horizontal axis as a text or category axis instead of a date axis. Format the date column as a real date value, then check Format Axis to confirm Axis Type is set to "Date axis," not "Text axis."
Can Excel handle irregular or missing dates in a time series?
It can plot around them, but it won't flag them for you. A gap in your data reads as a flat line unless you add an explicit entry for the missing period, and Excel won't warn you the gap exists.
Is a bar chart ever the right choice for time-based data?
Yes, when you're comparing distinct periods rather than tracking a continuous trend, like total sales per quarter. A vertical column chart works well there; a horizontal bar chart is also the basis for a Gantt-style timeline, covered in the table above.
Related CleanChart Resources
- Line Chart Maker – Build a time-based trend chart
- Area Chart Maker – Show cumulative totals over time
- Candlestick Chart Maker – OHLC financial data
- Gantt Chart Maker – Project timelines with start and end dates
- Excel to Line Chart – Convert an Excel file directly
- Excel vs Online Chart Makers – Full comparison
- Best Excel Alternative for Making Charts – When to switch tools
- Area Charts: The Complete Guide – Stacked vs standard area charts
- How to Create a Candlestick Chart – OHLC column layout
- Gantt Chart Maker – Turn a task list into a timeline
External Resources
- Microsoft: Available Chart Types in Office – Full reference for Excel's chart types
- Microsoft: Present Data in a Chart – Official guidance on choosing and building charts
- Wikipedia: Time Series – Background on time-based data and its properties