Chart that Ignore N/A! Errors and Blank Cells

We will create a chart that ignores both N/A! and empty values. 

As a data analysis practitioner, I know that data quality is often poor. There are data gaps and data errors in Excel.

If you are faced with the problem of data quality in Excel and you want to create a graph from incomplete data, then the following Excel lesson is for you.

Data preparation

For you to create that, you need data that has the two of them, which looks like this:

ignore chart data table

Note: The result in the earning column is with the help of the IF function, as you can see in the formula bar, and the 0,6875 is the price of the product.

Mark relevant cells in the chart.

ignore graph mark data table

Inserting a line chart

Click insert in the Microsoft Excel.

insert ribbon plot ignore

Click on the line chart.

ribbon line chart

Click on the 2-D line chart.

2d line chart

Right-click on the series with a break (1), and choose "Select Data" (2).

select data ignore chart

Click on hidden and empty cells.

hidden empty cells to ignore

Check zero, and then press ok.

zero ok ignore errors chart

Press ok. The new chart ignore empty values and show empty cells as zero:

line chart ready

You can download free chart template here