Excel Chart Dates Not Matching Data? Fix the Axis

If Excel chart dates do not line up with the source data, first check whether the date cells contain real Excel dates and whether the chart is using the intended date or category axis. In Microsoft 365 for Windows, right-click the chart’s horizontal axis, choose Format Axis, and inspect Axis Options; then verify the chart’s source range with Chart Design > Select Data.

Start with the symptom

A chart can look wrong for several different reasons: dates may appear out of order, points may be spaced unevenly, some dates may be missing, or the labels may not match the worksheet. The visual symptom is useful because each points to a different setting or data issue. For example, dates in the correct order but with equal spacing often indicate a category axis, while dates that appear as numbers or far-future labels may indicate that Excel is interpreting text or serial values unexpectedly.

Before changing anything, compare the chart with the source range one row at a time. Confirm that each date belongs to the value in the same row, that the range includes the first and last intended records, and that no extra heading or blank row has been included as data. If possible, make a copy of the workbook before restructuring data or changing workbook-wide date settings.

Wrong labels: check the axis-label range and date conversion.
Wrong order: check the source sort order and whether the dates are real values.
Wrong spacing: compare a date axis with a category axis or an XY scatter chart.
Missing points: inspect blanks, filtered rows, and hidden-data settings.

How Excel stores and plots dates

Excel stores ordinary dates as numbers, called serial values, and uses cell formatting to display those numbers as dates. A cell that displays 4-Mar-2025 can therefore contain a number underneath. A date typed or imported as text may look identical on the worksheet but behave differently in a chart. This is one of the most common reasons a chart does not follow the dates as expected.

Charts also distinguish between an axis that treats labels as categories and one that recognizes dates as a continuous timeline. A category axis gives each listed entry a position, typically with equal spacing. A date axis places entries according to elapsed calendar time. If the data has observations on Monday and Friday but none in between, a date axis leaves more space between those points; a category axis can place the two observations side by side at equal intervals.

Excel date values depend on the workbook’s date system. The standard 1900 date system is common in Windows workbooks; some workbooks use the 1904 system. The systems differ by 1,462 days. If dates seem consistently shifted by roughly four years after moving data between workbooks, check the date-system setting rather than editing every chart label. In Windows, it is under File > Options > Advanced, in the section labeled When calculating this workbook. Change it only when you understand the effect on existing dates and formulas.

Check whether the source cells are real dates

Changing a cell’s number format to Date does not convert text into a date. It only changes how numeric values display. To check a suspicious cell, select it and look at the formula bar and number format. You can also test whether a cell contains a number. With a suspected date in A2, the formula =ISNUMBER(A2) returns TRUE for a stored Excel date or another number, and FALSE for text. Since numbers can also be ordinary quantities, use this as a diagnostic alongside the cell’s contents, not as proof by itself.

Imported CSV files are a frequent source of text dates. A value such as 03/04/2025 is ambiguous: depending on the intended convention and regional settings, it could mean March 4 or April 3. Confirm the source format before converting it. If the dates are consistently written as year-month-day text, an explicit formula avoids relying on the computer’s date interpretation. With ISO-style text 2025-03-04 in A2, use =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). It returns the Excel date for March 4, 2025, which has serial value 45720 in the 1900 date system. Format the result as a date, fill the formula down, and use the converted column as the chart’s date values.

Another option for consistently formatted text is Data > Text to Columns. In the wizard, select the appropriate date order when prompted, then finish the conversion. Always check a few converted records against the original source, especially when day and month could be reversed. If the data comes from a database or an export process, correcting its import settings can prevent the issue from returning.

Set the horizontal axis to match the data

For a line or column chart, right-click the horizontal axis and select Format Axis. Under Axis Options, look for Axis Type. Depending on the chart and data, Excel can offer Automatically select based on data, Text axis, or Date axis. Choose Date axis when the labels are valid dates and calendar spacing is meaningful. Choose Text axis when every entry should be treated as a separate category, regardless of the amount of time between entries.

In the Format Axis pane, inspect the axis bounds and units if the displayed range seems clipped or compressed. Minimum and maximum bounds can be fixed rather than automatic, and date-axis units can group or space labels by days, months, or years. Reset a bound to Auto if the chart is excluding newly added dates. Changing the display format for the axis labels affects how dates look, not the underlying dates or their order.

If the axis lists months or years instead of every individual date, Excel may be grouping labels to keep them readable. That does not necessarily mean the source records are missing. Widen the chart, reduce label density, or choose an appropriate major unit if the controls are available. Avoid forcing every label to display on a crowded chart: overlapping text can make correct data appear incorrect.

Choose between a line chart and an XY scatter chart

A line chart is often appropriate when the horizontal values are dates and the main goal is to show a trend in observations. Its horizontal axis can be a date axis or a category axis. A category axis is useful when each observation represents one step in a sequence, such as survey periods, even if the gaps between periods differ. A date axis is more suitable when the actual elapsed time matters.

An XY (Scatter) chart treats its horizontal values as numeric coordinates. Excel dates are numeric serial values, so a scatter chart can position observations according to actual time intervals. This is often the better choice when measurements occur irregularly, such as readings taken on the 1st, 2nd, and 20th of a month. It is also useful when the horizontal values are dates but you need a numeric X scale rather than a sequence of labels.

To check or change a chart’s type, select it and use Chart Design > Change Chart Type. For a scatter chart, check the series ranges in Chart Design > Select Data, then edit each series so its X values point to the date cells and its Y values point to the matching measurement cells. A common mistake is to use the dates only as category labels while the scatter series’ X values still refer to a different column.

Verify the chart’s data range and row alignment

Select the chart and choose Chart Design > Select Data. Review the chart data range, the series names, and the Horizontal (Category) Axis Labels. If the chart includes the wrong date column, click Edit in the axis-label area and select the intended cells. If it omits recent rows, expand the data range or use an Excel table so that new records are included as the table grows.

Check that the date and measurement columns remain aligned. Sorting only one column can pair dates with the wrong values even though both columns look valid. Sort the complete data range together, or sort the table by its date column. Also look for a header accidentally treated as a record, duplicate dates, blank rows splitting the selected range, and formulas that return an empty string. A chart may handle blanks differently from cells containing zero, so confirm which one the source actually contains.

Filtered or hidden records can affect what appears in a chart. In the Select Data dialog, use Hidden and Empty Cells to review whether data in hidden rows and columns should be plotted. The exact behavior depends on the chart and the setting. This is worth checking when a chart changes after filtering a table or when some dates appear to disappear without being deleted.

Common fixes for dates that look wrong

Dates show as large numbers: the cells may be valid serial dates with a General or Number format. Apply a date format to the source cells or axis labels; do not replace the underlying values with typed text just to change their appearance.
Dates are out of chronological order: sort the entire dataset by the date column, and confirm the dates are numeric rather than text. Text dates can sort alphabetically, putting 10-Jan before 2-Feb in some sequences.
All points have equal spacing: switch from a category axis to a date axis, or use an XY scatter chart if actual elapsed intervals must determine the spacing.
Some dates are not labeled: Excel may be suppressing labels to prevent overlap or showing date units at a broader interval. Adjust chart size or axis label intervals rather than assuming the points are absent.
New dates do not appear: inspect the selected range, fixed axis maximum, and whether the chart uses a growing table or a fixed cell reference.
Dates shift after copying between workbooks: check whether the workbooks use different 1900 and 1904 date systems before making a bulk correction.

Prevent the mismatch in future charts

Keep dates in one column and measurements in adjacent columns, with one clear header row and no merged cells inside the data. Store dates as genuine Excel values, use a consistent date convention during import, and avoid mixing text labels such as “March” with numeric dates in the same date field. If the source is a recurring list, format it as an Excel table before creating the chart; tables generally make it easier for chart ranges to include additional rows.

Choose the chart type based on what the horizontal position means. If each record is a category, equal spacing may be intentional. If the distance between dates represents elapsed time, use a date axis or scatter chart. After adding data, check both the source range and axis bounds. These simple checks help distinguish a chart-formatting issue from a problem in the source data itself.

Frequently Asked Questions

Why are my Excel chart dates showing as numbers?

Excel stores dates as serial numbers, so the chart may be displaying the underlying values with a number format. Apply a date number format to the source cells or axis labels. If the numbers are not valid date serials, inspect the source values before changing the format.

Why does Excel put dates in the wrong order on a chart?

The source may be sorted incorrectly, or its dates may be text that sorts alphabetically rather than chronologically. Check that the cells contain real dates, then sort the entire data range by the date column so each measurement stays with its date.

How do I make Excel show gaps between dates?

Use a Date axis for a line or column chart when calendar spacing matters, or use an XY scatter chart with the date cells as X values. A Text axis assigns equal space to categories and does not represent the length of gaps between dates.

Why are some dates missing from my Excel chart?

They may be excluded from the chart’s selected range, hidden by filtering, or represented by blank cells. Excel may also omit some axis labels to avoid clutter while still plotting the data points. Review Select Data, Hidden and Empty Cells, and the axis label interval.

Does changing a cell’s format convert text into a date?

No. Number formatting changes the display of numeric values but does not turn text into a date value. Convert text dates using a suitable import method, Text to Columns, or a formula that matches the source’s known date layout, then verify the result.

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.