Excel: How to Create a Chart and Ignore Zero Values

Often you may want to create a chart in Excel using a range of data and ignore any cells that are equal to zero.

Fortunately this is easy to do by using the Find and Replace feature in Excel to replace 0 with #N/A.

The following example shows how to use this feature in practice.

Example: Create Chart in Excel and Ignore Zero Values

Suppose we have the following dataset that shows the sales of two products during 10 consecutive years:

Now suppose we would like to create a line chart to visualize the sales of each product during each year.

To do so, highlight the cells in the range B1:C11, then click the Insert tab along the top ribbon, then click the Line button within the Charts group:

The following chart will appear:

Notice that each of the cells that contains a zero is still shown for each line in the graph.

To avoid displaying these zero values in the graph, we can highlight the range B2:C11, then click Ctrl + H to open the Find and Replace Window.

In this window, type 0 in the Find what box and =NA() in the Replace with box.

Then click Replace All and then click Close.

Each value equal to zero in the original dataset will now be replaced with #NA and the zero values will no longer be shown in the chart:

Excel create chart and ignore zero values

Note that Excel considers cells with a value of #N/A to be missing values, which is why it does not display them in a chart.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Create a Chart and Ignore Blank Cells
Excel: How to Create Chart and Ignore Blank Axis Labels
Excel: How to Interpolate Missing Values

Leave a Reply

Your email address will not be published. Required fields are marked *