How to remove empty values in excel chart when dates are not empty?

Question

I have a chart with two types of graphs. One shows number of items ordered and the second one - damaged items rate for each order. For the first graph I use bar chart, for the second dot chart. The question is how to remove empty/zero values in dot chart? "Hide empty values" doesn't work because date column is filled and used for bar chart.

Here is the data:

Here is the chart:

Solution

TL;DR (most likely) you are returning a ="" for a blank cell, you need to return #N/A.

There are a couple of ways to resolve this issue depending on how you chart is setup.

Defaults

First, by default, Excel will not plot a cell that is truly empty. If I just create a chart (bar, scatter, line, etc.) and delete a data point, it will be removed from the chart, not set to 0. This depends on the Hidden and Empty setting which defaults to Gaps.

What goes wrong

So if you are seeing a data point for a blank cell, it means one of a couple things:

• The setting was changed for the chart on how to handle Hidden and empty cells
• The cell is not truly empty. This is possible if you have a formula that returns "". This will show as a 0 regardless of the Hidden and empty setting because it is not an empty cell.

How to fix

To resolve this issue then requires one of the following changes:

• Completely delete the cell contents so that it is truly an empty cell. This assumes that the Hidden and empty cells option is at the default of Gaps.
• Have your formula return an #N/A and the chart will hide the data point regardless of any other settings.

• Change the Hidden and empty cells setting to Gaps (instead of Zero). This will not affect the bar chart. It will only serve to hide the missing entries on the dot plot. This only works if the cell is truly empty.