PowerPoint Chart Empty Cells Treated as Zero vs Gap: How to Set

When you create a chart in PowerPoint from Excel data, empty cells in the source range can appear as zeros or as gaps in the chart line. This behavior can distort your data story, especially in line or scatter charts where a missing value should break the trend line rather than drop to zero. The difference between treating empty cells as zero versus as a gap changes how your audience interprets the chart’s continuity. This article explains the root cause of this setting, shows you how to change it in both PowerPoint and Excel, and covers common mistakes to avoid when hiding data gaps.

Why PowerPoint Charts Show Zero Instead of a Gap for Empty Cells

PowerPoint charts are built on top of the Microsoft Graph chart engine, which shares its data-handling rules with Excel. When a chart’s source data contains blank cells, the chart engine must decide how to display the missing point. The default behavior depends on the chart type and the version of Office. In most line charts, empty cells are treated as zeros by default unless you explicitly change the setting.

The technical reason is that the chart engine does not distinguish between a cell that is truly empty and a cell that contains a zero value. Without a specific instruction, it assumes the data series should continue, so it plots the missing point at zero on the value axis. This creates a sharp dip in the line that can mislead viewers into thinking the actual value dropped to zero rather than being absent.

The setting that controls this behavior is stored in the chart object itself, not in the PowerPoint file. If you copy a chart from Excel, the empty cell setting from the Excel chart is preserved. If you create the chart directly in PowerPoint using the Insert Chart dialog, the chart uses the default Office setting, which varies by chart type: line charts default to Zero, scatter charts default to Gap, and column charts default to Zero.

Steps to Change How Empty Cells Are Displayed in a PowerPoint Chart

You can change the empty cell behavior for any chart in PowerPoint using the Select Data Source dialog. This works for charts created in PowerPoint and for charts pasted from Excel as long as the chart is not broken or unlinked.

1. Click the chart to select it
   Make sure the chart is active. You should see the Chart Design and Format tabs appear on the ribbon.
2. Go to Chart Design > Select Data
   On the Chart Design tab, click Select Data in the Data group. The Select Data Source dialog opens.
3. Click the Hidden and Empty Cells button
   At the bottom-left corner of the Select Data Source dialog, click the Hidden and Empty Cells button. This opens the Hidden and Empty Cell Settings dialog.
4. Choose Gap, Zero, or Connect data points with line
   In the dialog, select one of the three options under Show empty cells as:
   – Gap: Leaves a break in the line or hides the column/bar for that data point.
   – Zero: Plots the missing value as zero on the value axis.
   – Connect data points with line: Only available for line charts; draws a line across the missing point using the neighboring values, ignoring the empty cell entirely.
5. Check the box for Show data in hidden rows and columns if needed
   If your source data is in a worksheet and you have hidden rows or columns that contain data you want to include, check this box. Otherwise, leave it unchecked.
6. Click OK to close both dialogs
   Click OK in the Hidden and Empty Cell Settings dialog, then click OK in the Select Data Source dialog. The chart updates immediately.

Changing the Setting in Excel Before Copying the Chart

If you built the chart in Excel and then copied it into PowerPoint, you must change the empty cell setting in Excel first. The setting is not editable from PowerPoint for linked charts. In Excel, follow the same steps: click the chart, go to Chart Design > Select Data > Hidden and Empty Cells, and choose Gap or Zero. After you copy the chart and paste it into PowerPoint, the setting is preserved.

What Happens With Scatter and Bubble Charts

Scatter and bubble charts behave differently. For these chart types, empty cells are always treated as gaps by default. If you change the setting to Zero, the chart will still show a gap because scatter charts plot points by their X and Y coordinates. A missing Y value cannot be plotted as zero because the X coordinate is still present. For scatter charts, the only practical option is Gap or Connect data points with line.

Common Misconceptions and Limitations When Setting Empty Cell Display

I Changed the Setting but the Chart Still Shows a Zero

This happens when the chart is linked to an Excel workbook and the Excel chart still has the Zero setting. The PowerPoint chart reads the setting from the linked Excel chart, not from its own dialog. Open the Excel file, change the setting there, and save the workbook. Then refresh the link in PowerPoint by right-clicking the chart and selecting Edit Data. If the chart is embedded instead of linked, the setting is stored inside the PowerPoint file and can be changed directly in PowerPoint.

Empty Cells vs Cells With a Formula That Returns an Empty String

A cell that contains a formula such as =IF(A1>0,A1,””) is not truly empty. The formula returns an empty string, which the chart engine treats as a zero. To make the chart treat it as a gap, the formula must return NA() instead. Use =IF(A1>0,A1,NA()) in the source cell. The chart engine recognizes NA() as a missing value and displays a gap.

The Gap Option Does Not Work for Stacked Charts

In stacked column or stacked area charts, the Gap option is not available. Stacked charts require a continuous series to calculate the cumulative height of each segment. If a data point is missing, the chart engine cannot determine the stack position, so it defaults to Zero. The only workaround is to use a non-stacked chart type or to fill the empty cells with a placeholder value and hide the data point using a custom number format.

Empty Cell Display Options: Gap vs Zero vs Connect Data Points

Item	Gap	Zero	Connect data points with line
Description	Shows a break in the line or hides the column/bar for the missing point	Plots the missing value at zero on the value axis	Draws a straight line across the missing point using the two neighboring values
Best for	Line charts where a missing data point should not imply a value of zero	Column charts where a zero value is meaningful and should be shown	Line charts with occasional missing data where you want to maintain a continuous trend
Available in	Line, scatter, bubble, area charts	All chart types except scatter and bubble	Line and scatter charts only
Impact on trend line	Creates a break; trend line stops before the gap and resumes after	Pulls the line down to zero, often creating a misleading dip	Ignores the missing point; trend line continues as if the point never existed

You can now control exactly how empty cells appear in your PowerPoint charts. Use the Hidden and Empty Cell Settings dialog from Chart Design > Select Data. For linked Excel charts, make the change in Excel before copying. Remember that formulas returning empty strings are not the same as empty cells. If you need a gap, use the NA() function instead. As an advanced tip, you can apply this setting to multiple charts at once by selecting all charts on a slide before opening the Select Data Source dialog, but only if the charts share the same data source.