Excel Line Charts: Ignore Blank Cells or Show Gaps?

Yes. In Excel for Microsoft 365 on Windows, select the line chart, choose Chart Design > Select Data > Hidden and Empty Cells, then choose whether empty cells appear as gaps, zeros, or connected points. Choose Connect data points with line to draw across blank cells; choose Gaps to leave breaks in the line.

What Excel Does With Blank Cells in a Line Chart

A line chart needs a value for each category on its horizontal axis. When a source cell has no value, Excel has to decide how to represent that missing observation. The chart setting controls this display; it does not fill in the worksheet cell or change the underlying data.

Excel offers three main treatments for empty cells:

Gaps: The line stops at the last available value and resumes at the next one. No line is drawn through the missing category.
Zero: Excel plots the missing value at zero. This can make the line fall sharply to the bottom of the chart and rise again.
Connect data points with line: Excel draws a line from the previous available value to the next one, spanning the empty category or categories.

For example, suppose monthly revenue is 100 in January, blank in February, and 140 in March. Gaps leaves the line broken between January and March. Zero adds a February point at 0. Connecting points draws a segment from 100 to 140 across February, but it does not calculate or display a February revenue value. That distinction matters: a continuous-looking line is not evidence that the missing data was measured or estimated.

How to Change the Empty-Cell Setting

In Excel for Microsoft 365 on Windows, change the setting for the selected chart as follows:

Click the line chart to show the chart tabs on the ribbon.
Choose Chart Design > Select Data.
In the Select Data Source dialog, choose Hidden and Empty Cells.
Under Show empty cells as, select Gaps, Zero, or Connect data points with line.
Choose OK, then check the chart against the source values.

This setting belongs to the chart. If the workbook has multiple charts, inspect each one rather than assuming a change to one chart updates the others. If you cannot find the option, first click the chart and confirm that the Chart Design tab is visible. In Excel for Mac, select the chart and use the chart design and data-selection controls; wording and dialog placement can vary by release. Excel for the web may not expose every desktop chart setting, so open the workbook in the desktop app if the empty-cell control is unavailable.

Which Option Should You Choose?

Choose the treatment that reflects what the missing observation means, not simply the one that makes the chart look smooth. A chart is a visual summary, and the choice can change the story readers take from it.

Use Gaps when the value is unknown, not reported, or not applicable, and you want the chart to make that absence visible. A break is often the clearest and least misleading option.
Use Zero only when the missing entry genuinely means a measured or confirmed value of zero. For example, if a daily count is confirmed to be zero, enter 0 in the worksheet. Do not rely on a blank being interpreted as zero unless that is truly what the data means.
Connect the points when you want to show a trend before and after a temporary missing observation, and a continuous line is appropriate for the audience. Make clear that the missing value itself is still unknown.

Consider a temperature series with readings of 18, blank, and 22 degrees on three consecutive days. Plotting zero would imply a dramatic plunge to zero degrees, which is probably false. A gap accurately signals the missing reading. Connecting the points can help show the broader movement, but it visually bridges a day with no measurement. For a sparse time series, gaps may be the more honest choice; for a presentation where continuity is useful, a connected line may be acceptable if the missing data is clearly identified.

Truly Empty Cells and Formula-Generated Blanks

One common source of confusion is that a cell that looks blank may not actually be empty. A cell can contain a formula that returns an empty string, a space character, or other text. These are different from a cell that has been cleared. Excel’s empty-cell chart setting is intended for empty cells, and formula results that look blank may not behave the same way in every chart setup.

If a point appears at zero even though the worksheet cell looks empty, inspect the formula bar and the source range. A formula returning an empty string can be treated differently from a genuinely empty cell, and chart behavior can depend on how the data is supplied. Test the relevant setting on a small copy of the chart before relying on the visual result. A space typed into a cell is also not a true blank; clear the cell’s contents if the point should be handled as empty.

Another option is to return the error value #N/A for values that should not be plotted. Excel charts generally omit #N/A points rather than plotting them as ordinary numeric values. However, that is not the same as telling the chart to connect across a blank: it may leave a break, and the empty-cell setting may not control an error value. Use this approach only if omitted points and any visible error values in the worksheet are acceptable. For a clean chart, a separate chart-source range can be useful, but verify its behavior with the actual workbook.

Why a Line May Still Look Wrong

If changing the setting does not produce the expected result, check the source data and chart structure. A missing line segment is not always caused by the empty-cell option. Common causes include:

The source cell is not empty. It may contain a formula, a space, text, or an error. Check the cell itself, not just what appears on the chart.
The selected data range is incomplete. Right-click the chart and choose Select Data to review the chart’s data range and individual series ranges.
The chart has multiple series. The gap may belong to one series while another series continues normally. Review each series and its source cells.
The value is zero, not blank. A zero is a real numeric value, so the empty-cell option does not turn it into a gap. Remove or correct the zero only if the source data supports that change.
The axis or chart type affects the appearance. A line chart connects points in category order. If dates or categories are missing, duplicated, or out of order in the source, the line may not represent the sequence you intended.

It can help to temporarily select the worksheet range and look for formulas, zeros, or entries that are formatted to appear blank. Avoid deleting source values just to make a chart look right: correct the chart’s source or use a clearly documented helper range instead.

Hidden Rows, Filtered Data, and Empty Cells

Hidden data is related to, but separate from, blank-cell handling. In the Hidden and Empty Cells dialog, Excel also provides an option to show data in hidden rows and columns. If that option is enabled, charted data in manually hidden rows or columns can remain visible in the chart. If it is disabled, hiding those rows or columns can remove their data from the chart.

This is useful when a chart should respond to manually hidden worksheet rows, but it is not a way to decide whether an empty observation becomes a gap or a zero. Those are separate controls. Filtering rows can also affect what appears in a chart, and behavior can depend on the chart and filtering setup. If the chart seems to include excluded records, inspect the hidden-data option and test the filter with a small, obvious example before sharing the workbook.

Blank Cells Versus Zero Values

A blank and a zero communicate different things. A blank usually means there is no value in the cell; the reason could be unknown, not yet collected, or not applicable. Zero means a value was recorded and its amount is zero. Treating those as interchangeable can distort totals, trends, and decisions made from a chart.

For example, if a store was open and made no sales on a particular day, zero may be correct. If the sales report for that day has not arrived, a blank is more accurate. With the Zero chart option, both a truly empty cell and a confirmed zero can appear at the baseline, making it difficult to tell them apart. If the distinction matters, use Gaps for missing values and enter confirmed zeros explicitly. You can also label the chart or add a note explaining how missing periods are handled.

Frequently Asked Questions

Can Excel connect a line across blank cells?
Yes. Select the chart and choose Chart Design > Select Data > Hidden and Empty Cells, then select Connect data points with line. Excel draws a segment between the surrounding plotted values; it does not create a value for the missing cell.

How do I make blank cells show as gaps instead of zero?
Open the chart’s Hidden and Empty Cells settings and select Gaps. If the point still appears at zero, check whether the source cell contains a formula returning an empty string, a zero, or another value rather than being truly empty.

Does Excel treat a blank cell and zero the same in a line chart?
No. Zero is a numeric value plotted at zero. A blank is handled according to the chart’s empty-cell setting, so it can appear as a gap, as zero, or as a connection between neighboring points.

Why does a formula that returns an empty string still affect my chart?
A formula result that looks blank is not necessarily the same as an empty cell. Chart behavior can differ from the setting for genuinely empty cells. Check the formula and test a chart-source range, or use #N/A when the point should be omitted and a gap is acceptable.

Can I change this setting in Excel for the web?
Some chart editing controls vary between Excel for the web and the desktop app. If Hidden and Empty Cells is not available in the web interface, open the workbook in Excel for Microsoft 365 on Windows or Mac and change the chart setting there.

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.