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:
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.
Inserting a line chart
Click insert in the Microsoft Excel.
Click on the line chart.
Click on the 2-D line chart.
Right-click on the series with a break (1), and choose "Select Data" (2).
Click on hidden and empty cells.
Check zero, and then press ok.
Press ok. The new chart ignore empty values and show empty cells as zero: