Chart & Visualization Issues

How to Hide Zero-Value Category Names in Excel Charts Dynamically

Khadija Khan Sep 27, 2026 869 views

Question details

The user needs to automatically hide category names and labels for zero values in a dynamically updating Excel chart.

How to Hide Zero-Value Category Names in Excel Charts

Product: Spreadsheet
Device & OS: not provided
Scenario: Visualizing changing data over time where some categories occasionally drop to zero, causing chart axes to become cluttered with zero-value category labels.
Observed behavior: Simply having zero values in a data range does not automatically remove the corresponding category names from the chart axis. A manual filter is required, which doesn't update when the data changes.

Before you start

Ensure your spreadsheet software supports dynamic array functions, as using the FILTER function is the most efficient way to keep your chart source updated automatically without macros.

Solution 1Recommended

Use the FILTER Function for a Dynamic Chart Data Source

Create a secondary data table that automatically extracts only non-zero values and their corresponding categories using the FILTER function, and set this as your chart source.

Instead of connecting your chart directly to the raw data, you can use a formula to generate a clean, dynamically updating dataset. The FILTER function pulls only the rows that meet your criteria (e.g., values greater or less than zero), dropping the zero-value categories entirely.

Identify your source data range

Locate your original dataset. For example, assume your category names are in A2:A15 and your values are in B2:B15.

Enter the FILTER formula

Click on an empty cell where you want your new dynamic chart data to live. Type the formula =FILTER(A2:B15, B2:B15<>0) and press Enter. This will spill the non-zero categories and their values into the adjacent cells.

Create a new chart from the filtered data

Highlight the new dynamically generated array. Go to the 'Insert' tab on the top ribbon and select the chart type you want to create (e.g., Column, Bar, or Pie chart).

Format and test the chart

Format your chart as desired. To test it, change a non-zero value in your original data (B2:B15) to 0. The dynamic array will update, and the category name will instantly disappear from your chart.

Use the FILTER Function for a Dynamic Chart Data Source

Automatic Synchronization: Because the chart is linked to the spilled array generated by the FILTER function, you will never need to manually check or uncheck categories as your monthly or weekly data changes.

Solution 2

Use a Pivot Chart to Filter Out Zeros

If you are using an older version of Excel that does not support the FILTER function, a Pivot Chart combined with a Value Filter is the best automated alternative.

Manage Charts Efficiently with WPS Office

Create Dynamic Charts Without Zero Values in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER, allowing you to easily build dynamic charts that automatically hide zero-value categories while keeping your workspace organized.

1. Open your data file: Launch WPS Spreadsheet and open the document containing the chart data.
2. Use the FILTER function: In an empty cell range, type =FILTER(DataRange, CriteriaRange<>0) to extract all categories with non-zero values.
3. Insert the dynamic chart: Select the newly generated data array, navigate to the 'Insert' tab, and click 'Chart' to automatically create a clean, zero-free visualization.

Highly compatible with Microsoft Excel formulas, charting tools, and file formats (.xlsx).Fully supports the FILTER function to automate your data visualization and reporting.Lightweight architecture ensures smooth performance even with large datasets.Free to use with a familiar, tabbed user interface that makes finding chart tools easy.

microsoft office alternative - wps office

Frequently Asked Questions

Can I hide zero labels using custom number formatting?

Custom number formatting (such as 0;-0;;@) can hide the '0' data label on the chart bars or lines, but it will not remove the category name from the axis or the legend. To completely remove the category, the source data must be filtered.

Does the FILTER function work in all spreadsheet versions?

No. The FILTER function is a dynamic array function available in newer spreadsheet software, including Microsoft 365, Office 2021, and modern versions of WPS Office. If you use an older version, you must rely on Pivot Charts or complex INDEX/MATCH formulas.

Why didn't my chart update when I hid the rows manually?

By default, charts will hide data in hidden rows, but manual hiding does not update dynamically when cell values change. If the cell value goes back above zero, you would have to manually unhide the row. The FILTER formula automates this process.

Excel & Data Problems