Excel Scatter Plot: How to Ignore Zero Values

How-to basics · Updated November 21, 2026

Yes. To make an Excel scatter plot ignore zero values, replace the zeros you mean to exclude with #N/A in helper columns, or filter those rows out before charting; a numeric zero in the chart’s source data is normally plotted as a real coordinate. Use blanks only when they are genuinely missing values, because blank-cell behavior depends on the chart and its settings.

Why a scatter plot includes zero values

An XY scatter chart plots pairs of numbers: one value supplies the horizontal coordinate and another supplies the vertical coordinate. If a row contains X = 0 and Y = 12, Excel sees a valid point at (0, 12). It cannot know that the zero was intended to mean “no measurement” rather than a real result. The same is true when a formula evaluates to the number zero.

This differs from the visual impression that zero is merely an empty-looking cell. A zero is data; a blank is not. Also, the axis crossing at zero is separate from a plotted data point. Changing where the horizontal and vertical axes cross will not remove points whose coordinates are zero.

Before changing the chart, decide which situation applies:

  • Zero is a valid measurement: Keep it in the series. For example, a recorded temperature of 0 degrees or a measured distance of zero may be meaningful.
  • Zero is a placeholder for missing data: Exclude the affected row from the plotted series.
  • Only one field uses zero as a missing marker: Exclude rows based on that field, but retain valid zeros in the other coordinate.

Choose how to treat zeros before editing the chart

The safest method depends on whether you can change the source data and whether zeros have meaning elsewhere in the workbook. Avoid replacing source zeros with blanks throughout a workbook if other calculations rely on them. A helper range lets you keep the original measurements intact while preparing a separate set of values for the chart.

For a scatter plot, a point needs both an X and a Y coordinate. If either coordinate is missing, the pair generally should not be plotted. When both zero values mean “no observation,” exclude the entire row if either coordinate is zero. If only a zero in the Y column is a placeholder, exclude rows based on Y alone; an X value of zero may still be a legitimate position.

Use these options as a guide:

  • Helper columns with NA(): A dependable choice when you want to preserve the source table and make missing points clear.
  • Filtered source rows: Convenient for a quick chart or a report where excluded records should also be hidden from view.
  • Blank cells: Appropriate when cells are truly empty, but not a guaranteed way to suppress a numeric zero.
  • Chart settings: Useful for controlling how actual empty cells are drawn; these settings do not convert a numeric zero into a missing value.

Exclude zero-coordinate rows with helper columns

In Microsoft Excel for Microsoft 365 on Windows, helper columns are usually the clearest approach. Suppose X values are in A2:A6 and Y values are in B2:B6. Put headers such as “Chart X” and “Chart Y” in C1 and D1. In C2, enter this formula and fill it down through C6:

With X values in A2:A6 and Y values in B2:B6, enter this in C2 and copy it down to return either the valid X value or #N/A when the row is incomplete or has a zero coordinate:

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

With the same source layout, enter this in D2 and copy it down to return the matching Y value or #N/A for the same excluded row:

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

These formulas exclude a row if either coordinate is blank or zero. For example, if A2 is 4 and B2 is 9, the helper values are 4 and 9. If A3 is 0 and B3 is 7, both helper cells return #N/A, so that pair is not intended to appear as a point. The formulas do not change the original values in A or B.

Select the helper X and Y columns, including their headers, then choose Insert > Insert Scatter (X, Y) or Bubble Chart and select a scatter subtype. If the chart already exists, use Chart Design > Select Data to edit its series so the X values refer to column C and the Y values to column D. Keep the X and Y ranges the same length and in matching row order. Excel charts generally omit points containing #N/A.

Adjust the formula to exclude only one zero field

It is common for zero to be missing only in one column. For example, a study may use X = 0 as a valid baseline but use Y = 0 to mark a measurement that was not collected. In that case, the all-or-nothing formula above removes too many points: it excludes any row whose X value is zero.

To treat only zero or blank Y values as missing, use a condition based on B2 alone. With X in A2:A6 and Y in B2:B6, put the following in C2 and fill down to create chart X values:

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

With that same layout, put the following in D2 and fill down to create chart Y values:

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

Here, a valid X value of zero is retained if the Y value is present and nonzero. The blank check on A2 still prevents an incomplete coordinate pair from being sent to the chart. Adapt the checks to your own rule: if blank X values are impossible or should be handled differently, make that decision explicitly rather than copying a formula without checking what it excludes.

Blanks, formulas returning empty text, and #N/A

A truly empty cell is not the same as a cell containing the number 0. Excel’s chart controls let you choose how to display empty cells, but the options concern gaps, zero treatment, or connecting points for chart types that support lines. They do not tell Excel that every numeric zero in a scatter series should be ignored.

A formula that returns an empty string, such as a formula using "", can look blank in the worksheet while still behaving differently from an untouched empty cell in a chart. Chart behavior can vary with chart type and setup, so do not rely on an empty-looking formula result as a universal way to remove a scatter point. If the point must be excluded, NA() in a helper series makes the intention clearer.

Using NA() does show an error in the helper cells. That is expected: the helper range is chart-preparation data, not a replacement for the clean source table. If the visible errors are distracting, put the helper columns on a separate worksheet or hide them. When hiding columns or rows, check the chart’s hidden-data setting so the chart behaves as intended.

Use filters when you need to remove whole records

If you want the chart to use only qualifying records, filter the source table so rows with placeholder zeros are excluded. This can be a useful choice when you also want to inspect or export only the retained records. Apply filters to the relevant X and Y columns and exclude zero and blank entries according to your data rule, then create or update the scatter plot using the remaining rows.

Filtering is less suitable when you need to compare the chart with the complete source list, or when other users might clear the filter and unintentionally change what appears. Excel charts can also have settings that determine whether data in hidden rows or columns is included. In the chart’s Select Data Source dialog, review Hidden and Empty Cells and the option to show data in hidden rows and columns. If you need a chart that reliably omits specific records regardless of a worksheet filter, use helper values with #N/A.

For a one-off chart, a copied table containing only the valid pairs is another simple option. It is easy to understand, but it will not automatically stay synchronized with later edits to the original data unless you refresh or rebuild that copied range.

Set chart behavior for genuinely empty cells

To review empty-cell handling in Excel for Microsoft 365 on Windows, select the chart and choose Chart Design > Select Data. In the Select Data Source dialog, choose Hidden and Empty Cells. The available choices include displaying empty cells as gaps, as zero, or connecting data points with a line where applicable. The exact visual effect depends on the chart subtype; a marker-only scatter chart has no line to connect.

These controls are relevant when your source has actual blanks, but they are not a general “ignore all zero values” switch. If you select the option to show data in hidden rows and columns, hiding records will not necessarily keep them out of the chart. If you leave it cleared, hidden data can be omitted, but that behavior may be too dependent on worksheet visibility for a chart that needs a stable, documented rule.

On Excel for Mac, chart commands and dialog layouts can differ slightly by release, but the chart’s data-source and empty-cell controls serve the same purpose. Excel for the web has fewer chart-editing controls than the desktop app; if you cannot find a needed setting, open the workbook in desktop Excel. In all versions, the key distinction remains: an empty-cell setting does not reinterpret an entered zero as missing.

Common problems and checks

  • The point remains at zero: Check that the chart series uses the helper columns, not the original A and B ranges. In Chart Design > Select Data, verify both the X values and Y values.
  • A valid point disappears: Review the formula’s rule. The example using OR(A2=0,B2=0) deliberately removes a row if either coordinate equals zero.
  • The chart has an unexpected point at the origin: Check for formulas returning numeric 0, not just cells that look blank because of formatting. A custom number format can hide a displayed zero without changing its value.
  • The helper columns show errors: #N/A is the planned marker for excluded rows. Confirm that the chart references those columns and that valid rows still return numbers.
  • The chart changes after filtering or hiding rows: Review Hidden and Empty Cells and whether the chart is set to show hidden data.

One useful check is to click a plotted marker and inspect the highlighted source ranges in the formula bar or Select Data dialog. Confirm that each plotted X value is paired with its intended Y value; a mismatched range can make a chart look wrong even when the zero-exclusion logic is correct.

Frequently Asked Questions

Can I make Excel ignore every zero in a scatter plot without changing the data?
There is no general scatter-chart switch that treats every numeric zero as missing. Use helper columns with NA(), filter out the rows, or prepare a separate valid-pairs table. First confirm that zero is not a meaningful measurement.

Does changing the chart’s empty-cell setting remove zero values?
No. That setting controls how empty cells are represented. A cell containing the number 0 is a value, so Excel normally plots it at coordinate zero.

Will Excel plot a blank as zero in a scatter chart?
It depends on how the blank is represented and on the chart setup. A genuinely empty cell, a formula returning an empty string, and a numeric zero are not interchangeable. For a point you specifically need excluded, use #N/A in a helper series.

Can I keep X = 0 but ignore Y = 0?
Yes. Make the helper formulas check the Y column for zero, rather than checking both coordinates. Keep a separate check for blank X values if a valid pair must have both coordinates.

Does formatting zero to display as blank remove the point?
No. Number formatting changes what the worksheet displays, not the underlying value used by the chart. To omit the point, change the chart’s source data through a helper range or filter.

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.