Excel Chart Ignore Blank Cells? How to Control Gaps

Yes. In Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Select Data > Hidden and Empty Cells, then choose whether empty cells appear as gaps, zeros, or connected points. If a cell only looks blank because a formula returns an empty string, Excel may not treat it like a truly empty cell, so the source data may need a different approach.

What Excel means by a blank cell

Excel charts distinguish between a cell that contains nothing and a cell that contains a value or formula that displays nothing. This distinction is the most common reason a chart seems to ignore its blank-cell setting.

Truly empty cell: Nothing has been entered in the cell. The chart’s empty-cell setting can determine how it is plotted.
Formula returning an empty string: The cell contains a formula, often one that returns "". It looks empty on the sheet but is not an actually empty cell, and a chart can treat it differently.
Cell containing a space: A space is text, not a blank. It can produce unexpected chart behavior and should be removed if the cell is meant to be empty.
Zero: A numeric zero is data. Excel plots it at zero; it is not a blank, even if the number format makes it look empty.

Before changing chart settings, click a suspicious cell and inspect the formula bar. If there is a formula, a space, or a zero, the cell is not truly empty. This check often explains why changing the chart’s treatment of blanks appears to have no effect.

Set a chart to show blanks as gaps

For a line chart where missing measurements should interrupt the line, use gaps. The chart will leave an open section rather than drawing a value at zero or bridging the missing observation.

Select the chart to show the chart-specific ribbon tabs.
Choose Chart Design > Select Data.
In the Select Data Source dialog, choose Hidden and Empty Cells.
Under Show empty cells as, select Gaps, then choose OK to close the dialog and apply the setting.

This setting applies to the selected chart, not to every chart in the workbook. If the chart still plots an unexpected point, check whether the source cell is a formula result, a zero, or text rather than an empty cell. The exact options that have a visible effect depend on the chart type; a line chart makes the difference between a gap and a connected segment especially clear.

Choose between gaps, zero, and connecting points

The dialog provides three ways to handle empty cells. Choose based on what missing data means, not simply on which option makes the chart look smoother.

Gaps: Leaves an interruption where the value is missing. This is usually the clearest choice when no measurement was recorded or the value is unknown.
Zero: Plots the missing position at zero. Use this only when an empty source cell genuinely means zero. A missing temperature reading, for example, is not automatically a temperature of zero.
Connect data points with line: Draws a line between the available points on either side of a blank. This can help show a trend across a short missing interval, but it can imply continuity where no observation exists. It is relevant to line-style charts, not to every chart type.

Suppose a weekly sales table has entries for Monday, Tuesday, and Thursday, but Wednesday is blank because the shop was closed. A zero may be appropriate if the table represents sales actually made each day. If Wednesday’s sales were not recorded, a gap or a connected line may be more honest than inventing a zero. The chart should communicate the meaning of the data, not conceal the difference between no activity and no information.

When formulas make cells look empty

A formula that returns "" is useful for keeping a worksheet tidy, but it can complicate charting. The chart sees a cell containing a formula, not an untouched empty cell. Depending on the chart and how the data is supplied, the result may appear as a zero or otherwise fail to follow the blank-cell option as expected.

One practical method is to use a helper column that returns #N/A where a value should be omitted. Excel charts generally do not plot the error value as an ordinary data point. In the example below, source values are in B2:B10 and the helper values go in C2:C10. Enter the formula in C2 and fill it down:

With source values in B2:B10, enter this in C2: =IF(B2="",NA(),B2). For a blank-looking or empty source cell, the helper returns #N/A; for a numeric value, it returns that value. Add the helper column to the chart instead of the original values.

In Microsoft 365 for Windows, select the chart and open Chart Design > Select Data > Hidden and Empty Cells. If the dialog offers Show #N/A as an empty cell, select it when you want those error results to appear as empty positions. The option’s availability and wording can vary between Excel releases. Check the chart after applying it, especially if the chart contains other error values that may indicate real problems in the source.

Do not use this technique to hide errors indiscriminately. A genuine calculation error can be useful evidence that the data needs attention. If the goal is only to leave gaps for missing observations, keep the helper formula and its purpose clear, and retain the original source column so the underlying values remain visible.

How the setting differs by chart type

Empty-cell behavior is most noticeable in line charts because a missing point can create a gap, a zero dip, or a line joining the neighboring observations. With a column chart, choosing zero may produce a bar of zero height, while a gap simply leaves no visible column at that position. Connecting points is not meaningful for a standard column chart.

For an XY (scatter) chart, the way blanks affect plotted pairs can depend on whether the missing value is in the X range or the Y range. Check that the X and Y ranges remain aligned: if one row is removed from only one range, points can be paired with the wrong coordinates. If the chart is a pie, doughnut, or another chart type that does not represent a continuous sequence, the line-chart idea of connecting across a gap does not apply. Review the result rather than assuming every chart type will display the same visual effect.

When comparing time-series data, keep dates or periods in the category or X-axis range even if the corresponding measurement is missing. Removing the entire row can compress the time axis and make observations that were separated by a day or month appear adjacent. A blank value preserves the position in the sequence; deleting the row changes the sequence itself.

Hidden rows and columns are a separate setting

Empty cells are not the same as hidden data. If a chart omits values because their worksheet rows or columns are hidden, check the separate option in the same area: select the chart, choose Chart Design > Select Data > Hidden and Empty Cells, and look for Show data in hidden rows and columns. Turn it on if the chart should include hidden source data; leave it off if hiding rows is intended to filter the chart.

This matters when a worksheet uses filters, manually hidden rows, or columns kept out of view for presentation. A chart that excludes hidden values may be behaving as configured, even though the cells contain numbers. Conversely, enabling hidden data can put values back into a chart when the worksheet view suggests they are excluded. Treat this option separately from the choice among gaps, zeros, and connected points.

Troubleshoot a chart that still shows a point

If the result does not match your choice, work through the source data before rebuilding the chart:

Click the cell and inspect the formula bar. Remove spaces or unintended values if the cell is meant to be empty.
Check whether a formula returns "". Consider a helper column with #N/A for values you want omitted.
Look for numeric zeros, including zeros hidden by number formatting. Formatting does not turn zero into a blank.
Confirm the chart’s data range in Chart Design > Select Data. The series may include extra rows or the wrong column.
Check whether hidden rows and columns are included. This is controlled separately from the empty-cell display choice.
Confirm that the chart type supports the visual behavior you expect, then inspect it at the missing-data positions.

Also distinguish a blank point from a missing category label. A chart can retain a category on the horizontal axis while showing no value for that category. If the category itself has been removed from the source range, the axis may change as well. Keeping the source table’s rows intact is usually preferable when the dates or categories matter.

Which option should you use?

Start by deciding what the empty cell represents. If it means “unknown” or “not collected,” a gap usually avoids implying a false measurement. If it means “none occurred,” zero may be correct, provided zero is a meaningful value for that measure. Connect points when a continuous trend is the intended message and the interpolation is not misleading; make clear that the intervening value was not observed if that distinction matters to readers.

There is no universal best setting. A chart for operational reporting may need to show zero activity explicitly, while a scientific or monitoring chart may need to preserve gaps in observations. If people will make decisions from the graph, label missing data or explain the convention in a note or legend. That context prevents readers from interpreting a gap, a zero, and a bridged line as interchangeable.

Frequently Asked Questions

How do I make Excel show blank cells as gaps in a line chart?

Select the chart, then choose Chart Design > Select Data > Hidden and Empty Cells. Under Show empty cells as, choose Gaps. If the point remains, check whether its source cell contains a formula, a zero, or a space rather than being truly empty.

Why does Excel plot a blank formula cell as zero?

A formula returning "" only looks empty; the cell still contains a formula. Charts may treat that result differently from a genuinely empty cell. A helper column returning #N/A for missing values can be a useful alternative, with the chart’s #N/A handling checked in the same dialog.

Can Excel connect a line across blank cells?

Yes. In the Hidden and Empty Cells dialog, choose Connect data points with line. This is useful when you want a line to continue across a missing observation, but it can suggest a continuous trend despite the missing data. It does not create a measured value for the blank.

Does a chart treat zero as a blank?

No. Zero is a numeric value and is plotted as zero. If a zero is merely formatted so it is not visible in the worksheet, the underlying chart data is still zero. Use an actually empty cell or a suitable helper result when the point should be omitted.

Why are hidden rows missing from my chart?

Charts can omit data in hidden worksheet rows or columns unless the chart is set to include it. Open Chart Design > Select Data > Hidden and Empty Cells and check Show data in hidden rows and columns if those values should appear. This setting is independent of how empty cells are displayed.

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.