zaro

How to ignore zeros in Excel graph?

Published in Excel Charting Zeros 6 mins read

To ignore zeros in an Excel graph, you generally need to prevent them from being plotted as data points. This can be achieved by changing how the chart interprets zero values or by modifying the source data to treat zeros as empty or non-existent values.

How Excel Charts Handle Zero Values

Excel charts, by default, treat a cell containing the numeric value 0 as a valid data point and will plot it at the zero mark on the axis. However, if a cell is truly blank (empty) or contains an error value like #N/A, most charts will treat it as missing data. Understanding this distinction is key to making a chart "ignore" zeros.

Direct Methods to Ignore Zeros in Charts

These methods directly impact how your chart renders zero values.

1. Adjusting Chart Settings for Hidden and Empty Cells

This is often the most straightforward method for line or scatter charts, especially when combined with data modification.

Steps:

  1. Select your chart.
  2. Go to the Chart Tools Design tab on the Excel ribbon.
  3. In the Data group, click on Select Data.
  4. In the "Select Data Source" dialog box, click the Hidden & Empty Cells button (usually in the bottom-left corner).
  5. In the "Hidden and Empty Cells Settings" dialog box, you'll see options for how Excel handles empty cells:
    • Gaps: Treats empty cells as missing data, creating a break or gap in the line/series. This is ideal for ignoring zeros if your zeros are converted to blank cells or #N/A.
    • Zero: Plots empty cells as a zero value on the chart. (This is often the default or what you want to avoid).
    • Connect data points with line: (For line and scatter charts) Draws a line between the data points on either side of the empty cells, effectively skipping the empty cell.
  6. Select Gaps to ignore data points, and then click OK twice.

Important Note: This setting primarily affects truly empty cells or cells containing #N/A errors. If your cells still contain the numeric value 0, this setting alone will not make the chart ignore them. For this to work with zeros, you need to first convert your zeros to blanks or errors in the source data (see Method 2).

2. Converting Zeros to Blanks or Errors with Formulas

This method directly modifies your source data so that Excel charts interpret zero values as missing data points.

  • Replacing Zeros with Blanks (""):
    You can use an IF statement to replace any zero values with an empty string (""). While Excel treats "" as text, charts often interpret it as an empty cell.
    Example Formula: =IF(A2=0,"",A2)

    • How it works: If cell A2 contains 0, the formula returns a blank string; otherwise, it returns the value from A2.
  • Replacing Zeros with #N/A Errors:
    Using the NA() function to return the #N/A error is often the most robust solution, as Excel charts are designed to explicitly ignore #N/A values, creating clear gaps.
    Example Formula: =IF(A2=0,NA(),A2)

    • How it works: If cell A2 contains 0, the formula returns #N/A; otherwise, it returns the value from A2.

Steps:

  1. Create a new column next to your data that you want to chart.
  2. Enter the formula (e.g., =IF(A2=0,NA(),A2)) in the first cell of the new column, referencing your original data cell.
  3. Drag the fill handle down to apply the formula to the rest of your data range.
  4. Use this new column as the data source for your chart.
  5. Ensure your chart's "Hidden and Empty Cells Settings" (as described in Method 1) are set to "Gaps" for line/scatter charts or the appropriate setting for other chart types.

Indirect Methods and Considerations

While these methods don't directly tell the chart to ignore zeros, they can help in managing their appearance or presence.

3. Hiding Zeros in Worksheet Display (Visual Only Impact)

You can configure Excel to display zero values as blank cells in the worksheet. This affects the visual appearance of your data on the sheet but does not change the underlying value from 0 to a blank or #N/A. Therefore, this method alone usually does not make a chart ignore a zero value because the chart still reads the numerical 0.

However, it's a setting worth knowing for general data presentation.

Steps:

  1. Click File > Options.
  2. In the "Excel Options" dialog box, select Advanced in the left pane.
  3. Scroll down to the section titled "Display options for this worksheet".
  4. From the dropdown menu, select the specific worksheet you are working with.
  5. To display zero (0) values as blank cells, uncheck the "Show a zero in cells that have zero value" check box.
  6. Click OK.

This will make cells containing 0 appear blank on your worksheet. Remember, for charts to ignore these zeros, you'll still likely need to combine this with Method 2 (converting to blanks/errors) and Method 1 (chart settings).

4. Filtering Source Data

If your data is in an Excel Table or you can apply filters, you can temporarily hide rows containing zeros from your chart's source data.

Steps:

  1. Select your data range, or ensure it's formatted as an Excel Table.
  2. Apply filters (Data tab > Filter).
  3. Click the filter arrow in the column header that contains the zeros you want to ignore.
  4. Uncheck 0 from the list of values to display.
  5. Click OK.

Your chart will then update to only display the visible data, effectively ignoring the filtered-out zeros. However, this is a dynamic approach and relies on the filter being active.

5. Using Conditional Formatting (Visual Only)

You can use conditional formatting to make zeros invisible in your cells by setting their font color to white (or matching the cell background). This is a purely visual change on the worksheet and has no impact on how the chart plots the underlying 0 value.

Choosing the Right Method

  • For line charts, scatter charts, or area charts where you want clear breaks for missing data, converting zeros to #N/A with a formula (Method 2) combined with the "Gaps" setting (Method 1) is usually the most effective and robust solution.
  • If you need to make cells look blank but the chart must still plot a zero, you can use the worksheet display option (Method 3), but be aware of its limitations for chart plotting.
  • For bar or column charts, zeros will typically result in bars of zero height, which might be acceptable. If you want to completely remove them, converting to #N/A or filtering might be necessary.
  • Filtering (Method 4) is good for quick, temporary exclusions.