Excel applies a trendline to a chart series, not to a hand-selected handful of points within a series. To fit only part of your data, make a helper series containing the points you want included, add that series to the chart, and apply the trendline to it.
This approach works especially well for an XY (scatter) chart, where each point has a numeric x-value and y-value. You can keep the original series visible, hide the helper points, and show a trendline fitted only to the selected range.
Why Excel needs a separate series
A trendline is a regression calculated from the plotted data in one chart series. Excel’s chart controls do not provide a command to select a portion of an existing series and attach a trendline just to those points. If you add a trendline to the original series, Excel uses all eligible points in that series.
The practical workaround is to give Excel a second series that contains only the observations to fit. The original series remains available for context, while the second series supplies the subset for the regression. This is useful when the relationship changes over time, when you want to fit a calibration range, or when early observations are not representative of a later period.
For example, suppose a chart shows monthly sales from January through December, but a promotion changed the sales pattern in July. You might want a trendline for July through December without removing January through June from the chart. A helper series can include only the July-to-December points, so the line is based on those observations alone.
Prepare the data and choose the points
Start with a clear definition of the subset. For a continuous range, use a lower and upper x-value, such as dates from July 1 through December 31. For selected, nonconsecutive observations, use a separate inclusion flag or construct a helper table containing only those rows. Avoid deciding which points to include simply because they make the trendline look more convincing; the selection should have a reason tied to the question or the data collection process.
- Put the independent variable, or x-values, in one column and the measured values, or y-values, in another.
- Make sure the x and y values on each row belong together, and check for text values, blank cells, and errors.
- For a date range, use real Excel dates rather than date-like text. Excel stores dates as numbers, which lets a scatter chart position them along a numeric axis.
- Keep the original data intact. Helper columns make the selection visible and easier to review.
For a scatter chart, the x and y columns must be identified separately. A standard line chart instead uses category labels along its horizontal axis; that can be suitable for evenly spaced periods, but it does not treat category labels as measured x-values in the same way as a scatter chart.
Create a helper series for a continuous range
Here is a concrete setup for selecting points by an x-value range. Assume the x-values are in A2:A11, the corresponding y-values are in B2:B11, and the lower and upper limits are entered in F1 and G1. For instance, F1 and G1 could contain the first and last dates to include. Put helper x-values in C and helper y-values in D.
With x-values in A2:A11, y-values in B2:B11, and the inclusive lower and upper limits in F1 and G1, enter this formula in C2 and fill it down through C11:
=IF(AND(A2>=$F$1,A2<=$G$1),A2,NA())
With the same layout, enter this formula in D2 and fill it down through D11:
=IF(AND(A2>=$F$1,A2<=$G$1),B2,NA())
For rows inside the limits, the helper columns return the original x and y values. For rows outside them, NA() returns the #N/A error, which Excel charts do not plot as a data point. If the limits include rows 7 through 10, for example, the helper series contains four usable point pairs; the trendline is calculated from those four points, not from all ten.
These formulas use inclusive boundaries. If your limits are in the wrong order, the helper series will contain no points, so check that F1 is less than or equal to G1. To select nonconsecutive rows, a simple date or value boundary is not enough: use an inclusion column, or make a compact helper table that copies only the chosen x-y pairs.
Add the helper data to an XY chart
- Select the existing chart. If you do not have one yet, select the original x and y data and use Insert > Insert Scatter (X, Y) or Bubble Chart, then choose a scatter subtype.
- On Windows, use Chart Design > Select Data > Add to add another series.
- Enter a series name if useful, then set the series X values to the helper x range, such as C2:C11, and the series Y values to the helper y range, such as D2:D11. Do not include the header cells in those ranges.
- Confirm the dialog boxes. The chart should now contain the original series and the subset series. The helper series may initially appear as markers or a connecting line.
Excel for Mac has similar chart data and trendline controls, although the chart formatting pane and ribbon layout can differ slightly from Windows. In Excel for the web, some chart editing controls may be more limited; if you cannot add or format the series as described, open the workbook in the desktop app.
Apply and format the trendline
Select one of the helper-series points, taking care not to select the original series. On Windows, use Chart Design > Add Chart Element > Trendline and choose a type, such as Linear. You can also use the chart’s Add Chart Element control when it is available. To adjust the fit, open Chart Design > Add Chart Element > Trendline > More Trendline Options and check that the selected series is the helper series.
The helper points do not have to be visually prominent. Select the helper series and use Format Data Series to remove its line and set its markers to none, or format the markers in a subtle color. Keep the trendline visible and give it a clear legend name, such as July–December fit. If you remove the helper series from the chart entirely, its trendline will no longer have a plotted series to describe.
To show the fitted equation or R-squared value on the chart, select the trendline’s options and enable the corresponding display setting. The equation describes the subset series. R-squared also applies only to the points used for that fit; it is not a score for the entire original data set.
Choose a suitable trendline type
A linear trendline is a reasonable starting point when the subset appears to change at a roughly constant rate. Other choices are intended for different shapes, not as automatic ways to improve a fit:
- Exponential: can model growth or decay at a changing rate, but the y-values must be positive.
- Logarithmic: can model a rapid change that levels off; the x-values must be positive.
- Polynomial: can represent a curved relationship. Higher orders can bend sharply between points, so a close-looking curve may be misleading, especially with few observations.
- Moving average: smooths a series over a chosen number of periods rather than fitting a conventional regression equation to a selected x-range.
Use the simplest type that matches the process you are trying to describe. A trendline is a model of an observed pattern, not proof that the pattern will continue. Avoid extrapolating far beyond the selected points without a sound reason.
Other ways to fit only some observations
If the chart does not need to show the other observations, the simplest option is to create a chart from only the subset and add a trendline to that chart’s series. This avoids helper points, but it also removes the broader context from that chart. A second chart can be useful when you want both a full-data view and a close-up fit.
Another option is to build a separate helper table containing only the observations to include. This is often clearer for nonconsecutive selections than filling the unused rows with #N/A. It also makes it easy to audit the exact points used. If the selection changes frequently, a formula-driven helper series is less repetitive than manually copying rows, provided the selection rule is documented and checked.
Filtering rows in the worksheet is not always an ideal substitute. Chart behavior for hidden or filtered data depends on the chart’s data settings, and filtering the original series may hide points you still want to display. A dedicated helper series makes the fit’s inputs explicit and keeps the original chart series separate.
Common problems and checks
- The line still reflects all observations: the trendline may have been added to the original series. Select the trendline and verify that it belongs to the helper series, or remove it and add one to the helper points.
- No helper points appear: check that the bounds contain actual x-values, that dates are stored as dates, and that the limits are in the right order. A column of #N/A values means the inclusion test matched no rows.
- The trendline option is unavailable: confirm that you selected a supported, nonempty data series rather than the chart area, an axis, or the original series. Some specialized chart types do not support trendlines; an XY scatter chart is a reliable choice for numeric x-y data.
- The fitted line looks misplaced: inspect the series’ x and y ranges and confirm they cover the same rows. In a scatter chart, an accidentally selected category-style chart or incorrect x range can change the apparent relationship.
- The equation has unexpected values: Excel may display rounded coefficients. For calculations, do not assume the displayed equation has full precision. Also check units, data entry, and whether the subset is large enough to support the chosen model.
One frequent misconception is that choosing a polynomial or exponential line automatically makes the model more accurate. A more flexible curve can follow the selected points closely while describing the underlying process poorly. Compare the model with the data and explain why that subset and trendline type were chosen.
Frequently Asked Questions
Can I add a trendline to only selected points in Excel?
Not directly within a single series. Create a helper series containing only the selected points, then add the trendline to that series.
Can I use this method with dates?
Yes. Store dates as real Excel date values, use the start and end dates as the helper-series limits, and use an XY scatter chart when you want date spacing to be treated numerically.
Can I make a trendline for nonconsecutive points?
Yes. Create helper columns or a separate helper table that includes only the chosen x-y pairs. A single lower and upper limit selects a continuous range, not arbitrary scattered rows.
Does the trendline use hidden or filtered data?
That depends on the chart’s data settings and how the series is built. A dedicated helper series is more predictable because it explicitly identifies the points intended for the fit.
Can I show the full data and the subset trendline together?
Yes. Leave the original series on the chart, add the helper series, and apply the trendline to the helper series. Format the helper markers or line discreetly so the fitted line is easy to distinguish.
Will the trendline update when the data changes?
If the helper formulas and chart ranges include the changed rows, the plotted subset and its trendline update with the data. Check the range limits and chart series ranges when adding rows beyond the original data area.
Related
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
- Excel Split Contents of a Cell Into Two Cells
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline Intercept: Meaning and How to Set ItIn Excel, a trendline intercept is the estimated y-value where the trendline crosses the vertical axis, which is…
- Google Sheets Distribution Charts: Types and SetupA Google Sheets distribution chart is usually a histogram: it groups numerical values into ranges, or bins, and…
- Excel Pivot Table Min and Max Values: How to Show ThemTo show a minimum or maximum in an Excel PivotTable, put a numeric or date field in the…
- Power BI and Excel Pivot Tables: How They Work TogetherYes—you can analyze a Power BI semantic model with an Excel PivotTable by using Power BI’s Analyze in…
- Gantt Chart Maker Excel Template: Build a Project PlanYes. You can make a Gantt chart in an Excel template by listing tasks, start dates, and durations,…
- Excel Power Pivot: What It Does and When to Use ItYes. Excel Power Pivot is a business intelligence (BI) and data-modeling feature built into certain Windows versions of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.