Excel Chart Stop Line Dropping to Zero: Causes and Fixes

To stop an Excel line chart from dropping to zero when data is missing, select the chart and choose Chart Design > Select Data > Hidden and Empty Cells, then set Show empty cells as to Gaps. If the cells contain formulas that return an empty string, use a chart-helper column that returns #N/A for missing values, because a displayed blank from a formula may still be plotted as zero.

Why a line chart falls to zero

A line chart draws a point for each value in its series and connects neighboring points. When a point is interpreted as zero, Excel draws the line down to the chart’s zero position. That can make it look as if a measurement suddenly collapsed, even though the underlying data is simply absent.

The cause is usually one of three things: a cell is genuinely empty but the chart is set to treat empty cells as zero; a formula produces a value Excel charts as zero; or the source range includes cells that contain zeros or other unintended values. These cases look similar on the chart, but they need different fixes. First inspect the source cells and the series range rather than changing the vertical axis.

For example, suppose monthly readings are 12, 14, 13, blank, blank, and 15. If the blank months are interpreted as zero, the plotted line falls from 13 to the bottom of the chart and then rises to 15. Those two apparent changes are chart behavior, not evidence that the reading was actually zero.

Set empty cells to display as gaps

For truly empty cells, change the chart’s treatment of missing values. In Excel for Microsoft 365 on Windows:

Click the line chart to show the chart 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.

A gap leaves the missing portion unplotted instead of drawing down to zero. This is usually the right setting when no measurement was taken or a value has not arrived yet. The chart will not claim that a missing reading was zero.

The same chart dialog includes an option to connect data points with a line. That setting has a different purpose: it can draw a line across an empty interval, joining the values on either side. Choose gaps when the line should break at missing periods; choose the connect option only when showing a continuous trend across the missing interval is appropriate. A gap communicates that there is no plotted value there, while a connecting line can imply a trend between observations.

Check whether the cells are really empty

A cell can look blank without being empty. It may contain a formula that returns an empty string, a space, or a zero formatted to appear blank. The empty-cell setting is not a universal cure for these cases: a formula result such as an empty string can still be treated differently from a genuinely empty cell by a chart. A space is text, not an empty cell, and a numeric zero remains a zero even if its number format hides it.

Click a suspicious cell and inspect the formula bar. If it contains a formula, review what the formula returns for missing records. Also check the number format and any custom format that might hide a displayed zero. To verify a suspected zero, temporarily apply a standard number format or inspect the formula bar. If the source really contains zero, the chart is accurately plotting the stored value, even if that value is not what you intended to record.

This distinction matters for data quality. A zero can mean “measured and none,” such as zero units sold. A blank can mean “not measured,” “not yet reported,” or “not applicable.” Decide what the data means before changing the chart, and represent the missing state consistently in the source.

Handle formula-generated blanks with a helper column

If the chart source is produced by formulas and missing rows return an empty string, make a separate chart series that returns #N/A for those rows. Excel charts generally do not plot #N/A as a data point, so the line breaks rather than falling to zero. Keep the original calculation column if other parts of the workbook need its blank-looking result, and chart the helper column instead.

Assume dates are in A2:A20 and readings or formula-generated blanks are in B2:B20. Enter this in C2 and fill it down; use C2:C20 as the chart’s value range:

=IF(B2="",NA(),B2)

If B2 contains 12, the helper returns 12; if B2 is empty or its formula returns an empty string, the helper returns #N/A. A real zero in B2 stays zero, which preserves the difference between a zero reading and missing data. The visible error values in the helper column are intentional; if desired, place that helper column away from the main report, but do not replace the error with a blank before charting.

Do not use this method if errors in the source are meaningful values that you need the chart to display. In that case, correct or classify the data first. Also test the result after changing the source, because a helper formula that labels every blank as missing may not match a workbook where blank has a different meaning.

Make sure the chart range excludes unwanted rows

Sometimes the chart is not misreading blanks; its series simply extends farther than the data. A range that includes future months, unused rows, or a totals row can add zeros to the plot. Select the chart and open Chart Design > Select Data. Review the horizontal-axis labels and each series’ values to confirm that they cover only the intended cells.

For a one-time chart, edit the series range so it ends at the last valid observation. If the data grows regularly, consider turning the source into an Excel table: select the data and choose Insert > Table. A chart based on table data can expand as rows are added, but the table does not decide what missing values mean. Continue to check that new rows contain genuine values, intentional blanks, or an appropriate missing-value formula.

Check for hidden rows as well as unused rows. The Select Data Source dialog’s Hidden and Empty Cells options can control whether data in hidden rows and columns is shown. If a value appears to be missing from the chart, verify that it is not excluded by a hidden row or column setting.

Choose between a gap, a zero, and a connected line

There is no universally correct way to draw missing data. The chart should match the meaning of the dataset and avoid implying a measurement that was never recorded.

Use zero when the value was measured and is truly zero. For example, zero orders on a particular day is different from a day for which the order report has not arrived.
Use a gap when the value is unknown, unavailable, or not applicable and you do not want to imply what happened between observations.
Connect across the gap when the purpose is to show an overall trend and the connecting segment is acceptable as a visual bridge. Remember that it does not create data for the missing period.
Use a separate marker or annotation when readers need to know why a value is absent, such as a sensor outage or an incomplete reporting period.

For the example of readings 12, 14, 13, missing, missing, 15, a gap honestly shows that the middle readings are unavailable. Connecting 13 to 15 gives a visual path across the interval but does not establish the actual readings in between. Plotting zeros would assert two measurements of zero and should be reserved for that factual situation.

Why changing the axis is usually the wrong fix

A common response is to change the vertical axis minimum from zero to a larger value. That may make a drop less conspicuous, but it does not correct the missing-data interpretation. The chart still contains points at zero; they are merely outside or near the edge of the displayed scale. An axis adjustment can also exaggerate ordinary variation, so use it only when the scale is appropriate and clearly readable—not as a substitute for handling blanks properly.

Likewise, changing from a line chart to a different chart type does not automatically repair the source data. A line chart is often suitable for values over time, but it will still need a clear rule for missing points. A scatter chart can be useful when the horizontal values are numeric dates or measurements with uneven spacing, but the source and treatment of missing values still need to be checked.

Windows, Mac, and Excel for the web

The steps above use Excel for Microsoft 365 on Windows. In current desktop Excel for Mac, select the chart and look for the equivalent chart-design controls, including Select Data and the empty-cell options; dialog placement and wording can vary slightly by release. If a control is not visible, check the chart-specific ribbon after selecting the chart rather than the ordinary worksheet tabs.

Excel for the web has a more limited interface for some chart-formatting tasks, and the available options can differ from desktop Excel. If you cannot find the empty-cells control in the web app, open the workbook in the desktop application and set the chart there. The chart and workbook data remain the same; the issue is access to the setting, not a different data rule.

Frequently Asked Questions

Why does Excel treat a blank chart cell as zero?
Excel can plot empty cells as zero when that option is selected, and formula-generated blanks may not behave like truly empty cells. Check the chart’s Hidden and Empty Cells setting, then inspect the cell contents and formula.

How do I stop a line chart from joining across blank cells?
Open Chart Design > Select Data > Hidden and Empty Cells and select Gaps. This breaks the plotted line at empty cells instead of connecting the points on either side.

Does returning an empty string make a chart skip a point?
Not reliably. A formula returning an empty string can still appear as zero in a chart. For formula-driven missing values, use a helper series that returns #N/A for missing records, or adjust the chart range so it excludes rows that are not ready.

Should missing values be zeros or blanks?
Use zero only when zero is the actual measured value. Use a blank or another explicit missing-value marker when no value is known. The choice affects calculations and the chart’s meaning, so document it if readers could confuse the two.

Why does my chart still drop after I select Gaps?
Check whether the cells contain numeric zeros, formulas, spaces, or a range extending into unused rows. Gaps applies to empty cells; it cannot turn a real zero into missing data. A formula-helper column returning #N/A can handle formula-generated missing values.

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.