How to Hide Zero Values in Excel Chart? (9 Easy Methods)

Sharing is caring!

Microsoft Excel is one of the most powerful spreadsheet tools for data visualization. But when you are working with charts, zero values can sometimes make your chart look misleading or cluttered. For example, a line chart might drop to the baseline for zero values, or a column chart may show unnecessary empty bars. This can distract your audience from the actual data trends.

Fortunately, Excel provides multiple ways to hide zero values in charts without losing your underlying data. In this guide, we’ll explore different methods step by step.

Why You Might Want to Hide Zero Values

Before moving to the techniques, let’s quickly understand why hiding zero values can improve chart readability:

• Cleaner visualizations – Zero values may distort the chart by pulling lines down to the baseline or adding extra bars.
• Focus on real data – Hiding zeroes ensures that trends and patterns are clearer.
• Improved reporting – When sharing reports, charts without zeros look more professional and easier to interpret.
• Accurate storytelling – Eliminating irrelevant data points helps the audience focus on key insights.

Method 1: Use Filter to Exclude Zero Values

The easiest way to prevent zeros from appearing in your chart is by filtering them out from the dataset.

Steps:

1. Select your data range.
2. Go to the Data tab and click Filter.
3. Use the dropdown filter on the column that contains values.
4. Uncheck 0 from the list.
5. Insert your chart again.

Key Points:

• This method removes zeros from the dataset entirely.
• It is useful when zero values are not relevant for your analysis.

Method 2: Use IF Formula to Replace Zeros with Blank

Another effective approach is using an IF function to replace zero values with blank cells.

Example Formula:

=IF(A2=0,"",A2)

This formula checks if a cell contains a zero. If yes, it replaces it with a blank cell; otherwise, it keeps the original value.

Why It Works:

• Excel charts usually ignore blank cells but plot zeros as actual values.
• By turning zeros into blanks, your chart automatically skips them.

Example:

This makes the chart look cleaner and prevents lines or columns from falling to zero.

Method 3: Change Zero Display Settings

Excel also allows you to hide zeros directly in the worksheet without altering the formulas.

Steps:

1. Go to the File menu and click Options.
2. Select Advanced.
3. Scroll to the section Display options for this worksheet.
4. Uncheck the option Show a zero in cells that have zero value.

Notes:

• This hides zeros in the sheet and in the chart.
• It does not delete or replace the actual value, only hides the display.

Method 4: Format Data Labels to Hide Zeros

If you only want to remove zero values from data labels (but keep the chart structure intact), formatting is the best choice.

Steps:

1. Click your chart.
2. Select the Data Labels you want to edit.
3. Right-click → Format Data Labels.
4. Choose Number → Custom.
5. Use a custom format like: 0;-0;;@ This format hides zero values from appearing in labels.

Benefit:

• Keeps the chart points intact but prevents unnecessary zero labels.

Method 5: Use NA() Function to Skip Zeros

If you’re creating a line chart or scatter chart, replacing zeros with #N/A values ensures Excel completely skips plotting them.

Formula Example:

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

Why It Works:

• Excel ignores #N/A values when plotting charts.
• Unlike blanks, #N/A creates a gap instead of connecting the line to zero.

Example:

In the chart, the line will not dip to zero; instead, it will show a break.

Method 6: Adjust Chart Options for Empty or Zero Cells

Excel gives you more control through hidden and empty cell settings.

Steps:

1. Right-click the chart and choose Select Data.
2. Click Hidden and Empty Cells.
3. Choose one of these options:
   • Gaps – Leaves gaps where zero or empty values exist.
   • Zero – Plots them as zero.
   • Connect data points with line – Joins the line between values ignoring gaps.

Best Use:

• Works best for time-series charts like line graphs.
• Provides flexibility in how your chart should handle empty values.

Method 7: Use Conditional Formatting in the Data Table

If your chart pulls from a table, you can use conditional formatting to hide zeros before they reach the chart.

Steps:

1. Select the dataset.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Format only cells that contain.
4. Set the condition: Cell Value = 0.
5. Change font color to white (or background color).

Limitation:

• This only hides zeros visually in the table, not in the chart itself.
• Combine with other methods for best results.

Method 8: Use Dynamic Named Ranges

For advanced users, dynamic named ranges help exclude zeros automatically.

Example:

1. Define a named range using: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>0"),1)
2. Use this range as the source for your chart.

Benefit:

• The chart updates automatically when new data is added, skipping zero values.
• Ideal for large datasets where zeros may frequently appear.

Method 9: VBA Macro to Hide Zero Values

If you’re comfortable with VBA, you can write a macro that hides zero values programmatically.

Example Code:

Sub HideZerosInChart()
    Dim s As Series
    For Each s In ActiveChart.SeriesCollection
        s.Values = Evaluate("IF(" & s.Values.Address & "=0,NA()," & s.Values.Address & ")")
    Next s
End Sub

Advantage:

• Automates the process across multiple charts.
• Useful for dashboards and recurring reports.

Comparing the Methods to Hide Zero Values in Excel Chart

Here’s a comparison of methods you can use depending on your needs:

Tips for Cleaner Excel Charts

• Use consistent formatting – Avoid mixing methods within the same chart.
• Test on different chart types – Some methods work better on line charts, while others suit column charts.
• Consider audience needs – Sometimes showing zeros is necessary for accuracy, especially in financial reporting.
• Keep backup data – If using formulas to replace zeros, store the original dataset separately.

Final Thoughts

Knowing how to hide zero values in Excel charts can make your reports cleaner, more professional, and easier to interpret. Depending on your scenario, you can use quick fixes like filters and display settings, or advanced approaches like NA() functions, dynamic ranges, or VBA macros.

The key is choosing the right method for your chart type and reporting needs. Once you apply these techniques, your charts will highlight the data that truly matters and present a clearer story to your audience.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.