Tips on how to Conceal Zero Values in Excel Charts

Editor’s notice: 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 Office 365. The steps are much like what Susan Harkins describes within the following tutorial.

A drop to zero in a chart may be abrupt, however generally, that’s what you need. Then again, there might be occasions if you gained’t need to draw consideration to a zero. If you don’t need to show zero values, you may have 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 would possibly discover multiple of those strategies useful.

Following alongside

For this demonstration, I’m utilizing Microsoft 365 Desktop on a 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 analysis w/checklist (TechRepublic Premium)

You’ll be able to comply with alongside extra carefully by downloading our demonstration file. For those who work by means of 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 beneath exhibits the info and preliminary charts that we’ll replace all through this text. The pie and single-line charts mirror the info in column B for Vendor 1. The opposite two charts have three knowledge 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 beneath, charts the zero, however you’ll be able to’t see it. For those who activate knowledge labels, you will notice the zero listed. There are seven slices however eight objects within the legend.

Line chart

The beneath instance exhibits the road chart’s default habits, 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 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 likely to be 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’ll be able to’t see all the strains, they’re there. The values are so shut that some strains obscure the others, which is deceptive.

Your outcomes might differ relying on Excel’s default settings and theme colours. Now that the instance knowledge, 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 multiple technique to suppress zero values in a chart, however none work the identical constantly for all charts.

Guide removals of zero

To start with, you would possibly strive eradicating zero values altogether if it’s a literal zero and never the results of a formulation. 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 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 properly, however the multiple-line chart is hopeless. The road chart has a spot between the 2 months, which undoubtedly appears to be like odd.

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

For those who 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 adjustments and reopen it.

Unchecking worksheet show choices

You can even cover zeros by unchecking the worksheet show choice referred to 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 correct sheet from the drop-down menu. (It is 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’ll be able to see them within the Formulation bar. Nevertheless, Excel gained’t show them; thus, this technique has no influence. 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 access to this setting.

We’ve discovered that unchecking this setting presents no benefit. I embrace this step in our tutorial to stop you from losing your time on this method 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 account this subsequent formatting strategy has diversified outcomes. Right here’s the way it works:

1. Choose the info vary B2:D9.
2. Click on the Quantity group’s dialog launcher (Home tab).
3. Within the ensuing dialog field, select Customized from the Class listing.
4. Within the Sort control, 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 embrace 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 may need to discover a completely different resolution for every chart!

For those who 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 leading zeros in Excel

Charting a filtered dataset

You probably have a single knowledge collection, you’ll be able to 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’ll be able to solely chart one vendor at a time. Moreover, Excel for the online doesn’t support this method.

SEE: 10 tips to make your Excel spreadsheets look professional and functional 

Let’s exhibit. 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. For those who’re working with a Desk object, you’ll be able to 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 complete row. Don’t fear about that, however remember to take away the filter if you’re performed.

The beneath instance exhibits the brand new pie chart.

Beneath, you’ll be able to 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 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 info 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 process, filtering presents a fast repair for a pie chart.

For those who 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 switch literal zero values with the NA() operate utilizing Excel’s Discover and Substitute characteristic. For those who replace the info 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 all the time sensible, so let’s use Excel’s Substitute characteristic to switch 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 needed, click on Choices to show extra settings.
6. Test the Match Whole Cell Contents choice.

7. Click on Substitute 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. For those who 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 which may return zero as a substitute of an error worth. In that case, you should use an IF() operate to return the #N/A error utilizing the next syntax:

=IF(formulation=0,NA(),formulation)

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 spot between one worth and one other when the zero worth is lacking. For those who’re working with one chart, you’ll be able to rapidly bypass the guesswork by utilizing 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 straightforward technique 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 a knowledge label. After working by means of all these examples, you’ll be able to see that the difficulty comes with no ensures. You’ll must discover a bit to search out the correct settings.

If the chart doesn’t show the zero within the chart or the info label however does show the collection within the legend, you’ll be able to take away it. Merely choose that merchandise within the legend and press Delete. For those who by chance delete all of them, press Ctrl + Z to undo the delete and check out once more, ensuring to pick solely the one label you need to take away.

Ultimate ideas

There isn’t a simple one-size-fits-all resolution for zeroless charts. For those who show zeros for reporting functions however don’t need to see them in charts and also you chart typically, think about 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 true drawback is the story the info tells. Zero is a legitimate worth and Excel treats it as such.

Learn subsequent: Discover among the best free alternatives to Microsoft Excel.