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:
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
