Excel has no built-in setting to make a trendline ignore zero values, but you can exclude them from the chart series with a helper column. If your y-values are in B2:B20, enter =IF(B2=0,NA(),B2) in a nearby column and use that helper column for the chart’s y-values; Excel does not plot the resulting #N/A values, so they are left out of the trendline.
Why zero values affect a trendline
A trendline is calculated from the data points in its chart series. A zero is a real numeric value to Excel, not a marker for missing information. If a row contains a zero y-value, Excel ordinarily includes that point in the trendline calculation. This can pull a fitted line or curve toward zero, even when the zero actually means that a measurement was unavailable or not recorded.
For example, consider an XY scatter chart with points (1, 10), (2, 0), (3, 14), (4, 16), and (5, 18). A linear trendline fitted to all five points is y = 3.2x + 2. If the point at x = 2 is a placeholder and is excluded, the other four points produce y = 2x + 8. That is a substantial difference caused by treating the zero as data rather than as missing information.
There is no separate trendline checkbox for excluding zeroes. Trendline options control the model, such as whether it is linear or exponential, and may offer forecasting and equation display; they do not change which points belong to the series. To change the fitted data, change what the chart plots.
Make a helper column that omits zeroes
Keep your source data intact and create a chart-only helper range. Suppose the x-values are in A2:A20 and the y-values are in B2:B20. Put a heading such as Chart Y in C1. With y-values in B2:B20, enter this formula in C2:
=IF(B2=0,NA(),B2)
Copy the formula down through C20. A nonzero value is repeated in column C, while a zero produces the #N/A error. Excel charts do not plot #N/A values as data points, which keeps those rows from contributing to the trendline. The error is visible in the worksheet helper column, but the original data in column B remains unchanged.
Use this helper range as the series’ y-values. For a scatter chart, keep A2:A20 as the x-values and set C2:C20 as the y-values. For a chart with categories, use the category labels as before and replace the plotted values with the helper values. The chart’s markers and its trendline will then be based on the remaining points.
- Choose a helper column that is not part of another chart or calculation, so the change does not affect unrelated work.
- Include the helper heading when selecting the data if you want Excel to use it as the series name.
- Do not replace the original zeroes with blanks unless changing the source data is intentional; a helper range is easier to review and undo.
Update the chart to use the helper values
In Excel for Microsoft 365 on Windows, select the chart, then choose Chart Design > Select Data. In the Select Data Source dialog, select the relevant series and choose Edit. For a scatter chart, set Series X values to the original x-value range and Series Y values to the helper range. For example, keep the x range A2:A20 and change the y range from B2:B20 to C2:C20. Confirm the dialog changes.
If you have not made the chart yet, select the x-values and helper values and insert the chart. For an XY chart, select the two ranges and choose Insert > Insert Scatter (X, Y) or Bubble Chart. If the ranges are not next to each other, you can select the first range and then select the second while holding Ctrl on Windows. Alternatively, insert the chart and assign the ranges in Select Data.
To add or review a trendline, select the chart and choose Chart Design > Add Chart Element > Trendline. Choose the desired type, or select More Trendline Options for additional settings. The trendline updates to match the series currently plotted. On Mac, the chart design controls and names are similar, though their placement can vary by Excel release; use the chart’s Chart Design tab and Select Data dialog. In Excel for the web, chart editing options can differ from the desktop app, so use the desktop version if the necessary series range controls are unavailable.
When the x-value is zero instead
Sometimes the row to exclude is identified by a zero in the x-value column, rather than a zero y-value. In the same layout, with x-values in A2:A20 and y-values in B2:B20, enter this in C2 if you want to omit a row whenever either coordinate is zero:
=IF(OR(A2=0,B2=0),NA(),B2)
Copy it down and use A2:A20 for the chart’s x-values and C2:C20 for its y-values. The #N/A result in the y-range prevents that row from appearing as a point, including when the x-value is zero. This formula also excludes rows where the y-value is zero. If you mean to exclude only x-values of zero while retaining legitimate y-values of zero, use a condition that checks only A2 instead; decide which values define an invalid row before changing the chart.
For a chart where only the y-value matters, the simpler first formula is usually clearer. Avoid removing a point merely because its x-coordinate is zero unless that coordinate really means the observation should not be analyzed. In a scatter chart, x = 0 can be a meaningful measurement.
Choose the right chart type
The helper-column approach works with common chart types, but the meaning of x-values differs. An XY scatter chart uses numeric x-values and positions points according to their values. It is generally the better choice when your x-values are measurements, dates that have uneven intervals, or any numeric scale where spacing matters.
A line chart usually treats its horizontal axis as categories, such as labels or dates, and spaces categories evenly. Its trendline is based on the chart’s plotted category positions rather than necessarily reflecting the actual elapsed time between dates. If the time gaps are irregular and the trend is meant to represent a numerical relationship over time, consider a scatter chart with actual date values as x-values. In either type, exclude placeholder zeroes from the plotted series before interpreting the trendline.
Also check that the chart contains the intended series. A trendline belongs to a specific data series, not to every value visible in the workbook. If a chart has multiple series, select the one whose trendline you want to change before adding or editing that trendline.
Other ways to handle zeroes
If zero means “not measured,” a helper column is usually the most transparent solution: it preserves the raw data and makes the chart’s exclusion rule visible. You can also filter the source table to remove zero rows from view, but chart behavior can depend on whether the chart is set to include data in hidden rows and columns. A helper range avoids relying on that setting and makes the plotted values explicit.
Changing zeroes to blank cells can be suitable when blanks genuinely represent missing data and you understand the chart’s empty-cell behavior. However, a formula returning an empty string is not always treated the same way across chart types and settings. #N/A is a more dependable way to mark a point that should not be plotted, though it does display as an error in the helper worksheet range.
Another option is to make a separate filtered dataset containing only valid rows, then chart that dataset. This can be useful when many analyses must use the same exclusions, but it takes more setup and may require formulas or filtering steps to keep it current. Whatever method you choose, document the rule—for example, “zero means no reading”—so another person does not mistake excluded observations for forgotten data.
Common problems and checks
- The trendline still includes zeroes: Confirm the chart series’ y-values point to the helper range, not the original column. Editing a worksheet formula alone does not change the chart’s source range.
- A point disappears that should remain: Check whether its value is truly zero or only displays as zero because of number formatting or rounding. A small underlying value may look like zero while not equaling zero.
- Text zeroes are not excluded: Values imported as text may not behave like numeric zeroes in comparisons. Convert the source values to numbers or clean the imported data before relying on the helper formula.
- The chart shows a gap: A gap is expected where a point is omitted. For a scatter chart, the omitted point will not appear; in a line chart, the visible line may break at that position depending on chart settings.
- The equation or R-squared value looks different: That is expected after the chart series changes. Excel recalculates the displayed trendline equation and fit statistics from the points that remain.
- The helper errors appear on the chart: Make sure the chart references the helper range as intended and check whether the specific chart type is displaying errors unusually. The #N/A values should not be plotted as ordinary points.
Frequently Asked Questions
Can Excel make a trendline ignore zero without a helper column?
No. Excel does not provide a dedicated ignore-zero option for trendlines. You need to exclude those values from the chart series, for example with a helper column that returns #N/A for zeroes, or by building a separate dataset without those rows.
Does Excel treat zero as a blank in a chart?
No. A numeric zero is a valid plotted value and is normally included in a trendline. A blank or #N/A is different, though how blanks appear can depend on chart type and chart settings. Use #N/A when you need a clear exclusion from the plotted data.
Will the original data be changed?
Not when you use a helper column. The formula creates a separate chart range and leaves the source values untouched. You can remove the helper range or change the chart’s source range later without rewriting the original observations.
Can I ignore zeroes only for the trendline but still show them as chart points?
Not with one ordinary series and a built-in trendline, because the trendline is fitted to the points in its series. A practical workaround is to make separate series: one containing all points for display and another containing only valid points for the trendline. Add the trendline to the filtered series and format that series’ markers or line so it does not duplicate the visible data.
What if zero is a real measurement?
Keep it in the series. Excluding a real zero changes the dataset and can make the trendline misleading. Remove zeroes only when they represent missing, invalid, or otherwise intentionally excluded observations, and apply the same rule consistently.
Related
- Excel Power Query: Extract Text from a String
- Extract Email Addresses From Text in Excel 365 Easily
- Remove Spaces in an Excel Column: Step-by-Step
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Traffic Light Chart in Excel: Build a Status DashboardA traffic light chart in Excel is a visual status indicator that uses green, yellow, and red symbols…
- Excel Data Model: Add a Calculated Column in Power PivotYes. In Excel for Microsoft 365 on Windows, open the Data Model in Power Pivot, select the table,…
- How to Flip a Bar Graph in Excel: Reverse Its OrderTo flip the order of bars in an Excel chart, right-click the category axis, choose Format Axis, and…
- Reverse Waterfall Chart in Excel: How to Make OneTo make a reverse waterfall chart in Excel for Microsoft 365 on Windows, enter the starting total and…
- Excel Line Chart Comparing Two Years: A Clear SetupTo make an Excel line chart comparing two years, put the shared time periods in the first column…
- How to Make a Flow Chart in Word Online: Step by StepTo make a flow chart in Word Online, open a document, choose Insert > Shapes, draw and label…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.