Hide Zero Percent in Excel Charts (3 Ways)

This article explains how to hide zero percent values in Excel charts, covering three reliable methods: formatting, formulas, and chart settings. You will learn which approach works best for different chart types and how to avoid common pitfalls.

Why Zero Percent Values Clutter Your Chart

When your data contains zero percent values, Excel plots them as points on the axis or as empty-looking bars. This can make a chart misleading, especially if the zero is a placeholder rather than a real measurement. For example, a monthly sales report might show 0% growth for months with no data entry, which visually suggests a decline. Hiding these zeros clarifies the story your chart tells.

Method 1: Format the Axis to Hide Zero Values

This works best for line charts and scatter plots where zero values appear as points on the baseline. Right-click the vertical axis, choose Format Axis, and set the Minimum to a value slightly above zero (e.g., 0.1). This pushes the baseline off the chart, so zero points are no longer visible. Be careful: this only hides zeros at the bottom, not zeros in the middle of the data range.

Method 2: Use a Formula to Replace Zeros with #N/A

Excel does not plot #N/A errors in line charts, bar charts, and scatter plots. This is the most flexible method. Create a helper column next to your data with a formula like:

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

Replace the original data range in your chart with this helper column. The zero values become gaps in the line or missing bars. This method works for any chart type and keeps your original data intact.

Method 3: Filter the Data Source

For a quick fix, filter the underlying data to exclude rows with 0%. Select your data, go to the Data tab, and apply a filter. Uncheck the 0% values, then create or refresh the chart. The chart will only show non-zero rows. This is simple but changes the data range, which may not be ideal if you need to preserve all rows for other purposes.

How to Choose the Right Method

• Use axis formatting when zeros are only at the bottom and you want a quick visual fix.
• Use the #N/A formula when you need precise control and the chart must update automatically when data changes.
• Use filtering when you are creating a one-off chart and do not mind altering the source data.

Common Mistakes and Misconceptions

A frequent mistake is using a blank cell instead of #N/A. Blank cells are often treated as zero by Excel, so they do not hide anything. Another misconception is that hiding zeros changes the underlying data—it does not; it only changes what the chart displays. Also, remember that the #N/A method does not work for stacked bar charts; those will still show a zero-height segment.

Limitations of Each Approach

Axis formatting cannot hide zeros that appear in the middle of the data. The #N/A formula works for most chart types but not for stacked charts or pie charts. Filtering is not dynamic—if you add new data, you must reapply the filter. For pie charts, the only way to hide zero slices is to filter them out, as Excel does not support #N/A in pie charts.

Frequently Asked Questions

Does hiding zero values affect the chart’s axis scale?

No. Hiding zeros only removes the visual representation of those points. The axis scale is calculated from the remaining visible data, so the chart may appear to have a different range, but the underlying data is unchanged.

Can I hide zero values in a pivot chart?

Yes, but the #N/A formula method does not work directly on pivot charts. Instead, filter the pivot table to exclude zero values, or use a slicer to deselect them. The chart will update accordingly.

Why do my zero values still appear after using the #N/A formula?

Check that the formula returns #N/A and not a text string like “#N/A”. Also, ensure the chart references the helper column, not the original column. If you are using a stacked chart, switch to a clustered chart or filter the data.

Is there a way to hide zeros permanently without a helper column?

You can use Excel’s Select Data dialog to manually hide data points, but this is tedious and not dynamic. The helper column method is the most maintainable because it updates automatically when your data changes.

Will hiding zeros change the average or other calculations?

No. Hiding zeros in a chart only affects the display. Any calculations you perform on the original data remain unchanged. If you want calculations to ignore zeros, you need to use functions like AVERAGEIF.

Related

• Calculating 1 Rep Max: Formula and Examples
• Real-Time Google Translate: How It Works & Limits
• Duolingo After a Year: What Feels Worse Now

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2