Excel Line Chart: Ignore Null Values or Show Gaps

Yes. In Excel for Microsoft 365 on Windows, you can control how a line chart handles empty cells: show a gap, plot a zero, or connect the points across the empty cell. Select the chart, choose Chart Design > Select Data > Hidden and Empty Cells, then choose the behavior you want. The right setting depends on what “null” means in your worksheet.

What “null” means in an Excel chart

Excel does not have one universal worksheet value called null. People often use “null” to mean several different things: a genuinely empty cell, a formula that displays an empty string, a zero, or an error such as #N/A. These can produce different results in a chart, so it helps to identify which kind of missing value you have before changing chart settings.

Blank cell: The cell contains no value or formula. Excel’s empty-cell setting controls whether the chart leaves a gap, plots zero, or connects across it.
Formula returning an empty string: A formula such as one that returns “” can look blank on the sheet, but it is not the same as an actually empty cell. Chart behavior may differ, so test it or use an explicit #N/A result when you need to suppress the point.
Zero: Zero is a real numeric value. Excel normally plots it at the zero level; it does not treat it as missing data.
#N/A: This error is commonly used to indicate that a value is unavailable. Excel charts generally do not plot the error as a data point, making it useful when a formula should deliberately omit a point.

This distinction matters because a chart can only follow the instruction implied by the source data. If “no measurement” is stored as 0, Excel has no way to know that you intended a missing value rather than a real zero.

Choose gaps, zero, or connected points

To set how a chart treats genuinely empty cells in Excel for Microsoft 365 on Windows:

Click the line chart. If needed, click the specific chart area so the chart tabs appear.
On the ribbon, select Chart Design > Select Data.
In the Select Data Source dialog, select Hidden and Empty Cells.
Under the empty-cell options, choose Gaps, Zero, or Connect data points with line, then confirm the dialogs.

Gaps leaves a visible break in the line at missing data. Zero draws the series down to the horizontal axis at that category. Connect data points with line draws a line from the preceding value to the following value, skipping over the empty position. It does not create a new measurement; it only changes the way the chart displays the two known points.

The option is about empty cells in the chart’s source range. It does not mean “delete the category from the chart.” For example, a date with no reading can remain on the horizontal axis even when the line connects across it. If you want to remove categories as well as missing points, you may need to filter the source data or build a separate chart-ready range.

When to leave a gap and when to connect

Choose a gap when the absence of a reading is important information. A break makes it clear that the line does not represent a continuous set of observations. This is often appropriate for interrupted sensor records, survey responses that were not collected, or sales data for a location that was closed. Connecting the points could imply a trend through a period when you have no evidence about what happened.

Connecting across missing values can be useful when the missing cell is simply an occasional omission and the reader should focus on the surrounding observations. For instance, if a weekly report has a missing entry between 18 and 22, a connected line can make the overall sequence easier to read. But it does not tell the reader whether the value rose steadily, fell and recovered, or changed in some other way between those points.

Plotting zero is appropriate only when zero is the true value. If a temperature sensor failed, a zero reading would be misleading. If a product genuinely had no sales that day, however, zero may be the accurate value. Use a legend, note, or clear data labels where the difference between “none” and “not recorded” could affect interpretation.

Handle formulas that display a blank

A formula that displays nothing can be mistaken for an empty cell. For example, a report formula might return an empty string when the source measurement is missing. The worksheet looks tidy, but the chart may not handle that result like a truly blank cell. If the point is still appearing, inspect the formula bar and source range rather than relying on what the cell looks like.

One reliable approach is to return #N/A for values that should not be plotted. With dates in A2:A5 and measurements in B2:B5, put this formula in C2 and fill it down: =IF(B2="",NA(),B2). If B2 is empty or returns an empty string, C2 returns #N/A; if B2 contains 12, C2 returns 12. Use the dates in column A and the results in column C as the chart’s category labels and series values. The chart will omit the unavailable point rather than treating the displayed blank as an ordinary numeric value.

Use this method only when #N/A correctly represents unavailable data. If the source cell contains a legitimate zero, the formula keeps and plots that zero. If a cell contains some other error, such as #VALUE!, the example does not hide that error; it passes the source value through only when the blank test is false. That can be useful because it keeps unexpected data problems visible instead of silently concealing them.

What #N/A does—and does not—mean

Using #N/A is a common way to omit a point, but it is not the same as deleting a row or removing a date from the axis. The category may still appear, depending on the chart’s source and axis settings. The key effect is that #N/A is not a plotted numeric value. If you need the chart to show a continuous line across that category, check the result in the chart rather than assuming the empty-cell connection option will necessarily bridge every type of error in every chart setup.

Some versions of Excel include a Show #N/A as an empty cell option in the Hidden and Empty Cells dialog. If it appears in your Excel installation, it can make the chart treat #N/A values like empty cells for display purposes, after which the selected gap, zero, or connection behavior can apply. If that checkbox is absent, the formula-based #N/A approach still commonly suppresses the point, but do not assume it will behave identically to a genuinely blank cell in every setting.

Do not replace all errors with #N/A just to make a chart look clean. Errors can reveal broken references or invalid calculations. A separate chart-ready column lets you control what the chart sees while leaving the original calculation available for checking.

Check the source range and chart type

If the chart does not respond as expected, verify that the series actually includes the cells you changed. Select the chart and open Chart Design > Select Data. Check both the chart data range and the values listed for the relevant series. A chart may be based on a fixed range that excludes new rows, or a series may point to a different column than the one you edited.

Also check whether the row or column is hidden or filtered. In the Hidden and Empty Cells dialog, Excel has a separate setting for whether data in hidden rows and columns is included in the chart. That setting is distinct from the empty-cell choices. If a value disappears after filtering, review this option; if a blank is being plotted unexpectedly, inspect the actual cell contents and the series references.

Line charts are designed to show values across categories or a sequence. If the horizontal values are actual numeric measurements with meaningful spacing, an XY (Scatter) chart may be a better fit. In a line chart, categories are generally spaced evenly; in a scatter chart, the horizontal position reflects the numeric X value. Changing chart type does not turn a missing value into a true observation, but it can affect how the remaining points are positioned and connected.

Windows, Mac, and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. In Excel for Mac, select the chart and use the chart design controls to open the data-source and hidden-and-empty-cells options; the dialog layout and wording can differ slightly by release. If you cannot find the setting, right-clicking the chart and choosing Select Data is another route to the source-data dialog in desktop Excel.

Excel for the web may not expose every chart configuration found in the desktop application, and available controls can change as the web app is updated. If the empty-cell dialog or a needed #N/A option is missing, open the workbook in the desktop version of Excel to set the chart behavior. The workbook can then be saved and viewed online, although it is sensible to check the chart after editing because web and desktop features are not always identical.

Common mistakes to avoid

Using zero as a placeholder: Zero is plotted as data, so use a blank or an intentional #N/A value when the measurement is missing.
Assuming a visually blank cell is truly empty: A formula or a space character can make a cell look blank while leaving content in it.
Choosing “connect” without considering the story: A continuous line can suggest information between observations that was never collected.
Changing the chart setting but not the source: Confirm the series range includes the cells you intend to chart.
Removing errors indiscriminately: Hide only values that are genuinely unavailable; investigate calculation errors that indicate a problem.

Frequently Asked Questions

How do I make an Excel line chart skip blank cells?
Select the chart and choose Chart Design > Select Data > Hidden and Empty Cells. Choose Gaps to leave breaks, or Connect data points with line to draw across genuinely empty cells.

Why does a blank-looking cell show as zero in my chart?
The cell may contain a formula returning an empty string, or it may contain an actual zero. Check the formula bar and the underlying value. To omit an unavailable point more reliably, use a chart helper formula that returns #N/A for missing data.

Does Excel treat zero as a null value?
No. Zero is a valid number and is normally plotted at zero. Store missing data separately as a blank or an intentional #N/A result rather than using zero as a placeholder.

Can I connect a line across a missing value without changing the source data?
Yes, for genuinely empty cells, choose Connect data points with line in the Hidden and Empty Cells settings. If the cells contain formulas or errors, their behavior may differ; use a helper range and inspect the finished chart.

Will filtering or hiding a row remove it from the chart?
Not necessarily. The Hidden and Empty Cells dialog has a separate option controlling whether hidden row and column data appears. Review that setting as well as the empty-cell behavior.

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.