Table of Contents
To create a chart in Excel that ignores zero values, follow these steps:
1. Select the data range for your chart, including the cells with zero values.
2. Right-click on the chart and select “Select Data” from the drop-down menu.
3. In the “Select Data Source” window, click on the Hidden and Empty Cells” button.
4. Check the box next to “Show data in hidden rows and columns” and click “OK.”
5. The zero values will now be hidden in the chart, but the data will still be included in any calculations or functions.
6. You can also choose to replace the zero values with blank cells by selecting the “Empty Cells as:” option and choosing “Gaps” or “Zero” from the drop-down menu.
By following these steps, you can easily create a chart in Excel that ignores zero values and presents a more accurate representation of your data.
Excel: 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:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I create a chart in Excel that ignores zero values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-excel-that-ignores-zero-values/
stats writer. "How can I create a chart in Excel that ignores zero values?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-excel-that-ignores-zero-values/.
stats writer. "How can I create a chart in Excel that ignores zero values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-excel-that-ignores-zero-values/.
stats writer (2024) 'How can I create a chart in Excel that ignores zero values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-create-a-chart-in-excel-that-ignores-zero-values/.
[1] stats writer, "How can I create a chart in Excel that ignores zero values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I create a chart in Excel that ignores zero values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.