To add a trendline to only part of an Excel graph, make a helper data series containing values for the section you want and missing values elsewhere, add that series to the chart, then add a trendline to the helper series. Excel applies a trendline to a data series, not directly to a hand-selected stretch of one series, so separating the portion is the practical workaround.
What a trendline represents in Excel
A trendline is a mathematical fit to the plotted values in a chart series. A linear trendline, for example, estimates a straight line that best fits the selected data. If you add one to a series containing an entire year of monthly results, Excel calculates the fit using all the values in that series. Zooming in on the chart or selecting a visible section of the line does not limit the calculation to that section.
To fit just a portion, the chart needs a separate series that contains just the points in that portion. You can keep the original series visible for context and attach the trendline to the helper series. This distinction matters: hiding or cropping part of a chart is not the same as excluding those points from the trendline calculation.
A partial trendline can help show a local pattern, such as sales growth after a product launch or temperatures during a particular season. It should not be confused with a claim that the same pattern continues outside the selected interval. A short segment may produce a neat-looking line even when the broader data behaves differently.
Create a helper series for a selected interval
Here is a numeric example. Suppose A2:A11 contains x-values 1 through 10, B2:B11 contains measurements, and you want a trendline only for x-values 4 through 7. Enter 4 in H2 and 7 in I2 as the lower and upper bounds. With the data and bounds in those cells, enter this formula in C2 and fill it down through C11:
=IF(AND(A2>=$H$2,A2<=$I$2),B2,NA())
For example, if B2:B11 contains 12, 15, 14, 18, 21, 19, 24, 26, 25, and 30, the helper series returns the original values 18, 21, 19, and 24 for x-values 4, 5, 6, and 7. For the other rows it returns #N/A. Excel charts generally omit #N/A points, leaving the helper series with only the chosen section to fit.
- Select the existing chart. On Windows, go to Chart Design > Select Data.
- In the Select Data Source dialog, choose Add. Set the series name to a useful label, such as Selected interval.
- For a standard line chart using the same category labels as the existing series, set Series values to C2:C11. If the chart does not already use the labels in A2:A11, check the Horizontal (Category) Axis Labels range as well.
- Choose OK to add the series. The helper values may appear as a second plotted line or set of markers.
- Select the helper series, then use Chart Design > Add Chart Element > Trendline > Linear. Choose More Trendline Options if you need a different fit or settings.
To leave the original data as the visible line while showing only its fitted trendline for the interval, format the helper series itself with no line and no markers. Select the helper series, open Format Data Series, and set its line and marker options to none. The trendline is a separate chart element attached to that series, so it can remain visible. If you want readers to see which points were used, leave the helper markers visible or add a brief note to the chart.
Use the right chart type for the x-values
The helper-series approach depends on what the horizontal axis means. In a line chart, Excel normally treats the x-axis as categories in order. Even if the labels look like dates or numbers, the spacing may be equal from one category to the next. That can be appropriate for evenly spaced observations, such as one value per month, but it is not always appropriate for measurements taken at irregular intervals.
If the x-values are genuine numeric measurements or dates with meaningful spacing, consider an XY (Scatter) chart. A scatter chart uses numeric x-values as coordinates, so a point at x = 10 is positioned farther from x = 2 than from x = 8. To add a partial helper series to a scatter chart, provide both an x range and a y range for the added series. One option is to create a helper x column as well as the helper y column.
With x-values in A2:A11 and bounds in H2 and I2, enter this in D2 and fill down to make x-values outside the chosen interval unavailable to the chart:
=IF(AND(A2>=$H$2,A2<=$I$2),A2,NA())
With the same layout and y-values in B2:B11, the helper y formula in C2 is =IF(AND(A2>=$H$2,A2<=$I$2),B2,NA()). For the example bounds 4 and 7, D contains x-values 4, 5, 6, and 7 in the selected rows, and C contains the matching measurements; the other rows return #N/A. Add the series through Chart Design > Select Data > Add, then set its X values to D2:D11 and its Y values to C2:C11. A line chart can be simpler when its categories are equally spaced; a scatter chart is the more faithful choice when horizontal distance represents actual numeric distance.
Choose a trendline that fits the question
After the helper series is in place, choose the trendline type based on the relationship you are trying to describe, rather than on which option looks most persuasive. Excel offers several trendline types, and they do not all make the same assumptions.
- Linear: Fits a straight-line relationship. It is a useful starting point for a steady increase or decrease over the selected interval.
- Exponential: Models values that rise or fall at a rate related to their current size. It is not suitable when the selected y-values include zero or negative values.
- Logarithmic: Models a pattern that changes quickly at first and then levels off. Its x-values must be positive.
- Polynomial: Can represent curvature. Excel lets you choose an order; a higher order can follow bends in the sample closely but may make a short segment look more meaningful than it is.
- Power: Models a power relationship and requires positive x- and y-values.
- Moving Average: Smooths a series over a chosen number of points. It is a rolling average, not the same kind of fitted equation as a linear or polynomial trendline.
To inspect settings, select the trendline and open Format Trendline. Depending on the selected type, you can adjust options such as polynomial order, moving-average period, or forecast distance, and you can choose to display the equation or R-squared value on the chart. The equation and R-squared value describe the points in the helper series, not the full original series.
Interpret the result without overstating it
A trendline is a summary of selected data, not proof of cause or a guarantee about future values. A linear trendline through four points can be useful for describing that local interval, but four points provide limited evidence about a lasting pattern. If the data contain outliers, a trendline may be pulled toward them. If the interval was chosen after looking for the steepest rise, the displayed fit can also give a biased impression of the overall behavior.
Excel can display a trendline equation and R-squared value, but neither is a substitute for understanding the data. R-squared describes how closely the selected observations fit the chosen model; a high value does not show that the model is appropriate or that the relationship is causal. A more complex polynomial may raise the fit statistic simply by bending around the observed points. When a chart is for a report, identify the dates or x-values used and name the trendline type.
Forecast settings deserve particular care. A forecast extension extrapolates the selected trendline beyond its data. That is a mathematical continuation, not a prediction that accounts for business changes, seasonality, or other causes not represented in the model. On a category-based line chart, forecast distance follows category steps; with a scatter chart, the horizontal axis is numeric. Check which interpretation fits your data before presenting an extension as a forecast.
Common problems and how to fix them
- The trendline follows the whole graph. It was probably added to the original series. Select the helper series first, then add or format its trendline.
- The helper line appears broken or shows gaps. That is expected outside the selected interval when the helper cells return #N/A. Check that the selected interval itself has numeric y-values and that the formulas were filled through the intended rows.
- The trendline does not appear. Confirm that the chart type supports trendlines and that the helper series contains plottable values. Some trendline types also require positive values. Try a linear fit as a diagnostic if appropriate.
- The interval selects the wrong rows. Check the values and data types in the boundary cells. A date stored as text may not compare as intended with real Excel dates. For date-based bounds, use actual date values in both the data and the boundary cells.
- The line looks mathematically wrong. A category axis may be treating unevenly spaced x-values as equally spaced. Use an XY (Scatter) chart if numeric spacing matters.
- The series was added but has the wrong range. In Select Data, edit the helper series and check its series values. For a scatter chart, verify both the X values and Y values ranges.
Trendlines are available for many common two-dimensional chart types, including line, column, bar, area, stock, scatter, and bubble charts, but not every chart configuration supports them. Stacked charts and 3-D charts are common cases where a trendline is unavailable. If the Trendline command is disabled or missing, changing to a supported, unstacked 2-D chart may resolve the issue.
Excel for Windows, Mac, and the web
The steps above target Excel for Microsoft 365 on Windows. In Excel for Mac, the chart design controls and Select Data dialog are also used to add a series and a trendline; depending on the current ribbon layout, select the chart and look under Chart Design for Add Chart Element or Select Data. You can also Control-click the relevant series to access its context menu. The wording or placement can vary slightly between builds, but the key is to select the helper series rather than the original one.
Excel for the web supports many chart editing tasks, but some detailed chart-formatting options can differ from the desktop application. If you cannot set the trendline type or format the helper series as needed in the browser, open the workbook in the desktop app. Keep the helper columns in the worksheet so the chart continues to update when the source data changes.
Frequently Asked Questions
Can I add a trendline to only selected points without making another series?
No. Excel attaches a trendline to a data series, so the dependable method is to create a series containing only the points you want included. You can hide that series line and markers while leaving its trendline visible.
Will filtering the chart to a date range make the trendline use only visible points?
Do not assume that hiding or filtering points will produce the precise partial fit you want. A dedicated helper series makes the included observations explicit and is easier to check. Verify the trendline against the values in that series.
Can I use a partial trendline with dates?
Yes. Put the start and end dates in boundary cells and make the helper series return values only for dates within that interval. Use actual Excel date values, not date-looking text. For irregular dates where spacing matters, use an XY (Scatter) chart and supply helper x- and y-values.
Can I show the equation for just the selected section?
Yes. Select the trendline attached to the helper series, open Format Trendline, and enable Display Equation on chart. The equation represents the helper series points only. Make sure the chart label or caption makes that scope clear.
Why does the trendline change when I edit the helper data?
The trendline recalculates from the values in its attached series. Changing the boundary cells, source measurements, or helper formulas changes which observations are included, so a different fitted line is expected.
A practical check before sharing the chart
Before using the chart in a presentation or report, confirm the helper series includes exactly the intended interval, that the trendline is attached to that series, and that the x-axis treats spacing correctly. Label the interval and trendline type, and avoid implying that a local fit describes the entire dataset. Those checks make a partial trendline both easier to reproduce and less likely to mislead.
Related
- Excel: Specify Which Duplicate to Remove?
- Excel Solver With Binary Options: Setup and Examples
- Excel Solver With Integer Values: Setup and Examples
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- T-Test Distribution Graph: How to Read and Interpret ItA t-test distribution graph is a picture of the t-distribution: a symmetric, bell-shaped probability curve centered at zero.…
- Excel Power Pivot Table Tutorial for BeginnersIn Excel for Microsoft 365 on Windows, enable Power Pivot, add your data tables to the workbook Data…
- Excel Combo Chart: Change a Bar to a LineTo change one bar or column series to a line in an Excel combo chart, select the chart,…
- Excel Pivot Table Filter: Less Than an Amount?To filter a PivotTable to amounts less than a threshold in Excel for Microsoft 365 on Windows, open…
- How to Make an Org Chart in ExcelThe easiest way to make an org chart in Excel for Microsoft 365 on Windows is to use…
- Excel Combo Chart: How to Combine Bars and a Line in ExcelTo make a bar-and-line combo chart in Excel for Microsoft 365 on Windows, select your data, choose Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.