Excel Line Chart by Date: Make Dates Plot Correctly

To make an Excel line chart by date, put real Excel dates in one column and the values to chart beside them, select the data, then choose Insert > Insert Line or Area Chart > 2-D Line. If the dates are spaced unevenly, format the horizontal axis and choose Date axis so Excel uses calendar spacing instead of giving every row equal space.

Build a basic line chart from dated data

For a simple chart, arrange the worksheet with one date per row and a measurement in the next column. For example, column A might contain dates from January 1 through January 31, and column B might contain daily website visits, temperatures, or sales. Put descriptive headers in the first row, such as Date and Visits. Select the headings and data, then use the Insert tab to create a 2-D Line chart. Excel uses the date column for the horizontal labels and the values column for the plotted line.

If the chart includes the wrong fields or treats a heading as a point, click the chart and choose Chart Design > Select Data. In the Select Data Source dialog, check that the series values refer only to the measurement cells and that the horizontal (category) axis labels refer to the date cells. A table with dates in the first column and separate measurements in following columns can produce one line per measurement series.

After insertion, click a date label or right-click the horizontal axis and select Format Axis. In the Format Axis pane, open Axis Options and locate Axis Type. Choose Date axis when calendar intervals should determine spacing. Depending on the data and chart, Excel may select an axis type automatically; selecting it explicitly is useful when the chart looks as though it is ignoring the dates.

Why the date axis changes the chart

Excel stores recognized dates as numeric serial values, even though the worksheet displays them in a human-readable format such as 1/5/2026. A line chart can use the date values as category labels or as a date axis. With a text axis, each row receives the same horizontal distance. That is appropriate for a sequence of equally spaced categories, but it can distort a timeline when measurements are recorded at irregular intervals.

A date axis positions points according to calendar time. Suppose the table has measurements for January 1, January 2, and January 10. A text axis spaces the three observations evenly, while a date axis leaves a larger interval between January 2 and January 10. This distinction can change the visual meaning of trends: a sharp change over eight days should not appear to happen over the same horizontal distance as a change over one day.

The date axis does not create observations for dates missing from the table. It positions the existing points along a calendar scale. If there are no rows for January 3 through January 9, the chart may show an interval without markers or measurements. The line’s behavior across that interval also depends on how Excel is configured to display empty cells.

Make sure Excel recognizes the dates

Changing the axis type will not fix dates that are actually text. Imported files, copied data, and regional date conventions can leave values such as 03/04/2026 ambiguous or stored as text. A cell can look like a date while still not being a usable date value. Try changing the cell’s number format to a date format; if its appearance changes to a date or a serial number, that can indicate Excel is treating the value numerically. Formatting alone, however, does not convert text into a date.

For text dates, first check how the source expresses day, month, and year. One conversion option is to select the date column and use Data > Text to Columns. Step through the wizard, choose Date, select the order that matches the source data (such as DMY or MDY), and finish the conversion. Check the results before charting, especially when dates like 04/05/2026 could mean April 5 or May 4. Excel’s interpretation can depend on regional settings, so do not assume that a slash-separated date has the same meaning in every file.

After conversion, apply a consistent display format from Home > Number Format, such as Short Date, or use Format Cells to choose a custom date display. The display format changes how a valid date looks; it does not change the underlying date or the chart’s actual spacing. Keep the underlying values intact if you want Excel to plot them chronologically.

Choose the axis units and label format

With a date axis selected, use the Format Axis pane to control the scale. Depending on the chart and data, Excel provides settings for the base unit and major or minor units. A base unit of days, months, or years determines the calendar unit used for the axis. Major units control the interval between prominent labels or tick marks. For a year-long daily series, monthly labels may be easier to read than a label for every date; for a short experiment, daily labels may be useful.

You can also adjust the number format used for the axis labels. In the Format Axis pane, expand Number and choose a date format that fits the chart, such as a month and year for a multiyear trend. If Excel is using the worksheet’s format, changing the number format on the axis can make the chart easier to scan without changing the source cells. Avoid displaying so many labels that they overlap; label readability is more useful than showing every date at once.

Axis bounds can focus attention on a particular time range. Set minimum or maximum bounds only when there is a clear reason to exclude other dates, and check that the resulting chart does not hide relevant observations. For a chart intended to show the complete record, automatic bounds are often the simplest choice. If new dates are added regularly, using an Excel Table as the source can help the chart include added rows; review the chart after changing its source data.

Prepare dates and values for a reliable timeline

Sort records by date in ascending order before charting. Excel generally plots line-chart categories in the order presented in the source, so unsorted rows can make a line move backward and forward across the timeline. Select the data range and use Data > Sort, sorting by the date column from oldest to newest. Keep each row’s date and related measurements together while sorting; sorting only one column would misalign the observations.

Check for duplicate dates as well. Multiple measurements on the same date can be valid, but they will share a horizontal position. If the repeated rows represent separate readings, decide whether to show all of them, summarize them by date, or use a chart suited to the underlying data. A line connecting several same-day readings can look crowded and may imply a sequence that is not meaningful.

Use one consistent date column rather than mixing dates and explanatory text in the same field.
Keep numeric measurements as numbers. Values imported with units or symbols embedded in the cell may be treated as text.
Include clear column headings and avoid blank rows inside the data range when possible.
Consider how missing observations should appear instead of replacing unknown values with zero. A zero is a real plotted value, not an indication that data was unavailable.

Handle gaps, times, and uneven sampling

A missing date is different from a missing measurement. If your table contains a row for a date but its value is blank, Excel’s empty-cell setting determines whether the chart shows a gap, connects points across the blank, or treats the empty cell as zero in some chart contexts. To review the behavior, select the chart and choose Chart Design > Select Data, then select Hidden and Empty Cells. Choose the display option that reflects the data’s meaning. Do not use zero merely to make the line continuous if zero was never observed.

When measurements include times as well as dates, Excel can store a date and time as a numeric value with a fractional part. A standard line chart with a date axis can show the overall timeline, but dense intraday readings may make axis labels and point spacing difficult to interpret. If exact numeric spacing between timestamps is central to the analysis, an XY (Scatter) chart with lines can be a better fit. Scatter charts use the horizontal values as a numeric scale, while a line chart is designed around a sequence of categories or dates.

For regularly sampled readings, such as one value each day, a line chart is usually straightforward. For irregular sampling, the date axis is important because it preserves elapsed time visually. If the intended comparison is simply one value per named period regardless of how long each period was, a text axis may instead be the honest choice. Select the axis type based on what the horizontal distance is meant to communicate.

Common date-chart problems and fixes

Every point is equally spaced: The chart may be using a text axis, or the source dates may be text. Verify that the dates are real values, then open Format Axis and choose Date axis.
The dates appear in the wrong order: Sort the complete table by date from oldest to newest and verify that the axis labels point to the correct column.
The chart shows numbers instead of dates: Select the axis and choose a date number format in Format Axis > Number. Also check the number format of the source cells.
The chart skips dates: An axis may show only selected labels to prevent crowding. Adjust the major unit or make the chart wider; the absence of a label does not necessarily mean Excel dropped a data point.
The line has unexpected zeros or gaps: Review blank cells and the Hidden and Empty Cells setting. Confirm that blank values have not been converted to zero.

When changing a chart does not resolve the issue, inspect the source range through Chart Design > Select Data. A chart may be referencing only part of the table, including an extra blank row, or using a different column as its labels. Correcting the source range is often more effective than repeatedly changing axis formatting.

Line chart or scatter chart for dates?

A line chart is a good default for time-series reporting: values are connected in sequence, and Excel can use a date axis to space dates according to calendar intervals. It is familiar for daily sales, monthly totals, or a measurement recorded over time. The key question is whether dates should be treated as time intervals or merely as ordered labels.

An XY (Scatter) chart is useful when the horizontal variable is numeric and the precise distances between observations matter, including date-time values with irregular intervals. In Excel, select the data and use Insert > Insert Scatter (X, Y) or Bubble Chart, then choose a scatter option with lines if a connected series is appropriate. Check that the date values are assigned as the X values and the measurements as the Y values. A scatter chart can be less convenient when the goal is a conventional category-style timeline, so choose it for its numeric spacing rather than assuming it is automatically superior.

Frequently Asked Questions

How do I make Excel show dates on the horizontal axis? Put valid date values in a column next to the measurements, create a line chart, and check the horizontal-axis labels in Chart Design > Select Data. In Format Axis, select Date axis if Excel has not chosen it automatically.

Why are my dates evenly spaced in an Excel line chart? The horizontal axis may be a text axis, which spaces categories evenly, or Excel may not recognize the cells as dates. Convert text dates to real dates where needed, then set Axis Type to Date axis to reflect elapsed calendar time.

Can an Excel line chart show missing dates? Yes. A date axis can show the time interval between recorded dates without inventing values for dates that are absent. For blank measurements within the source range, use Chart Design > Select Data > Hidden and Empty Cells to choose whether the chart leaves a gap or connects across it.

How do I show months instead of every date? Select the date axis, open Format Axis, and adjust the base or major unit so the labels use monthly intervals. You can also change the axis number format to display month and year, then widen the chart if labels still overlap.

Should I use a line chart or scatter chart for dates? Use a line chart for a familiar time-series display, especially when dates are regular or you want a standard timeline. Use an XY scatter chart when exact numeric spacing between irregular date-time values is important, and verify that dates are assigned to the X axis.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.