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

i have chart 2 types of graphs. 1 shows number of items ordered , second 1 - damaged items rate each order. first graph use bar chart, second dot chart. question how remove empty/zero values in dot chart? "hide empty values" doesn't work because date column filled , used bar chart.

here data:

here chart:

tl;dr (most likely) returning ="" blank cell, need return #n/a.

there couple of ways resolve issue depending on how chart setup.

defaults

first, default, excel not plot cell empty. if create chart (bar, scatter, line, etc.) , delete data point, removed chart, not set 0. depends on hidden , empty setting defaults gaps.

what goes wrong

so if seeing data point blank cell, means 1 of couple things:

• the setting changed chart on how handle hidden , empty cells
• the cell not empty. possible if have formula returns "". show 0 regardless of hidden , empty setting because not empty cell.

how fix

to resolve issue requires 1 of following changes:

• completely delete cell contents empty cell. assumes hidden , empty cells option @ default of gaps.
• have formula return #n/a , chart hide data point regardless of other settings.

• change hidden , empty cells setting gaps (instead of zero). not affect bar chart. serve hide missing entries on dot plot. works if cell empty.