Excel chart: Handling zero values that you don’t want to chart

Editor’s note: In the video, Brandon Vigliarolo walks you through a couple of ways for suppressing 0 values in Excel charts. For this demo, he uses Microsoft Office 365. The steps are similar to what Susan Harkins describes in the following tutorial.

Charting zeros isn’t wrong, but you won’t always want to display them in your Excel charts. A drop to zero in a chart can be abrupt, but sometimes, that’s what you want.

On the other hand, there will be times when you won’t want to draw attention to a zero. When you don’t want to display zero values, you have a few choices for how to hide or otherwise manage those zeros.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

In this tutorial, I’ll review a few methods for handling zero values that offer quick but limited results with minimal effort. Depending on how much charting you do, you might find more than one of these methods helpful. If you work through the instructions with our demonstration workbook file, undo each solution before you start the next. You can do this by simply closing the file and reopening it without saving.

For this demonstration, I’m using Microsoft 365 Desktop on a Windows 1zero 64-bit system, but you can also use earlier versions of Excel. Excel for the web supports most of these techniques. Again, you can follow along more closely by downloading our demonstration file.

Jump to:

• Exploring the sample data set
• Removing and formatting zero
• Charting a filtered data set
• Replace zeros with NA()
• Choosing from chart settings to chart zero values
• How do you exclude zero in data labels?
• Final tips

Exploring the sample data set

Figure A shows the data and initial charts that we’ll update throughout this article. The pie and single-line charts reflect the data in column B for Vendor 1. The other two charts have three data series: Vendor 1, Vendor 2 and Vendor 3. The Minimum column returns the minimum value for each month, so April, May and July return zero for the minimum value. This setup simplifies all the examples we’ll be reviewing in this guide:

Figure A

Right now, the charts display zero values by default in each chart type:

• Pie chart: It charts the zero, but you can’t see it. If you turn on data labels, you will see the zero listed.
• Line chart: Drops to zero on the X-axis.
• Stacked bar chart: Shows four stacks for the months without a zero value. The months with a zero display only two “stacks” because the Minimum column also returns zero for those months.
• Multiple-line chart: This is a messy chart; enlarging it doesn’t improve its readability. Although you can’t see all of the lines, they’re there. The values are so close that some lines obscure the others, which is misleading.

Your results may vary, depending on Excel’s default settings and theme colors. Now that you’re familiar with the example data, let’s review a few methods for suppressing the zero values in our example charts. Some will work with limited results and others won’t work at all.

Removing and formatting zero

Manual removals of zero

To begin, you might try removing zero values altogether if it’s a literal zero and not the result of a formula. Unfortunately, this simplest approach doesn’t always work as expected:

• Pie chart: The pie chart doesn’t chart the blank cell, but the legend still displays the category label.
• Stacked bar chart: The stacked bar responds interestingly. It doesn’t chart the zero values, but because the zeros are gone, the MIN() functions in the Minimum column are now all non-zero values and chart accordingly.
• Line and multiple-line charts: Neither line chart handles the missing zeros well, as you can see in Figure B, but the multiple-line chart is hopeless. It appears that the vendor 1 series is wrong, but if you click it, you will see the markers. It’s there but obscured by other lines; even doubling its size does nothing to improve its readability.

Figure B

If you removed zero values in the sheet during this phase, re-enter them before continuing to our next example.

Unchecking worksheet display options

You can also hide zeros by unchecking the worksheet display option called Show A Zero In Cells That Have Zero Value. Here’s how:

1. Click the File tab and choose Options.
2. Choose Advanced in the left pane.
3. In the Display Options For This Worksheet section, choose the right sheet from the drop-down menu.
4. Uncheck the Show A Zero In Cells That Have Zero Value option, as shown in Figure C. Click OK.

Figure C

The zero values still exist — you can see them in the Formula bar. However, Excel won’t display them; thus, this method has no impact. The charts treat the zero values as if they’re still there because they are. In other words, after removing this option, the charts are the same as they were in Figure A. (Excel for the web doesn’t allow access to this setting).

What we’ve found is that removing this setting offers no advantage. I include this step in our tutorial to keep you from wasting your time on this technique yourself.

Setting a custom category in the dialog box

Before you try this next formatting option, reset the Advanced option that you disabled in our previous step. Keep in mind this next formatting approach has varied results. Here’s how it works:

1. Select the data range B2:D9.
2. Click the Number group’s dialog launcher (Home tab).
3. In the resulting dialog box, choose Custom from the Category list.
4. In the Type control, enter 0,0;;; (Figure D) and click OK.

Figure D

You’ll notice that the results are similar to those seen earlier in Figure B:

• Pie chart: The pie chart doesn’t chart the zero value, but April is still in the legend.
• Stacked bar chart: The stacked bar chart displays only two stacks for the months with a zero value.
• Line and multiple-line charts: Both line charts include the zero values.

Because these methods are so easy to apply, try deleting the zeros or formatting them first. However, it’s important to recognize that these methods aren’t likely to update all charts the way you want.

Charting a filtered data set

If you have a single data series, you can filter out the zero values and chart the results. Like the methods discussed above, it’s a limited choice; you can only chart one vendor at a time. Additionally, Excel for the web doesn’t support this technique.

Let’s demonstrate. Start by adding a filter to the Vendor 1 column with these steps:

1. Click inside the data range.
2. On the Data tab, click Filter in the Sort & Filter group. If you’re working with a Table object, you can skip this step because the filters are already there.
3. Click Vendor 1’s drop-down and uncheck zero (Figure E).

Figure E

4. Click OK to filter the column, which will filter the entire row. Don’t worry about that, but be sure to remove the filter when you’re done.

Figure F shows the two charts based on the filtered data in column B. Neither displays the zero value or the category label on the X-axis. However, the line chart has a serious flaw: The line is solid, and April is the same value as March — distributing this chart as-is would be a serious mistake.

Figure F

Unfortunately, when you remove the filter, the charts update and display the zero values. On the other hand, if your chart is a one-time task, filtering offers a quick fix for a pie chart.

Replace zeros with NA()

The most permanent fix for hiding zeros is to replace literal zero values with the NA() function using Excel’s Find and Replace feature. If you update the data regularly, you might even enter NA() for zeros from the get-go, which will eliminate the problem altogether. To do so manually, enter =NA(). However, that’s not always practical.

Excel won’t chart #N/A error values. You’ll still see the category label on the X-axis, but Excel won’t chart the actual zero. Now, let’s make this work and use Excel’s Replace feature to replace the zero values in the example data set with the NA() function:

1. Select the data set. In this case, it’s B2:D9.
2. Click Find & Select in the Editing group on the Home tab and then choose Replace from the dropdown, or press Ctrl + H.
3. Enter 0 in the Find What control.
4. Enter =NA() in the Replace control.
5. If necessary, click Options to display more settings.
6. Check the Match Entire Cell Contents option, as shown in Figure G.

Figure G

7. Click Replace All, and Excel will replace the zero values.
8. Click OK to dismiss the confirmation message.
9. Click Close.

Figure G above shows the settings and the results. If you don’t select the Match Entire Cell Contents option in step six, Excel will change the values 40, 404 and so on. The formulas in column E display the error value because they’re referencing a cell that displays the error message.

None of the charts display the #N/A error values, but they still display the category label in the axis and the legend, similar to the results shown in Figure B. The stacked bar chart displays only two stacks for the months that have a #N/A value, which is what we saw in Figure A. The one curiosity is the multiple-line chart shown in Figure H: The zero values, which are now #N/A error values, are clearly visible.

Figure H

Suppose you’re working with the results of formulas that might return zero instead of an error value as shown in Figure G. In that case, you can use an IF() function to return the #N/A error using the following syntax:

=IF(formula=0,NA(),formula)

The MIN() function returns the minimum value for each month. The IF() function returns #N/A if the result is zero (Figure I):

=IF(MIN(B2:D2)=0,NA(),MIN(B2:D2))

Figure I

The example’s contrived, but don’t let that bother you. The truth is, you’re unlikely to need this expression because most functions and expressions return the #NA error value if they try to evaluate one.

Choosing from chart settings to chart zero values

Several charts show a gap between one value and another when the zero value is missing. If you’re working with one chart, you can quickly bypass the guesswork by using a chart setting to determine how to chart zero values. Here’s how:

1. Select the chart.
2. Click the contextual Chart Design tab.
3. In the Data group, click Select Data.
4. In the resulting dialog, click the Hidden and Empty Cells button in the bottom-left corner (Figure J).

Figure J

5. Choose one of the options (Figure K).

Figure K

6. Click OK twice to return to the chart.

How do you exclude zero in data labels?

There’s no easy way to remove the zero in data labels. If the chart doesn’t chart it, most of the time it won’t display the value in a data label. After working through all these examples, you can see that the issue comes with no guarantees. You’ll have to explore a bit to find the right settings.

If the chart doesn’t display the zero in the chart or the data label but does display the series in the legend, you can remove it. Simply select that item in the legend and press Delete. If you delete all of them, press Ctrl + Z to undo the delete and try again, making sure to select only the one label you want to remove.

Final tips

There isn’t an easy one-size-fits-all solution for the problem of zero-less charts. If you’re displaying zeros for reporting purposes, but you don’t want to see them in charts and you’re charting often, consider maintaining two data sets: One for reporting and one for charting. This is the best alternative to toggling back and forth with one data set.

Read next: The 8 best alternatives to Microsoft Project (Free & Paid) (TechRepublic)

Subscribe to the TechRepublic Premium Exclusives Newsletter

Save time with the latest TechRepublic Premium downloads, including original research, customizable IT policy templates, ready-made lunch-and-learn presentations, IT hiring tools, ROI calculators, and more. Exclusively for you!

Delivered Tuesdays and Thursdays

Sign up today