Make an Excel Chart Ignore Blank Cells and Zeros

If your line suddenly drops to the bottom of the chart for a month with no data, Excel is treating a blank or empty-looking cell as zero. You can make an Excel chart ignore blank cells with one setting, Hidden and Empty Cells, as long as the cells are truly empty. Cells with formulas that return "" need a different fix: return NA() instead. This guide covers both, plus how to hide real zeros, for anyone building monthly reports or dashboards.

Why does an Excel chart show blank cells as zero?#

There are two different causes, and each needs its own fix:

1. The cell is truly empty, and the chart's empty cells setting is Zero. Change the setting.
2. The cell holds a formula that returns "". It looks empty, but Excel sees text, and line and column charts plot text as zero. The setting doesn't help here, because the cell isn't empty.

To find out which one you have, click the cell. If the formula bar shows nothing, it is empty. If it shows a formula, it is cause 2. The guide to IFERROR returning a blank explains why "" is not a real blank.

Here is the data used in this guide. March has no figure yet, and May is a real zero because the shop was closed:

How to make a chart ignore truly blank cells#

The Hidden and Empty Cells setting controls how a chart draws empty cells. To change it:

1. Click the chart.
2. Right-click it and choose Select Data (or go to Chart Design > Select Data).
3. Click Hidden and Empty Cells in the bottom-left corner.
4. Under Show empty cells as, choose an option and click OK twice.

Gaps is the honest choice when data is missing. Connect data points with line gives a smoother look, but readers may think March had a real value. That option is available for line and scatter charts. These steps are for Excel for Windows and Microsoft 365. On a Mac, look for the same options in the Select Data dialog. For chart basics, see how to make a chart in Excel.

How to make formulas return a gap instead of zero#

When the chart data comes from formulas, return #N/A for missing values instead of "". Charts don't plot #N/A as a point. The NA() function returns it:

=IF(B4="",NA(),B4)

This returns the sales figure when there is one and #N/A when the cell is empty. Put it in a helper column, such as C, and chart column C instead of B.

The same idea fixes IFERROR formulas. If a calculation can fail, replace the blank fallback with NA():

=IFERROR(B2/C2,NA())

How the chart draws #N/A depends on the chart type:

• Line charts skip the point. By default, Excel draws the line straight across it. In Microsoft 365, tick Show #N/A as an empty cell in Hidden and Empty Cells, and #N/A then follows your Gaps setting.
• Column and bar charts show no bar for that category.

The IF function guide and the IFERROR function guide explain both functions, and how to check if a cell is blank covers the different blank tests.

How to hide zero values in a chart#

Real zeros are data, so Excel always plots them. To leave them out, turn them into #N/A in a helper column:

=IF(B6=0,NA(),B6)

For May, this returns #N/A, so the line skips the closed month instead of plunging to the axis. Only do this when a zero really means "no data". If sales were genuinely zero, showing the drop is the honest choice.

Two other ways to deal with zeros:

• Hide zero data labels. Select the data labels, open Format Data Labels > Number, choose Custom and enter 0;-0;;@. The third section is empty, so zero labels disappear but the point stays.
• Filter the rows out. Charts skip hidden rows by default, so filtering out the zero rows removes those categories from a column or bar chart.

How to stop #N/A spoiling the table#

The #N/A values the chart needs can look messy in a table people read. You have two clean options:

1. Keep a helper column for the chart and show the original column to readers. Many dashboards use a hidden chart-data area for this.
2. Hide the error text with conditional formatting. Select the helper column, go to Home > Conditional Formatting > New Rule > Format only cells that contain, choose Errors, and set the font colour to match the background.

Don't wrap the helper column in IFERROR to hide the #N/A, because that brings back the zero problem. The guide to fixing #N/A errors explains when #N/A is a problem and when it is useful, like here.

How to keep averages from counting blanks and zeros#

A chart's average line or title often uses AVERAGE, and zeros drag it down. AVERAGE already skips truly empty cells but counts zeros:

=AVERAGE(B2:B7)

This returns 1,090: 5,450 divided by 5 months, including May's 0. To average only the months with sales, use AVERAGEIF:

=AVERAGEIF(B2:B7,">0")

This returns 1,362.5: 5,450 divided by the 4 open months. Use it when the zeros mean "closed" or "no data", not real results. For more about line charts and averages, see how to make a line chart in Excel.

Common mistakes with blanks in charts#

These are the usual reasons a chart still shows zeros:

1. Changing the setting for "" cells. Hidden and Empty Cells only affects truly empty cells. Use NA().
2. Using IFERROR(x,"") for chart data. Use IFERROR(x,NA()) instead.
3. Typing a space to "clear" a cell. A space is text, and the chart plots it as zero. Select the cell and press Delete.
4. Hiding zeros with a number format only. A format like 0;-0;;@ hides the zero in the cell, but the chart still plots it.
5. Forgetting Show data in hidden rows and columns. If a chart is missing data, hidden rows may be the reason.

Practise it#

The fix for most chart blanks is a short IF or IFERROR formula, so it pays to write them without thinking. Practise IFERROR in a live spreadsheet, then practise the IF function: you type each formula and it is checked instantly. Or try the exercise on this page and average only the months with sales.