Editor's note:In the video Brandon Vigliarolo shows some methods to suppress 0 valuesstanding outgraphics. For this demo, useMicrosoft Office 365. The steps are similar to those described by Susan Harkins in the following tutorial.
Drawing zeros isn't bad, but you don't always want to show them in your Excel charts. A drop to zero on a chart can be abrupt, but sometimes that's what you want.
On the other hand, there will be times when you don't want to draw attention to a zero. If you don't want to display null values, you have some options to hide or manage these null values.
VER:Google Workspace vs. Microsoft 365: A side review with a checklist(Premium Technological Republic)
In this tutorial, I'll introduce some methods for dealing with null values that give you quick but limited results with minimal effort. Depending on how many charts you are creating, you may find more than one of these methods useful. If you follow the instructions using our demo workbook file, undo each solution before moving on to the next. You can do this by simply closing the file and reopening it without saving.
For this demo I useMicrosoft 365-DesktopnumberSistema Windows 1zero de 64 bits, but you can also use older versions of Excel. Excel for the web supports most of these techniques. Once again, you can take a closer look by downloading ourdemo file.
Pair of chickens:
- Explore the sample data set
- Delete zero and format
- Graph of a filtered data set
- Replace zeros with NA()
- Choose between chart settings and zero values
- How do you exclude null values in data labels?
- final tips
Explore the sample data set
Figure Ashows the data and the first graphs that we will update in 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 all return zero for the minimum value. This configuration simplifies all the examples that we will see in this guide:
Currently, charts in each chart type display zero values by default:
- cake table:It registers the zero, but you can't see it. If you enable data labels, zero will appear.
- line diagram:It falls to zero on the X axis.
- Stacked Bar Chart:Returns four stacks for months with no null value. Months with zero only show two "stacks" because the Minimum column also returns zero for those months.
- Mehrliniendiagrama:This is a confusing table; Enlarging does not improve readability. While you may not be able to see all the lines, they are there. The values are so close that some lines obscure the others, which is misleading.
Your results may vary based on Excel's default settings and theme colors. Now that you're familiar with the sample data, let's look at some methods for suppressing zero values in our sample charts. Some will work with limited results and some will not.
Delete zero and format
Manual distances from zero
For starters, you can try removing nulls entirely if it's a literal null and not the result of a formula. Unfortunately, this simpler approach doesn't always work as expected:
- cake table:The pie chart does not render the empty cell, but the legend still shows the category label.
- Stacked Bar Chart:The stacked bar reacts in an interesting way. The zero values are not plotted, but since the zeros are gone, the MIN() functions in the Minimum column are now all non-zero values and are plotted accordingly.
- Line and multi-line charts:None of the line charts handle missing zeros well, as you can see inFigura B, but the multiline graph is useless. The vendor 1 line appears to be incorrect, but if you click on it, you'll see the bullets. It's there, but obscured by other lines; Even doubling the size doesn't improve readability.
If you removed null values from the worksheet during this phase, re-enter them before continuing with our next example.
Disable worksheet display options
You can also hide zeros by turning off the worksheet display option called Show a zero in cells with zero value. To see how:
- Click the File tab and select Options.
- In the left panel, select Advanced.
- In the Show options for this worksheet section, select the correct worksheet from the dropdown menu.
- Uncheck Show a zero in cells with zero value, as shown inFigure CClick OK.
The zero values are still there; you can see them in the formula bar. However, Excel does not display them; therefore, this method has no effect. Charts treat null values as if they are still there because they are. That is, after removing this option, the graphics are the same as before.Figure A. (Excel for the web does not allow access to these settings.)
We found that there is no benefit to removing this setting. I'm including this step in our tutorial so you don't waste time doing this technique yourself.
Define a custom category in the dialog
Before trying the next format option, reset the advanced option that you disabled in the previous step. Note that this next formatting approach has mixed results. Is that how it works:
- Select the data range B2:D9.
- Click the number pool dialog box launcher (Home tab).
- In the resulting dialog box, select Custom from the Category list.
- Type "Type" in the control
0,0;;;(Figure D) and click OK.
You will find that the results are similar to what you have seen before.Figura B:
- cake table:The pie chart does not represent the zero value, but April is still in the legend.
- Stacked Bar Chart:The stacked bar chart only shows two stacks for months with zero value.
- Line and multi-line charts:Both line charts contain zero values.
Since these methods are very easy to use, try removing or formatting the zeros first. However, it's important to realize that these methods probably won't update all graphics the way you want.
Graph of a filtered data set
If you have a single data series, you can filter out the zero values and graph the results. Like the methods discussed above, it's a limited option; You can only assign one provider at a time. Additionally, Excel for the web does not support this technique.
Let's demonstrate. Start adding a filter to the Supplier 1 column with these steps:
- Click on the data area.
- On the Data tab, in the Sort & Filter group, click Filter. If you are working with a table object, you can skip this step as the filters are already in place.
- Click the Provider 1 dropdown menu and uncheck Null (E numbers).
- Click OK to filter the column, which will filter the entire row. Don't worry about it, but be sure to remove the filter when you're done.
Figure Fdisplays both charts based on the filtered data in column B. Does not display the null value or the category label on the x-axis. However, the line chart has a fatal flaw: the line is solid, and April has the same value as March; it would be a fatal flaw to distribute this graphic as-is.
Unfortunately, if you remove the filter, the charts will update to show zero values. On the other hand, if your chart is a one-time job, filtering offers a quick fix for a pie chart.
Replace zeros with NA()
The most permanent solution to hiding zeros is to replace literal zero values with the NA() function using Excel's Find and Replace function. If you update the data regularly, you can even write NA() for zeros from the beginning, which completely eliminates the problem. To do this manually, type
=NA(). However, this is not always practical.
Excel does not plot #N/A error values. You still see the category label on the x-axis, but Excel doesn't plot the actual zero point. Now let's make this work and use Excel's Replace function to replace the null values in the sample data set with the NA() function:
- Select record. In this case it is B2:D9.
- On the Home tab, in the Editing group, click Find & Select and choose Replace from the drop-down list, or press Ctrl + H.
- Digite 0 sin control Find What.
=NA()in the replacement control.
- If necessary, click Options to view more settings.
- Enable the Match all cell contents option, as shown inFigure G.
- Click Replace All and Excel will replace the null values.
- Click OK to close the confirmation message.
- Click Close.
Figure Gabove shows the settings and results. If you don't select Match all cell contents in step 6, Excel changes the values to 40, 404, and so on. The formulas in column E display the error value because they refer to a cell that displays the error message.
None of the plots show the #N/A error values, but they still show the category label on the y-axis in the legend, similar to the results shown inFigura B. The stacked bar chart only shows two stacks for months that have a #N/A value, which we sawFigure A. The only oddity is the multi-line chart displayed onfigure H: Null values, which are now #N/A error values, are clearly visible.
Suppose you are working with the results of formulas that might return a null value instead of an error value, as shown inFigure G. In this case, you can use an IF() function to return the #NV error with the following syntax:
The MIN() function returns the minimum value for each month. The IF() function returns #N/A if the result is zero(Figure I):
The example is made up, but don't let that bother you. Actually, you probably don't need this expression, since most functions and expressions return the #NA error value when trying to evaluate one.
Choose between chart settings and zero values
Several graphs show a gap between one value and another when the null value is absent. When working with a chart, you can quickly eliminate the guesswork by using a chart setting to specify how null values are plotted. To see how:
- Select the chart.
- Click the Contextual Chart Theme tab.
- In the Data group, click Select Data.
- In the dialog that appears, click the "Hidden and Empty Cells" button in the lower left corner (Figure J).
- Choose one of the options (Figure K).
- Click OK twice to return to the chart.
How do you exclude null values in data labels?
There is no easy way to remove the zero in data labels. Most of the time, if the chart doesn't show it, the value won't appear in a data label. After working through all these examples, you can see that the problem has no guarantees. You'll have to do some digging to find the right setting.
If the chart doesn't show a zero in the chart or data label, but shows the series in the legend, you can remove it. Just select that item in the title and hit delete. If you delete everything, press Ctrl+Z to recover and try again. Be sure to select only the tag you want to remove.
There is no single solution to the zero-zero graph problem. If you display zeros for reporting, but you don't want to see them frequently in charts and tables, you should maintain two data sets: one for reports and one for charts. This is the best alternative to change with a registry.
Keep reading:(Technological Republic)
TechRepublic Exclusive Premium Newsletter
Save time with the latest TechRepublic Premium downloads, including original research, customizable IT policy templates, study packed lunches and presentations, IT recruitment tools, ROI calculators, and more. Exclusive for you!
Delivered on Tuesdays and Thursdays