Excel Line Chart: How to Connect Gaps

To connect gaps in an Excel line chart, select the chart and choose Chart Design > Select Data > Hidden and Empty Cells. Under Show empty cells as, select Connect data points with line, then choose OK. Excel will draw a line across empty cells instead of leaving a break.

What connecting gaps changes

A line chart plots values in sequence and draws lines between the points. When a cell in the series is empty, Excel normally leaves a visible gap in the line at that position. Connecting gaps tells Excel to bridge that missing position by drawing a line from the last plotted point before it to the next plotted point after it.

For example, imagine a daily temperature series with readings of 18, 20, a blank, and 23. With gaps, the line stops after 20 and resumes at 23. With connected points, Excel draws a segment from 20 to 23 across the missing day. The empty cell itself does not become a measurement: Excel does not add a new point or calculate a replacement value. It simply changes how the chart draws the series around the blank.

This distinction matters when readers might interpret a continuous line as evidence that measurements exist throughout the interval. Connecting gaps can make a chart easier to read, but it can also hide missing data. Consider adding a note, marker, or other cue if the absent observations are important to the meaning of the chart.

Steps in Excel for Microsoft 365 on Windows

Click the line chart. If the chart is not selected, the contextual chart tabs will not appear.
On the ribbon, select Chart Design > Select Data. Excel opens the Select Data Source dialog box.
Choose Hidden and Empty Cells. In the dialog that opens, find Show empty cells as.
Select Connect data points with line, then select OK to close that dialog.
Select OK again in Select Data Source. Check the chart to confirm the line now bridges the blank.

The setting applies to the selected chart, not every chart in the workbook. If you have several charts, repeat the steps for each one. You can also return to the same dialog and choose Gaps to restore breaks, or choose Zero if you specifically want empty cells represented as zero values. Choosing zero changes the visual result substantially: the line may drop to the chart’s zero level, which is not the same as connecting the surrounding points.

Why Excel shows a gap in the first place

A gap can be useful because it distinguishes missing information from a real zero. Suppose a store records weekly sales. A blank for a week when the reporting system failed does not necessarily mean the store sold nothing. If the chart turns every blank into zero, viewers may conclude that sales actually fell to zero. Leaving a gap makes the absence of a reading visible; connecting the points instead emphasizes the overall direction while still leaving the underlying cell blank.

Empty cells can appear for several reasons: someone cleared a value, a data import omitted a record, a formula or report has not yet produced a result, or the source table deliberately leaves future dates empty. Before changing the chart, decide which of these situations applies. If the blank means no activity and zero is a valid observation, entering an actual zero may be clearer than relying on the chart’s empty-cell setting. If it means unknown or unreported, zero can be misleading.

Also check whether the cell is truly empty. A cell containing a space, text, or a formula that returns an empty text string may not be handled exactly like a genuinely blank cell. If the chart still breaks after you choose to connect points, inspect the source cell and test the chart with a genuinely empty cell. This helps identify whether the issue is the chart setting or the value in the worksheet.

Formula blanks and missing values

Many worksheets use formulas to hide results until input is available. A formula may display nothing even though the cell is not empty. That difference can affect chart behavior: a chart may treat a formula result differently from a cell that has been cleared. If you see an unexpected zero, gap, or plotted point, select the source cell and look at the formula bar rather than judging only by what appears on the sheet.

There is no universally best representation for a missing result. A true blank can be appropriate when the chart’s empty-cell setting gives the desired display. A zero is appropriate only when zero is the measured or intended value. In some charting workflows, #N/A is used to keep a value from being plotted, but how it appears alongside a line and the chosen empty-cell behavior should be checked in the actual chart. Do not substitute an error value casually in a report: it can be confusing to people reading the worksheet, and it may affect other formulas.

When data comes from a formula or an external system, test a small copy of the chart with representative values: a true blank, a formula returning an empty display, a zero, and a missing-value marker if your workbook uses one. That simple comparison is more reliable than assuming that visually similar cells are equivalent to Excel.

Line charts, date axes, and other chart types

The connect-gaps option is intended for charts that display a series as a line, but the visual result depends on the chart design. A standard line chart places categories in sequence along its horizontal axis. If the categories are dates, the chart may show each listed date as a category, so a missing date in the table and a blank value on an existing date are different situations. Connecting empty cells bridges a blank value in the plotted series; it does not automatically create missing date rows or decide how much time passed between observations.

An XY (scatter) chart uses numeric horizontal values and positions points according to those values. That makes it useful when the spacing between measurements matters, such as readings taken at irregular times. A line chart with a category axis may space categories evenly even when their dates are not evenly spaced. If the apparent gap is about elapsed time rather than a blank observation, check the chart type and axis setup as well as the empty-cell setting.

For a column chart, a gap generally means a missing column rather than a broken connecting line, so the same setting may not solve the visual problem you have. Likewise, a chart with multiple data series can have a gap in one series while other series remain continuous. The setting affects empty cells in the selected chart, so verify every series after changing it.

Hidden rows and columns are a separate setting

The Hidden and Empty Cells dialog includes an option for whether to show data in hidden rows and columns. This is related to chart visibility but is not the same as connecting gaps. If a source row is hidden and the chart omits its value, enabling the hidden-data option can make that value appear. If a source cell is empty, choosing Connect data points with line controls how the series is drawn around it.

When troubleshooting, check both settings if the chart omits values unexpectedly. For example, filtering or hiding worksheet rows may change what appears in a chart depending on whether hidden data is included. Turning on the hidden-data option does not fill blanks, interpolate values, or connect gaps by itself. It only changes whether hidden source data is eligible to appear in the chart.

Common problems and ways to check them

The line still breaks: Confirm that you selected the correct chart and series source. Check whether the cell is truly empty or contains text, a formula result, or an error.
The chart drops to zero: Reopen Hidden and Empty Cells and confirm that Connect data points with line is selected rather than Zero.
The line bridges too much: Excel connects across empty positions; it does not know whether a long run of missing dates makes that connection misleading. Consider retaining a gap or adding a note for extended periods with no data.
Some dates are missing from the chart: Check the source range and hidden-row setting. A date row that is absent from the plotted range is not the same as a blank value within the range.
The chart looks different after editing the data: Revisit the source range and chart settings. Adding rows, changing formulas, or replacing blanks with zeros can change what Excel plots.

To diagnose a chart safely, make a copy of the worksheet or chart first, then test one representative blank. This makes it easier to tell whether the setting works without accidentally changing the report’s original data or presentation. Remember that a line drawn across a gap is a visual connection, not a statistical estimate. Excel does not infer the missing values just because the line appears continuous.

When to connect gaps and when to leave them

Connecting gaps is often useful when a small number of missing readings interrupt an otherwise continuous trend and the chart’s purpose is to show the broader pattern. For example, a sensor might miss one scheduled reading while the values before and after it still provide useful context. The connected line keeps the overall trend legible, especially in a compact dashboard.

Leave gaps when the missingness itself is important, when there is a long period without observations, or when a continuous line could imply unsupported certainty. In a clinical, scientific, operational, or financial report, readers may need to distinguish measured values from absent ones. A short explanation such as “line connects isolated missing readings” can prevent a reasonable-looking line from being mistaken for complete data.

There is also a middle ground: keep the gap and mark missing periods in a note, or show the data table beneath the chart so readers can see which observations are absent. The right choice depends on what the chart is meant to communicate, not simply on which option makes the line look smoother.

Frequently Asked Questions

Can Excel automatically connect blank cells in a line chart?
Yes. Select the chart, then choose Chart Design > Select Data > Hidden and Empty Cells. Select Connect data points with line under Show empty cells as. The choice is saved for that chart.

Does connecting gaps fill the blank cells?
No. It changes the line drawn in the chart but does not insert values into the worksheet or calculate estimates. The source cells remain blank, and the chart should not be treated as evidence that a value was measured at every point.

Why does my formula blank still show as a gap or zero?
A cell that looks empty may contain a formula, text, or another value rather than being truly blank. Inspect the cell in the formula bar and test the chart with a cleared cell. Formula results and chart types can affect how Excel interprets the point.

Can I connect gaps in Excel for Mac?
Excel for Mac has chart data controls for empty cells, though the dialog placement or wording can differ by release. Select the chart and look under the chart design or data-selection controls for Hidden and Empty Cells. If the option is not available in your version, try opening the workbook in desktop Excel for Microsoft 365 on Windows.

Does this setting connect gaps in every chart in my workbook?
No. It applies to the chart you edited. Repeat the setting for each chart that needs it, and review each one after changes to its source 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.