Excel Trendlines: Can You Ignore Zero Values?

How-to basics · Updated November 21, 2026

Excel does not provide a built-in Ignore zero option for a trendline. To leave zero-valued observations out of the calculation, make a helper series that returns #N/A for the rows you want excluded, chart that series, and add the trendline to it.

How Excel treats zero values

A trendline is calculated from the data points in the chart series it belongs to. A numeric zero is a real value, so Excel normally includes it in the fit; it is not treated like an empty cell. That can pull a line or curve toward zero and change its equation and R-squared value.

For example, suppose sales are 10, 0, 18, and 20 across four periods. If the zero means sales were genuinely zero, it belongs in the trendline. If it means the figure was not recorded, including it would misrepresent the data. Excel cannot infer that distinction from the value alone: you have to decide what zero means in your dataset.

There is no trendline setting that says to omit all zero observations. Chart settings for empty cells govern blanks, not numeric zeros. Similarly, changing the trendline type—linear, exponential, logarithmic, polynomial, or power—does not add an ignore-zero rule.

Exclude zeros with helper columns

A dependable approach is to create chart-ready values in separate columns. Excel charts generally do not plot #N/A values, so points marked this way are left out of the series and its trendline. Keep the original data unchanged; the helper columns make the exclusion visible and reversible.

Assume the original data has X values in A2:A5 and Y values in B2:B5. For a scatter chart, suppose a row should be excluded when either its X or Y value is zero. Put this formula in C2, the helper X column:

With X values in A2:A5 and Y values in B2:B5, enter this in C2 and fill down. For the example rows (1, 10), (2, 0), (3, 18), and (4, 20), it returns 1, #N/A, 3, and 4.

=IF(OR(A2=0,B2=0),NA(),A2)

With the same source layout, enter this in D2, the helper Y column, and fill down. It returns 10, #N/A, 18, and 20 for those example rows, so the second row is missing from the chart as a pair.

=IF(OR(A2=0,B2=0),NA(),B2)

Both helper columns must apply the same row-exclusion test. Otherwise you can end up with mismatched X and Y values or an unintended point. If zero is only invalid in Y, use a condition based on B2 alone in both helper formulas; do not discard an X value of zero if it is a valid measurement.

Create the chart and add the trendline

  1. Put the helper X values and helper Y values in adjacent columns with clear headings.
  2. Select those helper columns, including their headings, and choose Insert > Insert Scatter (X, Y) or Bubble Chart. Pick the scatter style that suits the data.
  3. Select the chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. Select a trendline type in the Format Trendline pane.
  4. Optionally select Display Equation on chart or Display R-squared value on chart in the pane. These describe the trendline fitted to the plotted helper series, not the untouched source values.

In the example, the scatter chart contains three points: (1, 10), (3, 18), and (4, 20). The zero-Y observation is not plotted, and the trendline is calculated from the three remaining points. To update the chart when new rows are added, extend the helper formulas and the chart’s source range, or use an Excel table so its data range can expand with new entries.

Choose the right exclusion rule

The key decision is not how to hide a zero, but which observations should count. Make the rule match the meaning of the data and use it consistently.

  • Zero means missing or not applicable: exclude that row, and consider documenting the reason so readers do not mistake the omission for an Excel quirk.
  • Zero is a real observation: include it. For instance, zero units sold or zero rainfall may be an important result rather than a data gap.
  • Only one variable makes a row unusable: test that variable only. In a scatter plot, a zero X value may be legitimate even when a zero Y value is not.
  • A particular code means missing: test for that code rather than every zero. If the source uses -999 as a missing-value marker, for example, excluding all zeros would remove valid measurements instead.

Decide whether you are excluding zero in the X variable, the Y variable, or either one. That distinction matters especially for scatter charts, where both coordinates define a point. For a line chart with category labels, the horizontal axis is usually made of categories rather than numeric X measurements, so the question is often whether to omit a zero in the plotted Y series.

Blanks, zeros, and chart gaps are different

A blank cell and a cell containing 0 are not interchangeable. In a line chart, Excel has options for how to display empty cells, such as leaving a gap or connecting points with a line. Those options do not remove numeric zero values from a trendline calculation. A formula that returns an empty string is also not the same as a numeric zero, and chart behavior can depend on the chart type and how that series is constructed.

Returning NA() in a helper series is useful because it marks a value as unavailable rather than quietly replacing a meaningful number with a blank. In a chart, the resulting #N/A is normally not drawn as a point. The worksheet will show the error in the helper cell, however, so keep the helper columns separate from reports that expect clean-looking cell values. If you need an uncluttered view, hide the helper columns or place them on a supporting worksheet; check that the chart still displays as intended after doing so.

Trendline choices and limits

Removing a zero point does not make every trendline type appropriate. A linear trendline can fit a range of positive and negative values, while some other types have stricter input requirements. Logarithmic trendlines require positive X values; exponential and power trendlines require positive data values for the fitting method. If the remaining points violate the chosen type’s requirements, Excel may not be able to calculate it or may give an unsuitable result.

Also consider how many observations remain. A polynomial with a high order can closely follow a small set of points without describing a useful general pattern. A trendline equation and R-squared value are summaries of the selected points, not proof that the model is appropriate or that omitted rows were correctly classified. Record the exclusion rule and review the chart against the source data before sharing it.

Common mistakes and checks

  • Hiding zero with formatting: a custom number format or white font may make zero invisible, but it remains in the data and can still affect the trendline.
  • Using a blank-cell chart option: this controls empty cells, not numeric zeros. It does not replace the helper-series method.
  • Changing the source value to blank or deleting the row: this alters the source data and makes the decision harder to audit. A separate helper series preserves the original.
  • Applying inconsistent tests: if one helper formula excludes a row and the other does not, the chart may not represent the intended X-Y pairs. Use the same row-level condition for both coordinates.
  • Adding a trendline to the original series: the helper columns only help if the chart and trendline use the helper series. Confirm the selected series in the chart before interpreting the equation.

If the plotted points look right but the equation seems unexpected, check the series range and inspect the helper columns row by row. Also confirm that the trendline is attached to the intended series, particularly in a chart with multiple series. When using a filtered chart or hidden rows, do not assume that hiding data is equivalent to the explicit exclusion rule; helper data makes the rule easier to see and verify.

Frequently Asked Questions

Can I make an Excel trendline ignore zero without changing my original data?
Yes. Leave the source columns intact, create helper columns that return NA() for rows to omit, and build the chart and trendline from those helper columns.

Does Excel have an Ignore zero checkbox for trendlines?
No. Excel’s trendline options let you choose and format a trendline, but they do not include a setting to exclude zero-valued points automatically.

Does returning an empty string exclude zero from a trendline?
It may result in a blank-looking cell, but it is not as clear or dependable as marking an excluded chart value with NA(). Use helper values that make the exclusion rule explicit, then verify the plotted series.

Can I exclude zero only from the Y values?
Yes. If zero Y values are invalid but zero X values are valid, make both helper formulas return NA() when Y is zero, while otherwise passing through their respective X and Y source values.

Will this work with a line chart as well as a scatter chart?
It can work with either, but they represent horizontal positions differently. Scatter charts use numeric X-Y pairs; line charts usually use category positions and a Y series. Select the chart type that matches what your axes mean.

Does the displayed trendline equation update when the data changes?
It updates when the chart’s plotted helper data changes, provided the chart range includes the updated rows. Make sure formulas are filled down and newly added data is included in the chart source range.

Related

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.