The way to Disguise Zero Values in Excel Charts

Editor’s be aware: 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 just like what Susan Harkins describes within the following tutorial.

A drop to zero in a chart will be abrupt, however generally, that’s what you need. Then again, there might be instances if you gained’t wish to draw consideration to a zero. While you don’t wish to show zero values, you might have just a few selections for hiding or in any other case managing these zeros.

On this tutorial, I’ll evaluate just a few strategies for dealing with zero values that supply fast however restricted outcomes with minimal effort. Relying on how a lot charting you do, you would possibly 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 strategies.

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

You possibly can comply with alongside extra carefully by downloading our demonstration file. If you happen to work by the directions utilizing our demonstration workbook file, undo every answer 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 beneath reveals 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 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 beneath, charts the zero, however you possibly can’t see it. If you happen to activate knowledge labels, you will note the zero listed. There are seven slices however eight gadgets within the legend.

Line chart

The beneath instance reveals 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 no zero worth within the stacked bar chart proven beneath. 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 is perhaps a bit confused by what they’re seeing.

A number of-line chart

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

Your outcomes could range relying on Excel’s default settings and theme colours. Now that you already know the instance knowledge, let’s evaluate just a few 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 option to suppress zero values in a chart, however none work the identical persistently for all charts.

Guide removals of zero

To start with, you would possibly attempt 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 at all times work as anticipated.

Pie chart

The pie chart doesn’t chart the clean cell, however the legend nonetheless shows the class label, as proven beneath. 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() 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 effectively, however the multiple-line chart is hopeless. The road chart has a niche between the 2 months, which undoubtedly appears to be like odd.

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

If you happen to 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 adjustments and reopen it.

Unchecking worksheet show choices

You can even cover zeros by unchecking the worksheet show possibility 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 suitable 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 possibility.
5. Click on OK.

The zero values nonetheless exist — you possibly can see them within the Components bar. Nevertheless, Excel gained’t show them; thus, this technique 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 permit entry to this setting.

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

Setting a customized format

Earlier than you do this subsequent formatting possibility, reset the Superior possibility that you simply disabled in our earlier step, or shut the file with out saving and reopen it. Remember this subsequent formatting strategy has diversified outcomes. Right here’s the way it works:

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

You’ll discover that the outcomes are just 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, attempt deleting or formatting the zeros first. Nevertheless, it’s vital to acknowledge that these strategies is not going to probably replace all charts the way you need. You might need to discover a totally different answer for every chart!

If you happen to 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

In case you have 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 method.

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

Let’s display. 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. If you happen to’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 is able to filter the whole row. Don’t fear about that, however you’ll want to take away the filter if you’re performed.

The beneath instance reveals the brand new pie chart.

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

Each charts are primarily based 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 stable, and April has the identical worth as March. Distributing this chart as-is can be a critical mistake as a result of the information for April is wrong.

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

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

Change zeros with NA()

Essentially the most everlasting repair for hiding zeros is to switch literal zero values with the NA() perform utilizing Excel’s Discover and Change characteristic. If you happen to replace the information repeatedly, you would possibly even enter NA() for zeros from the get-go, eliminating the issue altogether. To take action manually, enter =NA(). Nevertheless, that’s not at all times sensible, so let’s use Excel’s Change characteristic to switch 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 House tab after which select Change from the dropdown, or press Ctrl + H.
3. Enter 0 within the Discover What management.
4. Enter =NA() within the Change management.
5. If needed, click on Choices to show extra settings.
6. Test the Match Complete Cell Contents possibility.

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

The determine above reveals the settings and the outcomes. If you happen to don’t choose the Match Complete Cell Contents possibility 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 which may return zero as a substitute of an error worth. In that case, you should 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 hassle you. The reality is, you’re unlikely to wish 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. If you happen to’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 knowledge labels?

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

If the chart doesn’t show the zero within the chart or the information 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. If you happen to by chance 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 answer for zeroless charts. If you happen to show zeros for reporting functions however don’t wish to see them in charts and also you chart typically, think about sustaining two datasets: one for reporting and one for charting. That is the most effective different 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 options to Microsoft Excel.