Excel Line Chart: How to Fill Gaps in Missing Data

In Excel for Microsoft 365 on Windows, select the chart, then choose Chart Design > Select Data > Hidden and Empty Cells and select Connect data points with line. Excel will draw a line across blank cells, but it will not calculate or insert the missing values.

What filling a gap means in an Excel chart

A line chart normally represents each row or column of values as a series of points connected in order. If a cell in a series is genuinely empty, Excel can leave a visible break in the line, plot the empty cell as zero, or connect the points on either side. The setting controls how the chart draws the missing point; it does not alter the worksheet data.

For example, imagine monthly readings of 12, 15, a blank, and 21. With Connect data points with line selected, the chart draws a segment from 15 to 21 across the missing month. It does not determine whether the missing reading was 17, 18, or something else. The segment is only a visual bridge between known values.

This distinction matters when the chart is used to communicate trends. Connecting a gap can make a series easier to follow, but it can also suggest continuity where no measurement exists. If the blank represents a period when data could not be collected, a visible break may be more honest than a continuous line.

How to connect points across blank cells

Use the chart’s empty-cell setting to bridge gaps in a standard line chart. The following steps apply to Excel for Microsoft 365 on Windows:

Select the line chart. If the chart is not selected, Excel will not show its chart-specific ribbon tabs.
Open Chart Design on the ribbon.
Choose Select Data.
In the Select Data Source dialog, choose Hidden and Empty Cells.
Under Show empty cells as, select Connect data points with line, then choose OK to close the dialog and OK again if needed.

Look at the affected series to confirm that the gap is bridged. If there are several series, the setting applies to the chart’s empty cells generally, so check each one. The setting does not fill cells on the worksheet, and it does not add a new data point that can be used in calculations or labels.

In current desktop Excel for Mac, select the chart and use the Chart Design tab’s Select Data command to reach the data-source options; the wording and dialog layout can vary slightly by build. Excel for the web has fewer chart-formatting controls than the desktop application. If you cannot find the empty-cell option in the web interface, open the workbook in desktop Excel, change the setting there, and then save the workbook.

What the three empty-cell choices do

The Hidden and Empty Cells dialog offers three choices for how Excel displays empty cells. Choosing the right one depends on what the missing value means, not merely on which option makes the line look smoothest.

Gaps: Excel leaves a break in the plotted line at an empty cell. Use this when you want readers to see that the series has a missing observation.
Zero: Excel plots the empty cell at zero. Use this only when an empty cell really should represent a value of zero. A zero is a real data value in the chart, not a neutral way to hide missing information.
Connect data points with line: Excel draws a segment between the plotted points before and after the empty cell. Use it when a continuous visual path is useful, while making clear that the value between those observations is unknown.

These choices can produce substantially different impressions. Suppose a production chart has 40 units in January, a blank for February, and 50 in March. Zero would create a sharp drop to zero and a rise afterward. Gaps would show two disconnected portions of the line. Connecting the points would show a continuous rise from 40 to 50, but would not establish what happened in February.

Check what is actually in the source cells

A chart gap is not always caused by a physically empty cell. The source range might contain a formula, a text value, an error, a space character, or a cell that looks blank because of formatting. Those cases may not be handled like a truly empty cell. Before changing chart settings, select the source cell and inspect the formula bar or the formula itself.

A formula that displays an empty string can look identical to a blank cell in the worksheet, but it is still a formula. Depending on the chart type and how the data is produced, Excel may not treat that result exactly like a cell that has never contained anything. Test the chart after changing the empty-cell setting rather than assuming that every visually blank cell will be bridged in the same way.

Also check whether the cell contains a space. A space is text, not an empty cell. If the source data comes from an import or a formula, clean or correct the source as appropriate; do not replace an unknown reading with zero just to make the chart behave. For errors such as #N/A, understand why the error occurs and verify how the particular chart displays it before relying on it as a way to create a gap.

A practical example: monthly readings

Suppose a worksheet has month names in A2:A5 and readings in B2:B5: January 12, February 15, March blank, and April 21. Select both columns, insert a line chart, and then set empty cells to Connect data points with line. Excel will draw January to February and then a segment from February to April. March remains a category on the chart’s horizontal axis, but the chart does not show a measured March value.

If the missing March reading means the instrument failed, leaving a gap can be the clearest choice: viewers can see the break and avoid mistaking an estimated trend for an observation. If the aim is to show the broad direction across the entire period, connecting may be reasonable, provided a note explains that the missing month was not measured. If March is known to have had no activity and that means zero readings, enter 0 in the worksheet; then zero is actual data rather than a display substitute.

For a report, consider adding a note near the chart such as “March reading unavailable; line connects adjacent observations.” That short explanation prevents the continuous segment from being mistaken for a measured March value. If the chart will be shared without its source table, the note is especially useful.

Choose between connecting, leaving a gap, and estimating

Connecting points is a display decision. Estimating a missing value is a data decision. If you need an estimated value—for example, to calculate a monthly average or feed a downstream analysis—decide on a defensible estimation method, record the estimated value separately, and label it as estimated. The chart’s connection setting cannot provide that estimate.

Use the meaning of the blank to guide the choice:

If the observation was not collected or is unreliable, a gap often communicates the limitation most plainly.
If a continuous line helps readers follow a trend and the missing interval is short, connecting may be suitable with an explanatory note.
If the blank means a confirmed value of zero, enter a numeric zero in the data rather than asking the chart to interpret an empty cell as zero.
If readers need the actual missing value, calculate or obtain it through a documented process; do not imply that the chart setting supplies it.

Think about the horizontal axis, too. A line chart may use categories, such as month names, or a date axis that positions dates according to time. Connecting points draws a segment between the neighboring plotted observations in the series. It does not make observations evenly spaced in time when the underlying dates are not evenly spaced. If the exact spacing between numeric or date values matters, an XY (Scatter) chart may represent that spacing more appropriately than a category-based line chart.

Common problems and quick checks

If the line still appears broken after selecting Connect data points with line, first confirm that you changed the setting for the chart you are viewing. Then inspect the source range and verify that the missing position really is an empty cell rather than text, a formula result, or an error. Check that the chart series includes the expected rows and that the gap is not caused by an omitted row or a different series.

If Excel draws the line down to zero, the chart may be set to plot empty cells as zero, or the source may contain a numeric zero rather than a blank. Select the source cell and inspect its contents. A cell containing 0 is a data point; the empty-cell setting does not turn it into a gap.

If the chart has hidden rows or columns, the Hidden and Empty Cells dialog also includes an option to show data in hidden rows and columns. That option is separate from the empty-cell choices. Enable it only if you want data from hidden worksheet ranges to appear in the chart. It does not repair missing values or change how blanks are connected.

Finally, check whether the chart is a line chart or another chart type. The dialog and behavior can differ by chart type, and a chart with markers may make the absence of an observation easier to notice than a line alone. After changing the setting, review the full chart at its normal display size; a bridge that looks clear when enlarged may be misleading in a small report or presentation.

Frequently Asked Questions

Can Excel automatically interpolate a missing value in a line chart?
No. Connect data points with line draws a line across the blank; it does not calculate an intermediate value or write one into the worksheet. If you require an estimate, create and document it separately.

How do I make a blank cell show as zero in a line chart?
Select the chart and go to Chart Design > Select Data > Hidden and Empty Cells, then choose Zero. Use this only when treating the blank as zero is appropriate. If zero is the actual observation, entering 0 in the source cell makes the data meaning explicit.

Why does my chart show a gap even though the cell looks blank?
The cell may contain a formula returning an empty string, a space, or another value that only appears blank because of formatting. Inspect the cell and test the chart with the actual source contents in mind; visually blank is not always the same as truly empty.

Can I fill the gap without changing the worksheet data?
Yes. Choose Connect data points with line in the chart’s Hidden and Empty Cells options. The chart’s appearance changes, but the empty source cell remains empty.

Should I connect gaps in a report chart?
Connect them when continuity helps readers follow the series and does not imply a measurement that was never made. If the missing observation is important, leave a gap or add a clear note explaining that the line bridges unavailable data.

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.