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 hiding or otherwise managing those zeros. 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. Following along For this demonstration, I’m using Microsoft 365 Desktop on a Windows 11 64-bit system, but you can also use earlier versions of Excel. Excel for the web supports most of these techniques. SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium) You can follow along more closely by downloading our demonstration file. If you work through the instructions using 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 it. Exploring the sample dataset The example below 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, Ma, and July return zero for the minimum value. This setup simplifies all the examples we’ll be reviewing in this guide. We’ll use four chart types to review the inherent behaviors that come with charting zero. Right now, the charts display zero values by default in each chart type. Pie chart By default, the pie chart, shown below, charts the zero, but you can’t see it. If you turn on data labels, you will see the zero listed. There are seven slices but eight items in the legend. The pie chart plots zero by default. Line chart The below example shows the line chart’s default behavior, which drops the one to zero on the X-axis. The line chart plots zero by default, which can be a bit abrupt. Stacked bar chart Excel plots four stacks for the months without a zero value in the stacked bar chart shown below. The months with a zero display only two values because the Minimum column also returns zero for those months, so the chart is actually plotting two zeros for each month. Readers might be a bit confused by what they’re seeing. The stacked bar chart plots zero values. Multiple-line chart This multiple-line chart below is messy; 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. Zero values in a multiple-line chart can add to the chaos. Your results may vary depending on Excel’s default settings and theme colors. Now that you know 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 There’s more than one way to suppress zero values in a chart, but none work the same consistently for all charts. Manual removals of zero To begin with, you might try removing zero values altogether if it’s a literal zero and not the result of a formula. By removing, I mean simply deleting all zero values from the dataset. 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, as shown below. Removing the zero values from the dataset changed nothing. Removing zero values won’t help the pie chart. 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. Removing the zero values changes the formula’s results, which can have unintended results. Line and multiple-line charts Neither line chart handles the missing zeros well, but the multiple-line chart is hopeless. The line chart has a gap between the two months, which definitely looks odd. Removing zero values leaves a gap, which probably won’t be what you want. The multiple-line chart is deceptive. The Vendor 1 series appears wrong, but you will see the markers if you click it. It’s there but obscured by other lines; even doubling its size does nothing to improve its readability. This multiple-line chart seems to hide data. If you removed zero values in the sheet during this phase, re-enter them before continuing to our next example. Or, close the demonstration file without saving your changes and reopen it. More about Software 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: Click the File tab and choose Options. You might have to click More first. Choose Advanced in the left pane. In the Display Options For This Worksheet section, choose the right sheet from the drop-down menu. (This is a sheet-level property.) Uncheck the Show A Zero In Cells That Have Zero Value option. Click OK. This option doesn’t change anything. 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. Excel for the web doesn’t allow access to this setting. We’ve found that unchecking this setting offers no advantage. I include this step in our tutorial to prevent you from wasting your time on this technique yourself. Setting a custom format Before you try this next formatting option, reset the Advanced option that you disabled in our previous