Excel Chart Skip Blank Data? Control Gaps and Zeros

Yes. In Excel for Microsoft 365, a chart can show blank cells as gaps, treat them as zero, or—on line charts—connect the points across them. Select the chart, choose Chart Design > Select Data > Hidden and Empty Cells, then pick the behavior you want under Show empty cells as.

The right choice depends on what a blank means in your data. A missing measurement is not necessarily the same as a value of zero, and a formula that displays an empty string may not behave like a genuinely empty cell. Understanding those differences helps you avoid charts that imply a trend or value that is not in the worksheet.

What Excel Means by a Blank Chart Cell

A cell can look empty for several different reasons. It may contain nothing at all, contain a formula that returns an empty string, or contain a value hidden by formatting. Those cases can look identical in the grid but represent different data to Excel, so it is worth checking the source before adjusting the chart.

Truly empty cell: Nothing has been entered in the cell. Excel’s empty-cell setting can display it as a gap, a zero, or a connected line segment where that option is available.
Formula returning an empty string: The cell contains a formula, often using IF, but displays no text. It is not truly empty, and a chart may treat it differently from a blank cell.
Hidden value: The cell contains a number or text that a number format or font color makes hard to see. The chart may still plot the underlying value.
Error value: A cell such as #N/A is not a blank. Excel may leave a gap for it, but it remains an error in the worksheet and should be used deliberately.

For a quick check, select a cell that appears blank and look at the formula bar. If a formula is present, the cell is not empty. You can also test whether a cell is truly blank with Excel’s Go To Special command: press F5, choose Special, and select Blanks. Cells containing formulas are not selected as blanks.

Set Blank Cells to Show as Gaps, Zeros, or Connected Points

In Excel for Microsoft 365 on Windows, use these steps to change how a chart handles empty cells:

Click the 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, if that option is available for the chart.
Choose OK, then check the chart against the worksheet data.

Gaps leaves an interruption where data is missing. Zero plots the missing position at zero, which can make a line drop sharply or a column extend from the axis. Connect data points with line draws a line across the blank position, visually implying continuity between the surrounding observations. It does not fill the worksheet cell or create a measured value.

The available choices can vary with chart type. Connecting points is relevant to line charts; it is not a meaningful way to display every kind of chart. For a column chart, for example, choosing gaps rather than zero typically leaves no column for that category. Check the result in the specific chart rather than assuming every chart type will render the options identically.

Choose the Right Treatment for Your Data

Use a gap when the data is genuinely unavailable and you want readers to see that the series is interrupted. A line joining observations on either side of a gap can suggest that values between them are known or that the trend is continuous. That may be reasonable for some measurements, but misleading for others.

Use zero only when zero is the actual value. If a store had no sales on a date, zero may be correct. If the sales figure has not arrived yet, zero falsely says the store made no sales. These distinctions matter because viewers often read a chart faster than they read its notes.

Connecting data points can make a chart easier to follow when an occasional observation is missing and the surrounding values still describe a meaningful trend. Avoid it when the gap represents a long outage, a change in measurement method, or an interval when the series should not be compared. For example, joining a temperature reading from Monday to one from Friday can suggest a smooth change even though the readings for Tuesday through Thursday are unknown.

A practical rule is to document what missing data means before choosing a chart setting. If the chart will be shared, add a note such as “gaps indicate unreported measurements” when the visual treatment might otherwise be misunderstood.

Formula Blanks and a Reliable Workaround

A common source of confusion is a formula that returns an empty string. For example, a table might use a formula to leave future dates visually blank until values are entered. Although the cell looks empty, the chart can interpret that formula result differently from a truly empty cell. If the chart draws unexpected zero points, inspect the source formulas before changing the chart’s empty-cell setting.

If you want a chart to omit a value conditionally, one option is to return #N/A for the unwanted point. With source values in B2:B13 and a chart series using those cells, enter this in a helper column’s first data row and fill it down: =IF(B2="",NA(),B2). For a blank B2, the formula returns #N/A; for a nonblank B2, it returns the value from B2. Add the helper column to the chart instead of the original values and check the result. Excel commonly leaves #N/A points unplotted, while other errors can affect the chart differently.

This workaround is useful when the chart needs a conditional rule that the empty-cell setting cannot express. It also has a trade-off: the helper range contains visible error values, which can be inconvenient in a report or when used by other formulas. Keep the helper data clearly labeled, or place it on a separate worksheet. Do not replace missing values with zero just to make errors disappear unless zero is genuinely correct.

Hidden Rows, Filters, and Chart Data

Blank-cell handling is different from whether a chart plots data in hidden rows or columns. If a value exists but its row is filtered out or hidden, Excel may omit it from the chart. To include such data, select the chart and choose Chart Design > Select Data > Hidden and Empty Cells. In the dialog, select Show data in hidden rows and columns if you want hidden source data included.

This option can be useful when you hide helper columns or filter a table but still want a chart to show the full series. It can also produce a surprising result: a chart may continue to display values that are not visible in the worksheet because their rows are hidden. If the chart is meant to respond to a filter, leave hidden data excluded and confirm that the chart updates as expected.

For charts based on filtered tables, behavior can depend on how the chart and source range are set up. After changing filters or hiding rows, inspect both the visible data and the plotted series. The chart’s Select Data dialog also lets you check the source range and edit individual series, which can reveal whether the chart is using the intended cells.

Why a Chart May Still Show an Unexpected Point

If changing the setting does not fix the chart, check these common causes:

The source is not actually blank. A formula, a space character, or a hidden number may be present. Delete the contents or adjust the formula or formatting as appropriate.
The chart uses a different range. Open Chart Design > Select Data and confirm the series values include the cells you are inspecting.
A zero is a real value. Excel is correctly plotting it. If zero means “not yet reported” in your process, change the source data logic rather than expecting the chart to infer that meaning.
The chart type affects the appearance. A blank in a line chart, a column chart, and an XY scatter chart can have different visual consequences. Confirm the chart type and how its axes represent categories or numeric coordinates.
Formatting hides the difference. A zero-value data label, an axis crossing, or a number format may make a plotted zero hard to recognize. Check the underlying cell and temporarily use a simple chart format to diagnose it.
The cell contains an error. Check for #N/A or another error rather than treating every non-number as a blank. Correct the source or use an intentional missing-value strategy.

When diagnosing, change one thing at a time: inspect the source cell, verify the chart’s data range, then test the empty-cell setting. This is more reliable than changing several options and trying to infer which one affected the result.

Excel on Mac and Excel for the Web

Excel for Mac has chart data controls, but ribbon labels and dialog layouts can differ slightly by release. Select the chart and look under the chart design tab for Select Data; then check the dialog for Hidden and Empty Cells or the corresponding empty-cell options. If a command is not visible, use Excel’s Search box to find Select Data. The Windows path above applies to Microsoft 365 on Windows.

Excel for the web supports editing many chart settings, but some chart controls and detailed dialog options may not be available in every web release. If you cannot find the empty-cell or hidden-data setting, open the workbook in the desktop app and change it there. Afterward, save the workbook and verify how the chart appears in the browser, especially if other people will view it online.

Frequently Asked Questions

How do I make an Excel chart ignore blank cells?
Select the chart and choose Chart Design > Select Data > Hidden and Empty Cells. Choose Gaps to leave the missing position unplotted. For formula-generated blanks, check whether the source formula is returning an empty string; a helper column returning #N/A for unwanted points may be more dependable.

Can Excel connect a line across blank cells?
Yes, for supported line charts, choose Connect data points with line in the Hidden and Empty Cells dialog. This connects the neighboring plotted points visually; it does not calculate or insert values for the missing dates.

Why does a blank cell appear as zero in my chart?
The chart may be set to show empty cells as zero, or the cell may contain a formula result or other content rather than being truly empty. Check the setting and inspect the cell in the formula bar.

Does an empty string from a formula count as a blank?
It looks blank in the worksheet, but the cell still contains a formula. A chart may not treat it like a genuinely empty cell. Test the chart with your actual formula, or use a helper series that returns #N/A when a point should be omitted.

How do I show data from hidden rows in a chart?
Select the chart, open Chart Design > Select Data > Hidden and Empty Cells, and select Show data in hidden rows and columns. Clear that option if filtered or hidden values should disappear from 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.