Editor’s word: Within the video, Brandon Vigliarolo walks you thru a few methods to suppress 0 values in Excel charts. For this demo, he makes use of Microsoft Workplace 365. The steps are much like what Susan Harkins describes within the following tutorial.

A drop to zero in a chart could be abrupt, however generally, that’s what you need. Alternatively, there will likely be instances while you received’t wish to draw consideration to a zero. Once you don’t wish to show zero values, you may have just a few selections for hiding or in any other case managing these zeros.

On this tutorial, I’ll assessment just a few strategies for dealing with zero values that provide fast however restricted outcomes with minimal effort. Relying on how a lot charting you do, you may discover a couple of of those strategies useful.

Following alongside

For this demonstration, I’m utilizing Microsoft 365 Desktop on a Home windows 11 64-bit system, however you may as well use earlier variations of Excel. Excel for the online helps most of those methods.

SEE: Google Workspace vs. Microsoft 365: A side-by-side evaluation w/guidelines (roosho Premium)

You may observe alongside extra intently by downloading our demonstration file. Should you work by way of the directions utilizing our demonstration workbook file, undo every resolution earlier than you begin the subsequent. You are able to do this by merely closing the file and reopening it with out saving it.

Exploring the pattern dataset

The instance under exhibits the info and preliminary charts that we’ll replace all through this text. The pie and single-line charts replicate the info in column B for Vendor 1. The opposite two charts have three knowledge sequence: Vendor 1, Vendor 2, and Vendor 3. The Minimal column returns the minimal worth for every month, so April, Ma, and July return zero for the minimal worth. This setup simplifies all of the examples we’ll be reviewing on this information.

Proper now, the charts show zero values by default in every chart kind.

Pie chart

By default, the pie chart, proven under, charts the zero, however you possibly can’t see it. Should you activate knowledge labels, you will note the zero listed. There are seven slices however eight objects within the legend.

Line chart

The under instance exhibits the road chart’s default conduct, which drops the one to zero on the X-axis.

Stacked bar chart

Excel plots 4 stacks for the months and not using a zero worth within the stacked bar chart proven under. The months with a zero show solely two values as a result of the Minimal column additionally returns zero for these months, so the chart is definitely plotting two zeros for every month. Readers may be a bit confused by what they’re seeing.

A number of-line chart

This multiple-line chart under is messy; enlarging it doesn’t enhance its readability. Though you possibly can’t see the entire traces, they’re there. The values are so shut that some traces obscure the others, which is deceptive.

Your outcomes might differ relying on Excel’s default settings and theme colours. Now that you recognize the instance knowledge, let’s assessment just a few strategies for suppressing the zero values in our instance charts. Some will work with restricted outcomes, and others received’t work in any respect.

Eradicating and formatting zero

There’s a couple of solution to suppress zero values in a chart, however none work the identical persistently for all charts.

Handbook removals of zero

To start with, you may strive eradicating zero values altogether if it’s a literal zero and never the results of a components. By eradicating, I imply merely deleting all zero values from the dataset. Sadly, this easiest method doesn’t all the time work as anticipated.

Pie chart

The pie chart doesn’t chart the clean cell, however the legend nonetheless shows the class label, as proven under. Eradicating the zero values from the dataset modified nothing.

Stacked bar chart

The stacked bar responds apparently. It doesn’t chart the zero values, however as a result of the zeros are gone, the MIN() capabilities within the Minimal column are actually all non-zero values and chart accordingly.

Line and multiple-line charts

Neither line chart handles the lacking zeros properly, however the multiple-line chart is hopeless. The road chart has a niche between the 2 months, which positively appears to be like odd.

The multiple-line chart is misleading. The Vendor 1 sequence seems flawed, however you will note the markers in case you click on it. It’s there however obscured by different traces; even doubling its dimension does nothing to enhance its readability.

Should you eliminated zero values within the sheet throughout this section, re-enter them earlier than persevering with to our subsequent instance. Or, shut the demonstration file with out saving your modifications and reopen it.

Unchecking worksheet show choices

You too can disguise zeros by unchecking the worksheet show choice known as Present A Zero In Cells That Have Zero Worth. Right here’s how:

1. Click on the File tab and select Choices. You might need to click on Extra first.
2. Select Superior within the left pane.
3. Within the Show Choices For This Worksheet part, select the precise sheet from the drop-down menu. (This can be a sheet-level property.)
4. Uncheck the Present A Zero In Cells That Have Zero Worth choice.
5. Click on OK.

The zero values nonetheless exist — you possibly can see them within the Formulation bar. Nevertheless, Excel received’t show them; thus, this technique has no impression. The charts deal with the zero values as in the event that they’re nonetheless there as a result of they’re. Excel for the online doesn’t enable entry to this setting.

We’ve discovered that unchecking this setting provides no benefit. I embody this step in our tutorial to stop you from losing your time on this system your self.

Setting a customized format

Earlier than you do that subsequent formatting choice, reset the Superior choice that you just disabled in our earlier step, or shut the file with out saving and reopen it. Consider this subsequent formatting method has assorted outcomes. Right here’s the way it works:

1. Choose the info vary B2:D9.
2. Click on the Quantity group’s dialog launcher (Dwelling tab).
3. Within the ensuing dialog field, select Customized from the Class record.
4. Within the Sort management, enter 0,0;;; and click on OK.

You’ll discover that the outcomes are much like these seen earlier:

• Pie chart: The pie chart doesn’t chart the zero worth, however April continues to be within the legend.
• Stacked bar chart: The stacked bar chart shows solely two stacks for the months with a zero worth.
• Line and multiple-line charts: Each line charts embody zero values.

As a result of these strategies are really easy to use, strive deleting or formatting the zeros first. Nevertheless, it’s vital to acknowledge that these strategies is not going to doubtless replace all charts the way you need. You might need to discover a completely different resolution for every chart!

Should you utilized this format to the demonstration file, delete it earlier than you proceed. Or shut the file with out saving it after which reopen it.

SEE: Right here’s how one can enter main zeros in Excel

Charting a filtered dataset

When you’ve got a single knowledge sequence, you possibly can filter out the zero values and chart the outcomes. Just like the strategies mentioned above, it’s a restricted selection as a result of you possibly can solely chart one vendor at a time. Moreover, Excel for the online doesn’t help this system.

SEE: 10 tricks to make your Excel spreadsheets look skilled and useful 

Let’s reveal. Begin by including a filter to the Vendor 1 column with these steps:

1. Click on inside the info vary.
2. On the Information tab, click on Filter within the Type & Filter group. Should you’re working with a Desk object, you possibly can skip this step as a result of the filters are already there.
3. Click on Vendor 1’s drop-down and uncheck zero.

4. Click on OK to filter the column, which can filter the whole row. Don’t fear about that, however be sure you take away the filter while you’re performed.

The under instance exhibits the brand new pie chart.

Under, you possibly can see the brand new line chart.

Each charts are based mostly on the filtered knowledge in column B. Neither shows the zero worth or the class label on the X-axis. Nevertheless, the road chart has a critical flaw: The road is strong, and April has the identical worth as March. Distributing this chart as-is can be a critical mistake as a result of the info for April is wrong.

Sadly, while you take away the filter, the charts replace and show the zero values. Alternatively, in case your chart is a one-time activity, filtering provides a fast repair for a pie chart.

Should you tried this with the demo file, undo the change, shut it with out saving it, and reopen it.

Substitute zeros with NA()

Essentially the most everlasting repair for hiding zeros is to interchange literal zero values with the NA() operate utilizing Excel’s Discover and Substitute characteristic. Should you replace the info usually, you may even enter NA() for zeros from the get-go, eliminating the issue altogether. To take action manually, enter =NA(). Nevertheless, that’s not all the time sensible, so let’s use Excel’s Substitute characteristic to interchange the zero values within the instance dataset with the NA() operate:

1. Choose the dataset. On this case, it’s B2:D9.
2. Click on Discover & Choose within the Modifying group on the Dwelling tab after which select Substitute from the dropdown, or press Ctrl + H.
3. Enter 0 within the Discover What management.
4. Enter =NA() within the Substitute management.
5. If mandatory, click on Choices to show extra settings.
6. Examine the Match Whole Cell Contents choice.

7. Click on Substitute All, and Excel will change the zero values.
8. Click on OK to dismiss the affirmation message.
9. Click on Shut.

The determine above exhibits the settings and the outcomes. Should you don’t choose the Match Whole Cell Contents choice in step six, Excel will change the values 40, 404, and so forth. The formulation in column E show the error worth as a result of they reference a cell that shows the error message.

Not one of the charts show the #N/A error values, however they nonetheless show the class label within the axis and the legend. The stacked bar chart shows solely two stacks for the months with a #N/A price, so there’s no distinction. The one curiosity is the multiple-line chart: The zero values, which are actually #N/A error values, are clearly seen.

Suppose you’re working with the outcomes of formulation that may return zero as a substitute of an error worth. In that case, you should utilize an IF() operate to return the #N/A error utilizing the next syntax:

=IF(components=0,NA(),components)

The MIN() operate returns the minimal worth for every month. The IF() operate returns #N/A if the result’s zero:

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

The instance’s contrived, however don’t let that hassle you. The reality is, you’re unlikely to want this expression as a result of most capabilities and expressions return the #NA error worth in the event that they attempt to consider one.

Selecting from chart settings to chart zero values

A number of charts present a niche between one worth and one other when the zero worth is lacking. Should you’re working with one chart, you possibly can rapidly bypass the guesswork by utilizing a chart setting to find out how one can chart zero values. Right here’s how:

1. Choose the chart.
2. Click on the contextual Chart Design tab.
3. Within the Information group, click on Choose Information.
4. Within the ensuing dialog, click on the Hidden and Empty Cells button within the bottom-left nook.

5. Select one of many choices.

6. Click on OK twice to return to the chart.

How do you exclude zero in knowledge labels?

There’s no straightforward solution to take away the zero in knowledge labels. If the chart doesn’t chart it, more often than not, it received’t show the worth in a knowledge label. After working by way of all these examples, you possibly can see that the problem comes with no ensures. You’ll must discover a bit to seek out the precise settings.

If the chart doesn’t show the zero within the chart or the info label however does show the sequence within the legend, you possibly can take away it. Merely choose that merchandise within the legend and press Delete. Should you by accident delete all of them, press Ctrl + Z to undo the delete and take a look at once more, ensuring to pick out solely the one label you wish to take away.

Closing suggestions

There isn’t a straightforward one-size-fits-all resolution for zeroless charts. Should you show zeros for reporting functions however don’t wish to see them in charts and also you chart usually, contemplate sustaining two datasets: one for reporting and one for charting. That is one of the best various to toggling backwards and forwards with one dataset.

The actual downside is the story the info tells. Zero is a sound worth and Excel treats it as such.

Learn subsequent: Discover a number of the greatest free alternate options to Microsoft Excel.