Tips on how to Cover Zero Values in Excel Charts

A drop to zero in a chart could be abrupt, however typically, that’s what you need. Alternatively, there can be instances once you gained’t wish to draw consideration to a zero. Once you don’t wish to show zero values, you’ve gotten a couple of selections for hiding or in any other case managing these zeros.

On this tutorial, I’ll overview a couple of 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 can even 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 (TechRepublic Premium)

You’ll be able to observe alongside extra intently by downloading our demonstration file. Should you work by the directions utilizing our demonstration workbook file, undo every resolution earlier than you begin the following. 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 information and preliminary charts that we’ll replace all through this text. The pie and single-line charts replicate the information in column B for Vendor 1. The opposite two charts have three information collection: 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 information labels, you will notice 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 with out 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 could 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 all the traces, they’re there. The values are so shut that some traces obscure the others, which is deceptive.

Your outcomes could differ relying on Excel’s default settings and theme colours. Now that the instance information, let’s overview a couple of strategies for suppressing the zero values in our instance charts. Some will work with restricted outcomes, and others gained’t work in any respect.

Eradicating and formatting zero

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

Guide 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 method. By eradicating, I imply merely deleting all zero values from the dataset. Sadly, this easiest strategy 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 curiously. It doesn’t chart the zero values, however as a result of the zeros are gone, the MIN() features within the Minimal column at the moment are all non-zero values and chart accordingly.

Line and multiple-line charts

Neither line chart handles the lacking zeros nicely, however the multiple-line chart is hopeless. The road chart has a spot between the 2 months, which positively appears odd.

The multiple-line chart is misleading. The Vendor 1 collection seems fallacious, however you will notice the markers in the event you click on it. It’s there however obscured by different traces; even doubling its measurement does nothing to enhance its readability.

Should you eliminated zero values within the sheet throughout this part, 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 conceal 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 may need to click on Extra first.
2. Select Superior within the left pane.
3. Within the Show Choices For This Worksheet part, select the appropriate 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 gained’t show them; thus, this methodology has no affect. 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 embrace 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 simply disabled in our earlier step, or shut the file with out saving and reopen it. Take into accout this subsequent formatting strategy has different outcomes. Right here’s the way it works:

1. Choose the information vary B2:D9.
2. Click on the Quantity group’s dialog launcher (Residence tab).
3. Within the ensuing dialog field, select Customized from the Class listing.
4. Within the Kind 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 remains 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 embrace zero values.

As a result of these strategies are really easy to use, strive deleting or formatting the zeros first. Nevertheless, it’s necessary to acknowledge that these strategies won’t possible replace all charts the way you need. You may need to discover a totally 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 easy methods to enter main zeros in Excel

Charting a filtered dataset

You probably have a single information collection, 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 assist this system.

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

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

1. Click on inside the information 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 your complete row. Don’t fear about that, however make sure to take away the filter once you’re finished.

The under instance exhibits the brand new pie chart.

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

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

Sadly, once you take away the filter, the charts replace and show the zero values. Alternatively, in case your chart is a one-time job, 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.

Exchange zeros with NA()

Probably the most everlasting repair for hiding zeros is to exchange literal zero values with the NA() perform utilizing Excel’s Discover and Exchange characteristic. Should you replace the information 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 Exchange characteristic to exchange the zero values within the instance dataset with the NA() perform:

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

7. Click on Exchange All, and Excel will exchange 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 worth, so there’s no distinction. The one curiosity is the multiple-line chart: The zero values, which at the moment are #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 need to use an IF() perform to return the #N/A error utilizing the next syntax:

=IF(method=0,NA(),method)

The MIN() perform returns the minimal worth for every month. The IF() perform 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 trouble you. The reality is, you’re unlikely to want this expression as a result of most features 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 spot 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 through the use of a chart setting to find out easy methods to 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 information labels?

There’s no simple strategy to take away the zero in information labels. If the chart doesn’t chart it, more often than not, it gained’t show the worth in a knowledge label. After working by all these examples, you possibly can see that the problem comes with no ensures. You’ll need to discover a bit to search out the appropriate settings.

If the chart doesn’t show the zero within the chart or the information label however does show the collection 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 check out once more, ensuring to pick solely the one label you wish to take away.

Remaining suggestions

There isn’t a simple 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, take into account sustaining two datasets: one for reporting and one for charting. That is the very best various to toggling backwards and forwards with one dataset.

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

Learn subsequent: Discover among the greatest free alternate options to Microsoft Excel.