How to Create an Excel Chart That Ignores Zero Values Without Hiding Rows
Question details
The user wants to create a chart that only plots non-zero data points without filtering or hiding the source rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing a 52-week dataset where some weeks contain zero mileage. The user cannot filter or hide the rows because doing so negatively affects other visual elements on the worksheet.
- Observed behavior
- By default, Excel plots zero values, dropping the chart line to the axis. When the user filters out the zeros, entire rows are hidden, which distorts or hides other dashboard elements located in those same rows.
Determine whether your version of Excel supports dynamic array formulas like FILTER (available in Microsoft 365 and Excel 2021), or if you need to use the universal NA() function workaround.
Use the FILTER Function to Create a Dynamic Helper Range
If you use Microsoft 365 or Excel 2021, the dynamic FILTER function can extract non-zero data to a new range automatically without affecting your original table.
This method creates a separate, dynamic copy of your data that only contains rows with non-zero values. You can then safely use this new range as the source for your chart.
Find a blank section in your current worksheet or open a new worksheet to house the chart-specific data.
Type the formula =FILTER(A2:B53, B2:B53<>0) into the first cell of the empty area. Adjust 'A2:B53' to match your entire data range and 'B2:B53' to the column containing the zero values you want to exclude.
Press the Enter key. Excel will automatically spill the filtered results, displaying only the rows that have non-zero mileage.
Select this newly generated spilled range. Go to the 'Insert' tab on the ribbon, navigate to the Charts group, and select your desired chart type.
Replace Zero Values with #N/A Using a Helper Column
Excel line and scatter charts do not plot #N/A error values. You can create a helper column using the IF and NA() functions to ignore zero values in older versions of Excel.
Create Dynamic Charts Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful functions and advanced charting capabilities that seamlessly handle missing or zero values. You can easily use helper columns with the NA() function to customize your data visualizations without altering original layouts.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet file in the WPS Spreadsheet application.
- 2. Create a helper column: Next to your main data, use the formula =IF(B2=0, NA(), B2) to convert unwanted zero values into #N/A errors.
- 3. Select data and insert chart: Highlight your category labels and the newly created helper column. Navigate to the 'Insert' tab and choose your preferred chart type.
- 4. Customize chart settings: Use the Chart Tools menu on the right panel to format your chart, ensuring that hidden or empty cells are displayed exactly how you want.
Frequently Asked Questions
Why does hiding rows affect other visuals on my dashboard?
Hiding rows physically collapses the row height for the entire worksheet. If you have other charts, tables, or shapes placed across those same rows, they will also be collapsed, hidden, or distorted.
Can I just uncheck the 'Show zero values' option in Excel?
Unchecking 'Show a zero in cells that have zero value' in Excel Options only hides the visual text display of zeros in the grid. The chart will still read the underlying 0 value from the cell and plot it on the graph.
How do I make my chart ignore empty cells instead of treating them as zero?
Right-click your chart and select 'Select Data'. Click on the 'Hidden and Empty Cells' button at the bottom left of the dialog box, and choose 'Gaps' or 'Connect data points with line' instead of the 'Zero' option.
