Excel Line Chart Blanks: Gaps, Zeros, or Connected Lines

Yes. In Excel for Microsoft 365 on Windows, you can choose whether blank cells in a line chart appear as gaps, are plotted as zero, or are skipped while the line connects the surrounding points. Select the chart, open Chart Design > Select Data > Hidden and Empty Cells, then choose the behavior you want under Show empty cells as.

What Excel means by ignoring blank cells

“Ignore blanks” can mean two different things on a chart. Excel can leave a visible break where a value is missing, or it can draw a continuous line from the point before the blank to the point after it. Both avoid treating the blank as a measured value of zero, but only the second option visually connects across the missing observation.

For example, suppose a monthly series has values for January and March but no February value. With Gaps selected, the line ends at January and resumes at March, with no segment across February. With Connect data points with line selected, Excel draws a segment between January and March. With Zero selected, the chart treats February as zero and draws down to the horizontal axis before rising again.

That distinction matters: a gap communicates that a measurement is unavailable, while a connecting line can imply a continuous trend through the missing period. Neither setting fills in the underlying worksheet cell; it changes only how the chart displays it.

Change how a line chart displays blanks

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

These choices affect the selected chart, not every chart in the workbook. If you have several charts, review each one separately. The chart updates when its source data changes, but a blank-cell display setting remains a chart-level choice.

On Excel for Mac, select the chart and use the Chart Design tab to open Select Data; the dialog includes the empty-cell options. Ribbon labels or their placement can vary slightly between Mac releases. Excel for the web has a more limited chart-editing interface, and the full Hidden and Empty Cells dialog may not be available there. If you cannot find the setting in the web app, open the workbook in the desktop version of Excel.

Choose gaps, zeros, or a connected line

Choose Gaps when a value is genuinely missing, not observed, or not applicable. This is often the clearest choice for measurements such as daily temperatures when a sensor stopped recording.
Choose Zero only when the missing entry should represent an actual value of zero. For example, zero sales may be meaningful if a store was open and recorded no sales; a blank caused by a reporting delay is not the same thing.
Choose Connect data points with line when you want to show the overall trend across a missing entry and the reader can understand that the intermediate value is unknown. Do not use the connecting segment to suggest that Excel has calculated the missing value.

For a report, consider explaining the gap in a note or legend when its cause affects interpretation. A broken line can be easy to miss in a dense chart, and a connected line can look like a complete series. If the missing interval is long, showing a connection may imply more continuity than the data supports.

Blank cells, empty text, and error values are different

A genuinely empty cell has no value or formula in it. A cell containing a formula that returns an empty string, such as a formula whose result is an empty string, is not truly empty: it contains a formula. Excel can treat that result differently from a physically blank cell when charting, and the behavior can depend on the chart and workbook. If a supposedly blank point appears at zero, inspect the source cell rather than assuming the chart setting is wrong.

One useful way to check is to click the source cell and look at the formula bar. If the cell contains a formula, it is not an empty worksheet cell, even if nothing visible appears in the grid. You can also temporarily replace the formula result with a genuinely empty cell in a copy of the data and see whether the chart changes.

Error values are another case. A formula returning #N/A is not a blank, and the error may be visible in the worksheet. Standard Excel charts generally do not plot #N/A as an ordinary numeric point, which makes it useful for intentionally omitting a point. In versions that show the option, Select Data > Hidden and Empty Cells includes Show #N/A as an empty cell. If you use error values this way, verify the result in the specific workbook and chart, and make clear to anyone maintaining the sheet why the errors are intentional.

For example, with source values in B2:B20 and a helper series beginning in C2, enter =IF(B2="",NA(),B2) in C2 and fill it down. When B2 is blank or returns an empty string, the helper formula returns #N/A; otherwise it passes through the value from B2. Build the chart from the helper series and check that the missing points display as intended. This approach is preferable to silently changing blanks to zero, but it adds a helper column and visible error results that may need explaining.

How the chart type and axis affect the result

A line chart normally uses a category axis. Its categories—such as dates or months—are arranged in sequence, and the chart gives each category a position. A blank point therefore creates a missing spot in that sequence. Choosing to connect points draws a line between the available values; it does not remove the category or estimate an intermediate value.

A scatter chart works differently because its horizontal axis represents numeric x-values. It is a better fit when the horizontal spacing itself matters, such as measurements taken at irregular time intervals. Missing y-values can still create gaps or connected segments, but the horizontal distance between observations reflects the x-values rather than equal spacing between category labels. Changing from a line chart to a scatter chart can therefore change the story even when the source numbers are unchanged.

Also distinguish a blank value from a missing category. If February is absent as a row altogether, Excel may have no February category to display. If February has a row with a blank value, the chart can apply its empty-cell rule to that position. Include the intended dates or categories in the source range if you want the chart to preserve those intervals.

Hidden rows and filtered chart data

Hiding a worksheet row is not the same as leaving a cell blank. By default, charts usually omit data in hidden rows or columns. In the same Hidden and Empty Cells dialog, select Show data in hidden rows and columns if you want the chart to include that data. This is useful when rows are hidden for presentation but remain part of the series.

Filtering can also affect what appears, depending on how the chart and source range are set up. If a point seems missing despite a value in the worksheet, check whether its row is hidden or filtered, whether the chart uses the intended source range, and whether the series references the right column. This is often a better diagnosis than repeatedly changing the blank-cell option.

Common problems and practical checks

The line drops to the axis: Check whether the source cell is actually zero or contains a formula returning zero. If it is truly empty, set the chart to Gaps or Connect data points with line.
A blank-looking cell is plotted: Inspect for formulas, spaces, or other text. A space is text, not a blank cell, and a formula that returns an empty string is still a formula.
The line stops instead of bridging the blank: Confirm that Connect data points with line—not Gaps—is selected for that chart.
A point disappears unexpectedly: Check for #N/A, hidden rows, filtered data, or a source range that excludes the cell.
Dates appear evenly spaced when they should not: A line chart may be using a category axis. Consider a scatter chart if actual numeric spacing between dates or measurements matters.

Before publishing, compare the chart with a small table of the source values and ask what a reader would infer from each missing interval. In an operational dashboard, a gap may signal a data collection failure. In a summary chart, connecting across a single absent reading may improve readability. The visual choice should follow the data’s meaning, not simply make the line look smoother.

Frequently Asked Questions

How do I make Excel skip blank cells in a line chart?
Select the chart, then choose Chart Design > Select Data > Hidden and Empty Cells. Choose Gaps to leave a break, or Connect data points with line to draw across the missing point without plotting it as zero.

Why does my blank cell show as zero on the chart?
The cell may contain an actual zero or a formula returning zero rather than being empty. It may also contain a formula returning an empty string, which is not a genuinely blank cell. Inspect the formula bar and test with a truly empty cell.

Can Excel connect a line across missing data?
Yes. In the Hidden and Empty Cells dialog, choose Connect data points with line. Excel draws a segment between the available points on either side; it does not calculate or insert the missing value.

Can I make a chart ignore a formula that returns an empty string?
That result may not behave like a physically blank cell. A helper series that returns #N/A for unavailable values can be a more reliable way to omit points, but check the chart’s #N/A handling and explain the helper column to workbook users.

Why are values in hidden rows missing from my chart?
Charts generally omit hidden worksheet data unless you enable Show data in hidden rows and columns in the Hidden and Empty Cells dialog. Select that option if hidden values should remain visible in the chart.

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.