Excel Trendline for a Specific Range: How to Set It Up

Charts & pivot tables · Updated November 21, 2026

Excel does not let you tell a trendline to use an arbitrary subset of points within a chart series: by default, it fits the whole series. To fit only a specific range, create a separate chart series containing just the observations you want, then add the trendline to that series.

Why a separate series is needed

A trendline belongs to a chart series, not to a highlighted block of worksheet cells or to a manually selected stretch of the plotted line. If a series contains 20 observations, Excel calculates its trendline from those observations, even if you are interested in only observations 8 through 14. Selecting one point or zooming the chart does not change which values are used in the calculation.

The reliable solution is to make a second series whose source data contains only the intended observations. The original series can remain visible for context, or you can leave it off the chart. Add a trendline to the new series, not to the original one. This also makes the choice auditable: someone opening the workbook can inspect exactly which data points informed the fit.

“Specific range” can mean two different things: a row range, such as records 8–14, or a value range, such as dates from March 1 through March 31. Both work with a helper series. The important point is that the subset is defined by its source data rather than by the visible chart window.

Choose the right chart and define the subset

For numeric X values, including measurements, elapsed time, and dates, an XY (Scatter) chart is usually the best choice. It treats X values as numbers and fits the trendline against their actual spacing. A Line chart instead uses a category axis: points are positioned by category order, which can make unequal date gaps look equally spaced. That distinction can change the fitted line.

Before building the helper series, decide what “in range” means. If you mean a contiguous block of rows, copy those rows. If you mean all records whose X values fall between two limits, use a condition on the X column. If the rule depends on another field, such as a product or region, include that criterion too. Check that the X values are genuinely numeric; dates stored as text will not behave like Excel date serials in comparisons or scatter charts.

Recommended method in Microsoft 365: FILTER

Suppose A2:A21 contains numeric X values, B2:B21 contains corresponding Y values, and G2 and H2 hold the lower and upper X limits. Put the following formula in J2 to return the matching X and Y rows together:

With X values in A2:A21, Y values in B2:B21, and inclusive limits in G2 and H2, enter =FILTER(A2:B21,(A2:A21>=G2)*(A2:A21<=H2),"No matching points") in J2. If A contains the integers 1 through 20, B contains the values 2A+5, and G2 and H2 are 8 and 14, the result spills seven rows: (8,21), (9,23), through (14,33).

FILTER is available in Microsoft 365 and Excel 2021 or later. Because the formula returns both columns from the same source rows, each X value stays paired with its correct Y value. The two tests create an inclusive interval: values equal to either limit are retained. If the limits are reversed, no values match; enter the lower limit in G2 and the higher one in H2.

Now create or select an XY (Scatter) chart. On Windows, use Chart Design > Select Data > Add to add a series. Give it a name, set Series X values to the spilled X results in column J, and Series Y values to the corresponding results in column K. If Excel does not accept a spill reference directly in the dialog, select the currently spilled cells or define a workbook name referring to the spill range and use that name as the chart source. Then select the new series and choose Chart Design > Add Chart Element > Trendline, or right-click that series and choose Add Trendline. On Mac, the chart commands are also on the Chart Design tab, though dialog layout can vary slightly.

Using a fixed subset or an older Excel version

If the range will not change, the simplest method is often to copy the desired X and Y rows into two adjacent helper columns and make a chart series from those cells. For example, copy only the records for dates in a chosen month. This works in older desktop versions as well as Microsoft 365, and avoids dependence on a dynamic-array formula. Keep the source rows together and label the helper columns clearly so the subset is not mistaken for the full dataset.

If you need to update the subset regularly and do not have FILTER, apply worksheet filters to identify the matching records, copy the visible X/Y pairs, and paste them into the helper area before refreshing the chart. Check that both columns contain the same visible records and remain aligned. A filter on the worksheet does not itself guarantee that the trendline uses only those records: charts can be configured to show or hide data from hidden rows, and the trendline still follows the data actually included in its series. A separate helper series makes that inclusion explicit.

Add the trendline to the correct series

When a chart has several series, Excel can attach a trendline to the wrong one if you do not select carefully. Click a point in the helper series once to select the whole series, not just one data point. Confirm that the highlighted points are the subset you prepared, then add the trendline. In the Format Trendline pane, Excel identifies which series the line is associated with; if the fit looks unchanged from the full-data fit, remove it and check the selected series and its source ranges.

You may keep the original series as a visual reference. If both series have the same marker style, the subset can appear to be plotted twice. Give the helper series a distinct marker or color, or hide its markers while retaining the trendline. If the goal is to show only the selected points and their fit, remove the original series from the chart without deleting the worksheet data.

Choose a trendline type and interpret the result

Excel offers linear, exponential, logarithmic, polynomial, power, and moving-average trendlines. A linear trendline is a straight least-squares fit and is a sensible starting point when the relationship changes at a roughly constant rate. Other types impose different curve shapes and are not interchangeable. For example, an exponential fit requires appropriate positive data, while a polynomial can bend to follow local variation and may overfit a small subset.

Right-click the helper series’ trendline and choose Format Trendline to choose a type. The pane also provides options such as setting a polynomial order, displaying the equation on the chart, and displaying the R-squared value. Use the equation to inspect the fitted model, not as proof that the model is appropriate. R-squared describes fit to the selected observations under the chosen model; it does not establish causation or guarantee useful predictions. A very short range may produce a visually convincing fit that is not representative of the broader process.

Forecast Forward and Backward settings extend a trendline beyond the observed data; they do not select which observations are fitted. If you change those settings, distinguish the extrapolated portion from the actual data and avoid treating an extension as a measured value.

Common problems and checks

  • The line still reflects the full dataset: the trendline may be attached to the original series. Check the selected series and its X/Y source ranges.
  • The trendline is missing or incomplete: check for text values, blanks, errors, or mismatched X and Y ranges in the helper data. Each plotted point needs a corresponding X/Y pair.
  • The result changes when dates are irregular: use an XY (Scatter) chart for numeric dates or measurements. A category Line chart spaces categories by order rather than by elapsed time.
  • The chart stops updating: a copied subset is static. Refresh it when the source data or range limits change, or use a dynamic FILTER-based helper series.
  • The chart shows the wrong interval: confirm whether the bounds are meant to be inclusive. The example formula includes values equal to both limits.

Also verify that the X and Y columns have not been sorted independently. Sorting one without the other breaks the pairings and can produce a nonsensical fit. For date boundaries, enter real Excel dates in the limit cells, not date-looking text. If the chart uses a logarithmic axis or a non-linear trendline, interpret the displayed equation in light of that setting.

Frequently Asked Questions

Can I apply a trendline to only selected points in an existing series?

Not by selecting those points alone. Excel fits a trendline to a series, so make a separate series containing the selected points and add the trendline to that series.

Does filtering rows make Excel recalculate the trendline for visible rows only?

Not reliably as a way to define a trendline subset. Chart settings determine how hidden or filtered data is displayed, and a series may still include data you did not intend. A helper series with only the wanted values is clearer and easier to verify.

Can I use dates as the range limits?

Yes. Use actual Excel date values in the data and limit cells. For a fit based on actual time spacing, use an XY (Scatter) chart; a Line chart treats dates as ordered categories.

Will the trendline update when the range limits change?

It can if its chart series is based on a dynamic FILTER result and the chart references the resulting spill range. A manually copied subset will not update by itself; recopy the records or use a dynamic helper.

Can I show the trendline equation and R-squared?

Yes. Select the trendline, open Format Trendline, and enable the options to display the equation and R-squared value on the chart. These summarize the selected series and model, not the quality of the underlying data or a guarantee of future results.

Related

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.