Excel Line Chart: Hide Zero Values Without Losing Data

To hide zero values in an Excel line chart without changing your original data, create a helper column that replaces zeros with NA(), then use that column as the chart’s data series. Excel does not plot the resulting #N/A values, so the chart omits those zero points while your source values remain intact.

Why zero values appear in a line chart

A line chart treats a numeric zero as a real measurement, not as a blank or a display instruction. If a series goes from 12 to 0 to 15, Excel plots all three values and draws the line down to zero and back up. That is the correct chart behavior when zero means an actual result, such as no sales or no activity during a period.

The difficulty is that zero can also mean “there is no value to show yet.” For example, a monthly report may contain zeros for future months, or a tracking sheet may use zero as a placeholder for missing readings. In those situations, plotting the zero makes the chart look as if the measure genuinely fell to nothing.

Formatting a cell to hide its zero does not solve the chart problem. Number formats affect how a worksheet cell displays; they do not change the numeric value Excel sends to the chart. The chart can therefore still plot a zero that appears blank in the grid. To change the chart, you need to change what the chart references, filter the relevant point, or use a chart setting that applies to empty cells.

Hide zeros with a helper column

A helper column is usually the clearest option because it separates presentation from the original data. Suppose dates are in A2:A13 and the values to chart are in B2:B13. In C2, enter the formula below, then fill it down through C13:

With source values in B2:B13 and the first helper result in C2, enter =IF(OR(B2="",B2=0),NA(),B2) in C2 and copy it down.

The formula returns the original value when it is nonzero. When the source cell is blank or contains zero, it returns #N/A. Excel charts do not plot that error value, so the zero point is omitted. Including the blank test also prevents empty source cells from being treated like zero by the formula. If your data uses text or other special values, check how those values should appear before adopting the formula unchanged.

Add a heading to column C, such as “Chart values,” and enter the formula in C2.
Use the fill handle to copy the formula down alongside the full source range.
Select the chart, open Chart Design > Select Data, and edit the series so its values refer to C2:C13 instead of B2:B13. You can also create a new line chart using the date column and helper column.
Check several points against the source data, especially a genuine zero, a blank, and a nonzero value.

In the worksheet, the helper cells for omitted points show #N/A. That is expected; it is the signal that keeps those points out of the chart. Keep the source column unchanged so calculations, tables, and later analysis can still use the actual zeros.

Choose what happens between the remaining points

Omitting a point and deciding how the line looks around it are related but separate choices. Depending on the chart and the surrounding values, the line may show a break where the omitted value sits or may connect the valid points on either side. A gap emphasizes that there is no displayed measurement for that interval; a continuous line suggests a trend between the available measurements.

For genuinely empty cells, Excel provides a chart setting. Select the chart, choose Chart Design > Select Data, then select Hidden and Empty Cells. In the dialog, choose how empty cells should appear: as gaps, as zero, or with data points connected by a line. The available choices can depend on the chart type.

That setting is not a general “hide zero values” switch. A cell containing the number 0 is still a numeric value, so choosing gaps for empty cells does not make numeric zeros disappear. The helper-column method works because it changes the chart input for those points to #N/A, rather than relying on the empty-cell setting.

Pick the method that fits your chart

Use a helper column when you want to hide zeros consistently and keep the raw data available. It is also the easiest method to audit: anyone looking at the sheet can compare the original values with the chart-ready values and see why a point is missing.

Use a helper column when zeros should be omitted according to a repeatable rule, or when the source data must remain unchanged.
Use the chart filter for a quick, manual presentation change when hiding a category is acceptable. Select the chart, use the chart filter button if it is available, and deselect the category you do not want displayed.
Use empty-cell settings when the unwanted points are genuinely blank cells and you want to control whether the line breaks or connects across them.
Change the source data itself only when zero is not a valid value and should be corrected throughout the workbook, not merely hidden in one chart.

A chart filter generally hides a category or data series, not just a zero in one series. If a row represents January and one of several series is zero in January, hiding that category can also remove the other series’ January values. For that reason, filtering is less suitable for a multi-series chart when you want to suppress only individual zero points.

What to do when a zero is only displayed as zero

Sometimes the underlying number is not exactly zero. A value such as 0.004 may be displayed as 0.00 because the cell is formatted to show two decimal places. The comparison in the helper formula checks the stored value, so it will retain 0.004 even though the chart label or worksheet may look like zero.

Decide whether you want to hide only exact zeros or all values that round to zero at the precision shown. The latter is a reporting rule, not merely a chart-formatting change: a small nonzero measurement may be meaningful. If you choose that rule, adjust the formula deliberately to use the appropriate rounding precision and test it against representative values before applying it to the whole series.

Also check whether the source formula returns a true numeric zero, an empty string, or a text value. These can look similar on the sheet but behave differently in formulas and charts. The helper formula above handles numeric zero and a blank or empty-string result; if a source formula returns text such as a dash, decide whether that text should remain, be converted, or be omitted.

Common problems and how to fix them

The chart still drops to zero. Check the series range in Chart Design > Select Data. It may still point to the original data column rather than the helper column.
The worksheet shows #N/A. This is normal for the helper cells that represent omitted points. Do not replace the error with a formula result that the chart might interpret as zero. If you need a cleaner layout, place the helper data on a separate sheet, or hide its column and ensure the chart is set to include data from hidden rows and columns where that option is available.
A blank cell appears as a zero in the chart. Confirm whether the source is truly blank or contains a formula returning an empty string. Use the helper formula to handle both cases, and inspect the chart’s empty-cell settings if the chart uses actual blank cells.
The line has an unexpected break. Decide whether a break is the right message for missing data. For actual blank cells, review the options in Hidden and Empty Cells. Test the chart after changing the setting, since a gap and a connected line communicate different things.
Some values that look like zero remain visible. They may be small nonzero numbers hidden by number formatting. Review the underlying values and choose whether the rule should apply to exact zeros or to values below a stated threshold.

When you revise a helper formula, verify the results for at least one ordinary value, one zero, and one blank. This simple check catches common mistakes such as filling the formula down only part of the series or referencing the wrong source column.

Excel for Mac and Excel for the web

The helper-formula approach uses standard worksheet functions and works in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The ribbon arrangement and chart dialogs may look different across platforms, but the key task is the same: make the chart series refer to the helper values rather than the original zero-containing range.

In desktop Excel, start with Chart Design > Select Data to inspect or change the series range. On Mac, the chart tools and dialog layout can differ slightly by release, so select the chart and look for the chart design or data-selection controls. Excel for the web supports ordinary formulas and chart editing, but some chart-formatting controls may be more limited than in the desktop app. If you cannot find the hidden-and-empty-cells options there, use the helper column; it does not depend on that setting.

Frequently Asked Questions

Can I hide zero values without changing the source data?
Yes. Use a helper column that returns NA() for values you want omitted, and point the chart series to that column. The original data remains available for formulas and other reports.

Does “Show empty cells as Gaps” hide zeros?
No. It controls cells that Excel considers empty. A numeric zero is a plotted value, so use a helper column or a chart filter if you need to omit it.

Can I hide zeros in only one line on a multi-series chart?
Yes. Create a separate helper column for that series and use it as that series’ chart range. Leave the other series pointed at their original data unless they need the same treatment.

Why does my chart show a gap after I hide a zero?
The chart is omitting the point represented by #N/A. A gap indicates that the chart is not displaying a value for that interval. If the missing interval should instead be visually bridged, review the chart’s handling of empty cells and decide whether a connected line would accurately represent the data.

Can I hide zeros with a custom number format instead?
A custom number format can hide zeros in worksheet cells, but it does not reliably remove their numeric values from a chart. Use a chart-ready helper range when the chart itself must omit the points.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.