Excel Trendlines with Dates: How to Make Them Work

Yes—Excel can add a trendline to data recorded by date, but the chart type matters. For a trendline that measures actual time between dates, create an XY (Scatter) chart with real Excel dates on the X-axis, then add a trendline; a regular Line chart can treat dates as evenly spaced categories instead.

Why dates change the chart choice

Excel stores valid dates as serial numbers: each date corresponds to a number, with later dates having larger values. A chart can therefore use dates as numeric X-values, but only when its horizontal axis is a value axis. An XY (Scatter) chart has that kind of axis. It places points according to their numeric date values, so gaps in time are reflected in the chart.

A Line chart normally uses a category axis. Its points are arranged in sequence, and the distance from one point to the next is generally the same even if the dates are not. For example, measurements from January 1, January 2, and January 20 appear as three consecutive categories, not as points separated by 1 day and 18 days. A Line chart can sometimes display a date axis with calendar spacing, but that does not make it the dependable choice for a regression trendline based on elapsed time. Use Scatter when the spacing between dates matters to the calculation.

This distinction is easy to miss because both chart types can show dates along the bottom. The important question is not only whether the labels look like dates; it is whether the horizontal position is based on each date’s numeric value.

Create a date-based trendline in Excel

For Microsoft Excel for Microsoft 365 on Windows, arrange the data with dates in one column and the measurements to analyze in another. Keep a header row, and use one row per observation. For example, column A might contain dates and column B might contain daily sales, temperatures, or website visits.

Check that the dates are genuine Excel dates rather than text that merely looks like a date. Select a date cell and inspect its number format; changing the format to General should display a serial number if Excel recognizes it as a date.
Select the date and measurement columns, including their headers. If the columns are not adjacent, select the first range, then hold Ctrl while selecting the other range on Windows.
Choose Insert > Insert Scatter (X, Y) or Bubble Chart, then choose a scatter chart, such as Scatter with only Markers. Confirm that the dates are assigned to the horizontal axis and the measurements to the vertical axis.
Select the chart, then choose Chart Design > Add Chart Element > Trendline > Linear. You can also select a plotted data series, right-click it, and choose Add Trendline.
To change the fit or show its statistics, open Format Trendline and choose a trendline option. If useful, select Display Equation on chart or Display R-squared value on chart.
If the horizontal axis shows serial numbers, right-click it and choose Format Axis. In the Number section, select an appropriate date format. Formatting changes the labels, not the underlying date values.

Excel for Mac has the same basic approach: use Insert to make an XY scatter chart, then use the chart design controls or the series context menu to add a trendline. Ribbon wording and placement can vary slightly by build. Excel for the web may offer fewer chart-formatting controls than the desktop app, so use the Windows or Mac desktop version if a particular trendline option is unavailable.

Line chart or XY scatter chart?

Choose based on what the horizontal position should mean, not just on appearance. A Scatter chart is generally the right choice when observations are irregularly spaced, when you want regression against elapsed time, or when you need a numerical X-axis. A Line chart is useful when each observation is one step in an ordered sequence and equal spacing is intentional—for example, successive production batches or monthly reporting periods where the purpose is to compare one period with the next.

Consider monthly readings taken on January 1, February 1, and April 1. A category-style chart can give each reading equal horizontal space. A Scatter chart instead reflects the different lengths of the calendar intervals. If the April reading is much later than the February reading, that longer interval should influence a model of change per unit of time. If the dates are exactly one day apart and the goal is simply to show a sequence, a Line chart may be visually adequate, but Scatter still makes the time-based interpretation more explicit.

One practical test is to look for missing periods. If dates are missing and the chart still places all observations evenly, it is likely behaving like a category chart. A Scatter chart positions points according to their date values, leaving a larger gap where more time passed.

Choose a trendline that fits the question

Excel offers several trendline models, including Linear, Exponential, Logarithmic, Polynomial, Power, and Moving Average, depending on the chart and data. A linear trendline is a reasonable starting point when the measurement changes by roughly a steady amount per unit of time. Its slope represents change per one unit of the X-values. With date serials on the axis, that unit is a day.

Use a different model only when the data and the question justify it. Exponential growth can describe a pattern that changes by a roughly constant percentage over equal time intervals, while a polynomial can bend to follow a curved pattern. A higher-order polynomial may fit observed points closely without representing a stable underlying process. A Moving Average smooths short-term variation; it is not the same as a regression line and does not establish a causal explanation.

To compare models, inspect the plotted pattern, residual behavior if you calculate it separately, and whether the model makes sense for the subject. A larger R-squared value alone does not prove a model is appropriate. In particular, adding polynomial terms can raise R-squared while making the line less useful for explaining or forecasting the data.

Forecasting beyond the observed dates

In Format Trendline, Excel provides Forecast controls for extending a trendline forward or backward. For an XY Scatter chart with dates as numeric X-values, the forecast distance is expressed in the X-axis units. Since Excel date serials advance by one for each day, a forward forecast of 30 corresponds to 30 days. It does not mean 30 observations, unless the observations happen to be daily and evenly spaced.

Forecasting is an extension of the fitted mathematical line, not a guarantee about future results. A linear trendline that summarizes a short, stable period may become implausible when extended far beyond that period. Seasonal patterns, policy changes, unusual events, and measurement changes can all make a straight-line projection misleading. If the data are seasonal, a basic Excel trendline may not capture that repeating structure.

Before relying on an extension, ask whether the relationship has stayed consistent over the historical range and whether the forecast horizon is modest relative to that range. Treat the trendline as one descriptive tool, and make the chart’s date range and forecast length visible so readers can distinguish observed values from extrapolated ones.

Fix common date and trendline problems

The dates appear as labels but the trend behaves oddly: Check whether the chart is a Line chart. Rebuild it as XY Scatter and verify the date column is the X range.
Dates display as serial numbers: The data may be valid; the axis is simply using a general number format. Apply a date number format through Format Axis.
Excel will not use the dates as X-values: Imported dates may be text. Text can look identical to a date but is not a numeric date value. Convert the source data to actual dates before charting. Text to Columns can help with some imports, but confirm the resulting values are recognized dates.
Some points are missing: Check for blank cells, text in the measurement column, filtered rows, or dates outside the selected range. Also confirm that the chart series includes the intended cells.
The displayed equation looks strange: The equation may use large date serial numbers for x, which can produce a large intercept. That is a consequence of the date coordinate system; it does not mean the axis is formatted incorrectly. The equation’s x-value is a serial date, not a year such as 2025.
The line does not match every point: A trendline is a fitted summary, not a line that must pass through each observation. Outliers and data variability can leave points above or below it.

For imported data, check regional date interpretation as well. A value such as 04/05/2026 can mean different dates in different date conventions. Confirm the intended day and month before drawing conclusions from the fitted line.

Irregular dates, missing observations, and data quality

A date-based Scatter chart handles uneven intervals in the horizontal placement, but it cannot correct problems in the measurements. Before fitting a trendline, sort records by date for readability, remove accidental duplicate entries where appropriate, and check whether a blank means zero, unknown, or not measured. Replacing missing measurements with zero can create an artificial drop; connecting or omitting gaps also affects how the chart appears.

Repeated dates can be legitimate, such as several readings taken on one day. A Scatter chart can plot points at the same X-value, but a crowded chart may hide them. You might summarize those readings by day if that matches the question, or keep the individual observations and use markers that reveal overlap. Do not aggregate automatically: averaging can conceal meaningful variation or differences among measurements.

Also consider whether the measurement itself is comparable over time. A change in units, a new sensor, revised accounting definitions, or a change in collection method may create a break that a single trendline glosses over. For a series with a clear structural change, separate analysis of the periods may be more informative than forcing one line across the entire history.

Read the equation and R-squared carefully

If you display the trendline equation, Excel writes it in terms of the chart’s X-values. On a date-based Scatter chart, those values are serial dates. The slope of a linear equation therefore describes the estimated change in the measurement per day, not per month or per year. To express a daily slope in a larger unit, convert it using an appropriate number of days, while remembering that calendar months do not all have the same length.

R-squared describes how closely the fitted model accounts for variation in the plotted data under that model. It does not say that dates caused the change, that the relationship will continue, or that a forecast is accurate. A high value can coexist with a poor choice of model or a major outlier. A low value may simply indicate that the measurements vary substantially around the overall direction.

For a more interpretable equation, some analysts create a separate elapsed-time column, such as days since the first observation, and use that as the scatter chart’s X-values. This centers the numbers near zero and makes the intercept correspond to the model’s estimate near the start of the series. However, the axis then needs clear labels or explanation, because its values represent elapsed days rather than calendar dates. Keep the original date column and explain the chosen time scale.

Frequently Asked Questions

Can I add a trendline to a chart that already has dates?
Yes. Select the data series and add a trendline, but confirm the chart type. If you need actual elapsed time to determine the fit, use an XY Scatter chart with valid date values as X-values.

Why does my trendline equation use a number like 46,000?
Excel stores dates as serial numbers, and the equation uses the numeric X-values. The large number is the date coordinate, not a mistake in the date display. Formatting the axis changes labels, not the values used in the equation.

Can I forecast 12 months ahead?
You can extend a Scatter-chart trendline, but its forecast distance is measured in X-axis units. With date serials, 12 means 12 days, not 12 months. A rough calendar-year interval is about 365 days, but a forecast should be judged by whether the model is suitable, not just by entering a duration.

Should I use a Line chart for monthly data?
Use a Line chart when equal spacing by reporting period is the intended presentation. Use Scatter when the actual number of days between dates matters, such as when months are incomplete or observations are irregular.

Does a high R-squared mean the trendline is reliable?
No. It measures fit to the observed data under the selected model. It does not establish causation or guarantee future performance; inspect the data, the model assumptions, and the forecast range as well.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.