[ad_1]
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.
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.
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.
Jump to:
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.
Exploring the sample dataset
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
By default, the pie chart, shown in Figure B, 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.
Figure B
Line chart
Figure C shows the line chart’s default behavior, which drops the one to zero on the X-axis.
Figure C
Stacked bar chart
Excel plots four stacks for the months without a zero value in the stacked bar chart shown in Figure D. 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.
Figure D
Multiple-line chart
This multiple-line chart in Figure E 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.
Figure E
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
There’s more than one way to suppress zero values in a chart, but none of them 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 you can see in Figure F. Removing the zero values from the dataset changed nothing.
Figure F
Stacked bar chart
The stacked bar responds interestingly. It doesn’t chart the zero values, as you can see in Figures G, but because the zeros are gone, the MIN() functions in the Minimum column are now all non-zero values and chart accordingly.
Figure G
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 (Figure H).
Figure H
The multiple-line chart is deceptive. It appears that the Vendor 1 series is wrong, but if you click it, you will see the markers (Figure I). It’s there but obscured by other lines; even doubling its size does nothing to improve its readability.
Figure I
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.
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. You might have to click More first.
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. (This is a sheet-level property.)
4. Uncheck the Show A Zero In Cells That Have Zero Value option, as shown in Figure J.
Figure J
5. Click OK.
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.
What we’ve found is that unchecking 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 format
Before you try this next formatting option, reset the Advanced option that you disabled in our previous step, or close the file without saving and reopen it. 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 K) and click OK.
Figure K
You’ll notice that the results are similar to those seen earlier:
- 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. You might have to find a different solution for each chart!
If you applied this format to the demonstration file, delete it before you continue. Or close the file without saving and then reopen it.
SEE: Here’s how to enter leading zeros in Excel
Charting a filtered dataset
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 because 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 L).
Figure L
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 M shows the new pie chart.
Figure M
Figure N shows the new line chart.
Figure N
Both charts are 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 because the data for April is incorrect.
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.
If you tried this with the demo file, be sure to undo the change or close the file without saving and then reopen it.
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, so let’s use Excel’s Replace feature to replace the zero values in the example dataset with the NA() function:
1. Select the dataset. 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 O.
Figure O
7. Click Replace All, and Excel will replace the zero values.
8. Click OK to dismiss the confirmation message.
9. Click Close.
Figure O 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. The stacked bar chart displays only two stacks for the months that have a #N/A value, so there’s no difference there. The one curiosity is the multiple-line chart shown in Figure P: The zero values, which are now #N/A error values, are clearly visible.
Figure P
Suppose you’re working with the results of formulas that might return zero instead of an error value as shown in Figure O. 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 Q):
=IF(MIN(B2:D2)=0,NA(),MIN(B2:D2))
Figure Q
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 R).
Figure R
5. Choose one of the options (Figure S).
Figure S
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 accidentally 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 zeroless 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 datasets: One for reporting and one for charting. This is the best alternative to toggling back and forth with one dataset.
The real problem is the story the data tells. Zero is a valid value and Excel treats it as such.
Read next: Explore some of the best free alternatives to Microsoft Excel.
[ad_2]
Source link