Chart that Ignore N/A! Errors and Blank Cells

Excel is a powerful tool for organizing, analyzing, and visualizing data, and one of its key features is the ability to create charts and graphs. However, when working with data in Excel, it’s common to encounter missing values represented as N/A errors, blank cells, or other types of non-numeric data. If these missing values are included in a chart, they can produce confusing or misleading results, and make it difficult to see patterns or trends in the data.

To overcome this challenge, Excel provides several ways to ignore N/A errors and blank cells when creating charts, so that only the valid data is plotted. In this article, we’ll explain how to create charts in Excel that ignore N/A errors and blank cells, using several different techniques.

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.

See also  Multi Level Pie Chart

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, and choose Select Data.

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

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

line chart ready

After applying these steps, the chart will be created using only the valid data in the data range, and the N/A errors and blank cells will be ignored.

You can download free chart template here