Editor’s note: In the video, Brandon Vigliarolo walks you through a number of methods for suppressing 0 values in Excel charts. For this demo, he utilizes Microsoft Office 365. The steps are similar to what Susan Harkins explains in the following tutorial.
A drop to absolutely no in a chart can be abrupt, however sometimes, that’s what you want. On the other hand, there will be times when you will not wish to accentuate a zero. When you don’t want to show no values, you have a few choices for how to hide or otherwise handle those nos.
In this tutorial, I’ll examine a couple of techniques for managing zero worths that offer quick but limited outcomes with minimal effort. Depending on just how much charting you do, you might find more than among these methods handy.
Jump to:
Following along
For this presentation, I’m utilizing Microsoft 365 Desktop on a Windows 11 64-bit system, but you can likewise use earlier variations of Excel. Excel for the web supports the majority of these strategies.
SEE: Google Office vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
You can follow along more closely by downloading our presentation file. If you resolve the instructions using our presentation workbook file, reverse each solution before you start the next. You can do this by just closing the file and reopening it without conserving.
Checking out the sample dataset
Figure A reveals the data and preliminary charts that we’ll update throughout this article. The pie and single-line charts show the information in column B for Supplier 1. The other 2 charts have 3 data series: Supplier 1, Vendor 2 and Vendor 3. The Minimum column returns the minimum worth for each month, so April, May and July return zero for the minimum worth. This setup streamlines all the examples we’ll be evaluating in this guide.
Figure A
We’ll utilize 4 chart types to evaluate the fundamental habits that come with charting no
. Right now, the charts
show zero values by default in each chart type. Pie chart By default, the pie chart, displayed in Figure B, charts the no, but you can’t see it. If you switch on information labels, you will see the no
listed. There are seven pieces but 
eight items in the legend. Figure B The pie chart plots
absolutely no by default. Line chart Figure C reveals the line chart’s default habits, which drops the one to absolutely no on the X-axis. Figure C The line chart plots absolutely no by default, which can be a bit abrupt.
Stacked bar chart
Excel plots four stacks for the months without an absolutely no worth in the stacked bar chart shown in Figure D. The months with a zero screen just 2 worths since the Minimum column also returns absolutely no for those months, so the chart is really outlining two zeros for each month. Readers might be a bit puzzled by what they’re seeing.
Figure D
The stacked bar chart plots no worths. Multiple-line chart This multiple-line chart in Figure E is messy; enlarging it does not improve its readability. Although you can’t see all of the lines, they’re there. The worths are so close that some lines obscure the others, which is deceptive. Figure E No values in a multiple-line chart can contribute to the chaos. Your results might differ, depending upon Excel’s default settings and theme colors
. Now that you recognize with the example information, let’s evaluate a couple of techniques for reducing the absolutely no values in our example charts. Some will deal with limited outcomes and others will not work at all. Getting rid of and formatting absolutely no There’s more than one way to reduce zero worths in a chart,
but none of them work the very same, consistently, for all charts. Manual removals of no To start with, you might try removing no worths completely if it’s a literal zero and
not the outcome of a formula.
By eliminating, I imply simply deleting all zero worths from the dataset. Regrettably, this easiest method doesn’t always work as anticipated. Pie chart The pie chart does not chart the blank cell, but the legend still shows the classification label, as you can see
in Figure F. Removing the absolutely no values from the dataset changed nothing. Figure F Getting rid of absolutely no worths
will not assist the pie chart
. Stacked bar chart The stacked bar responds surprisingly. It doesn’t chart the zero worths, as you can see in Figures G, but since the absolutely nos are gone, the minutes() functions in the Minimum column are now all non-zero worths and chart accordingly.
Figure G
Getting rid of the absolutely no worths changes the results of solutions which can have unintentional results. Line and multiple-line charts Neither line chart handles the missing zeros well, however the multiple-line chart is helpless.
The line chart 
has a gap between the two months, which certainly looks odd (Figure H ).
Figure H Eliminating no worths leaves a gap, which most likely will not be what you desire. The multiple-line chart is deceptive. It appears that the Supplier 1 series is wrong, but if you click it, you will see the markers(Figure I ).
It exists however obscured by 
other lines; even doubling its size not does anything to enhance its readability.
Figure I This multiple-line chart appears to hide data. If you got rid of absolutely no values in the sheet during this stage, re-enter them before continuing to our next example.
Or, close the presentation file without
saving your changes and reopen. Unchecking worksheet screen choices You can also conceal nos by unchecking the worksheet screen alternative called Program An Absolutely no In Cells That Have Absolutely No Worth. Here’s how: 1. Click the File tab and select Choices. You may need to click More first.
2. Pick Advanced in the left pane.
3. In the Display Alternatives For This Worksheet section, choose the right sheet from the drop-down menu. (This is a sheet-level property.)
4. Uncheck the Show A No In Cells That Have No Value alternative, as displayed in Figure J.
Figure J
This choice doesn’t alter anything. 5. Click OK. The no worths still exist– you can see them in the Solution bar. Nevertheless, Excel will not display them; thus, this technique has no impact. The charts treat the zero worths as if they’re still there due to the fact that they are. Excel for the web doesn’t allow access to this setting
. What we have actually discovered is that unchecking this setting uses no advantage. I include this action in our tutorial to keep you from losing your time on this method yourself.
Setting a custom-made format
Prior to you try this next format choice, reset the Advanced alternative that you handicapped in our previous action, or close the file without saving and resume it. Keep in mind this next format approach has differed results. Here’s how it works:
1. Select the information range B2: D9.
2. Click the Number group’s dialog launcher (Home tab).
3. In the resulting dialog box, pick Custom from the Category list.
4. In the Type control, enter 0,0;;; (Figure K) and click OK.
Figure K
This format likewise conceals absolutely no values, however the worths are
Due to the fact that these approaches are so easy to use, attempt erasing the nos or formatting them initially. Nevertheless, it is very important to recognize that these methods aren’t most likely to update all charts the method you want. You might need to discover a various option for each chart!
If you applied this format to the presentation file, delete it prior to you continue. Or close the file without saving and after that reopen it.
SEE: Here’s how to go into leading nos 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 techniques discussed above, it’s a limited choice since you can only chart one supplier at a time. Furthermore, Excel for the web doesn’t support this method.
Let’s demonstrate. Start by including a filter to the Supplier 1 column with these actions:
1. Click inside the data variety.
2. On the Data tab, click Filter in the Sort & Filter group. If you’re dealing with a Table object, you can skip this action since the filters are currently there.
3. Click Supplier 1’s drop-down and uncheck zero (Figure L).
Figure L
Uncheck absolutely no to eliminate absolutely no values from the filtered set. 4. Click OK to filter the column, which will filter the entire row.
Don’t fret about that, but make certain to remove the filter when you’re done. Figure 
M shows the new pie chart. Figure M This filtering hack is a simple one-time repair for the pie chart. Figure N reveals the new line chart. Figure N< img src="https://www.techrepublic.com/wp-content/uploads/2018/07/Excel0Chart_F2-770x535.jpg" alt =" The line chart isn't an excellent candidate for this service." width ="770"height= "535"/ > The line chart isn’t an excellent candidate for this service. Both charts are based on the filtered information in column B. Neither displays the no value or the classification label on the X-axis. However, the line chart has a severe flaw: The line is strong, and April is the very same worth as March– dispersing this chart as-is would be a serious mistake because the information for April is incorrect.
Unfortunately, when you get rid of the filter, the charts update and display the zero worths. On the other hand, if your chart is a one-time job, filtering provides a fast fix for a pie chart.
If you tried this with the demonstration file, make certain to reverse the change or close the file without conserving and then resume it.
Change absolutely nos with NA()
The most permanent fix for concealing zeros is to replace literal no values with the NA() function using Excel’s Discover and Replace feature. If you upgrade the data frequently, you might even enter NA() for absolutely nos from the get-go, which will eliminate the problem altogether. To do so manually, go into =NA(). Nevertheless, that’s not always useful, so let’s utilize Excel’s Replace function to replace the no worths 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 after that choose Change from the dropdown, or press Ctrl + H.
3. Go into 0 in the Discover What control.
4. Enter =NA() in the Replace control.
5. If required, click Options to show more settings.
6. Check the Match Entire Cell Contents alternative, as displayed in Figure O.
Figure O
Make certain to check this alternative.
7. Click Replace All, and Excel will change the absolutely no worths.
8. Click OK to dismiss the confirmation message. 9. Click Close. Figure O above shows the settings and the results. If you do not select the Match Entire Cell Contents option in action six, Excel will change the worths 40, 404 and so on. The formulas in column E display the error worth due to the fact that they’re referencing a cell that displays the mistake message.
None of the charts display the #N/ An error values, however they still display the classification label in the axis and the legend. The stacked bar chart shows only 2 stacks for the months that have a #N/ A worth, so there’s no distinction there. The one interest is the multiple-line chart shown in Figure P: The zero values, which are now #N/ A mistake values, are plainly noticeable.
Figure P
The multiple-line chart shows clear breaks. Suppose you’re working with the outcomes of formulas that might return no rather of a mistake worth as displayed in Figure O. Because case, you can utilize an IF() function to return the #N/ An 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 outcome is absolutely no (Figure Q):
=IF(MIN(B2: D2)=0, NA(), MINUTES(B2: D2))
Figure Q
Use an IF( )expression to return NA mistake value if the formula doesn’t do this by itself. The example’s contrived, but don’t let that trouble you. The truth is, you’re not likely to need this expression due to the fact that a lot of functions and expressions return the #NA mistake value if they attempt to assess one.
Choosing from chart settings to chart no 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 rapidly bypass the guesswork by utilizing a chart setting to figure out how to chart zero values. Here’s how:
1. Select the chart.
2. Click the contextual Chart Style tab.
3. In the Information 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
Modification the absolutely no behavior for a single chart.
5. Select
among the options (Figure S).
Figure S< img src ="https://www.techrepublic.com/wp-content/uploads/2018/07/Excel0Chart_K.jpg"alt =" Choose an option."width= "417" height ="255"/ > Pick an option.
6. Click OK twice to go back to the chart. How do you omit absolutely no in data labels
? There’s no simple method to eliminate the zero in information labels. If the chart doesn’t chart it, the majority of the time it will not display the value in an information label. After overcoming all these examples, you can see that the problem features no guarantees. You’ll need to check out a bit to find the ideal settings.
If the chart does not show the zero in the chart or the information label, but does display the series in the legend, you can eliminate it. Just select that item in the legend and press Erase. If you inadvertently erase all of them, press Ctrl + Z to undo the delete and try once again, making sure to select only the one label you want to eliminate.
Final pointers
There isn’t an easy one-size-fits-all service for the problem of zeroless charts. If you’re showing zeros for reporting functions, however you don’t wish to see them in charts and you’re charting typically, think about keeping two datasets: One for reporting and one for charting. This is the very best alternative to toggling back and forth with one dataset.
The genuine problem is the story the information informs. No is a valid worth and Excel treats it as such.
Read next: Check out a few of the finest totally free alternatives to Microsoft Excel.
