Excel chart - dealing with null values ​​you don't want to chart (2023)

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:

Figure A

Excel chart - dealing with null values ​​you don't want to chart (1)
Excel chart - dealing with null values ​​you don't want to chart (2)
Excel chart - dealing with null values ​​you don't want to chart (3)
Excel chart - dealing with null values ​​you don't want to chart (4)
Excel chart - dealing with null values ​​you don't want to chart (5)

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.

(Video) How to Hide Zero Values on an Excel Chart

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.

Figura B

Excel chart - dealing with null values ​​you don't want to chart (6)
Excel chart - dealing with null values ​​you don't want to chart (7)
Excel chart - dealing with null values ​​you don't want to chart (8)
Excel chart - dealing with null values ​​you don't want to chart (9)

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:

  1. Click the File tab and select Options.
  2. In the left panel, select Advanced.
  3. In the Show options for this worksheet section, select the correct worksheet from the dropdown menu.
  4. Uncheck Show a zero in cells with zero value, as shown inFigure CClick OK.

Figure C

Excel chart - dealing with null values ​​you don't want to chart (10)

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:

  1. Select the data range B2:D9.
  2. Click the number pool dialog box launcher (Home tab).
  3. In the resulting dialog box, select Custom from the Category list.
  4. Type "Type" in the control0,0;;;(Figure D) and click OK.

Figure D

Excel chart - dealing with null values ​​you don't want to chart (11)

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.

(Video) How to remove blank/ zero values from a graph in excel

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:

  1. Click on the data area.
  2. 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.
  3. Click the Provider 1 dropdown menu and uncheck Null (E numbers).

E numbers

Excel chart - dealing with null values ​​you don't want to chart (12)
  1. 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.

Figure F

Excel chart - dealing with null values ​​you don't want to chart (13)
Excel chart - dealing with null values ​​you don't want to chart (14)

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:

  1. Select record. In this case it is B2:D9.
  2. On the Home tab, in the Editing group, click Find & Select and choose Replace from the drop-down list, or press Ctrl + H.
  3. Digite 0 sin control Find What.
  4. Forbidden=NA()in the replacement control.
  5. If necessary, click Options to view more settings.
  6. Enable the Match all cell contents option, as shown inFigure G.

Figure G

Excel chart - dealing with null values ​​you don't want to chart (15)
  1. Click Replace All and Excel will replace the null values.
  2. Click OK to close the confirmation message.
  3. 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.

(Video) Excel Graph - Ignore Blank Cells | Hide #N/A Values | Stop Line Graph Dropping to Zero

figure H

Excel chart - dealing with null values ​​you don't want to chart (16)

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:

=SI(formula=0,NA(),formula)

The MIN() function returns the minimum value for each month. The IF() function returns #N/A if the result is zero(Figure I):

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

figure me

Excel chart - dealing with null values ​​you don't want to chart (17)

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:

  1. Select the chart.
  2. Click the Contextual Chart Theme tab.
  3. In the Data group, click Select Data.
  4. In the dialog that appears, click the "Hidden and Empty Cells" button in the lower left corner (Figure J).

Figure J

Excel chart - dealing with null values ​​you don't want to chart (18)
  1. Choose one of the options (Figure K).

Figure K

Excel chart - dealing with null values ​​you don't want to chart (19)
  1. 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.

(Video) Optimized Excel Line Charts: Prevent drop to zero & dynamic Legend positioning

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.

final tips

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)

Excel chart - dealing with null values ​​you don't want to chart (20)

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

sign up today

Videos

1. Skip Dates in Excel Charts (ignore gaps in Excel horizontal chart axis)
(Leila Gharani)
2. How-to Create a Dynamic Excel Chart that Only Displays Non-Blank Values
(Excel Dashboard Templates)
3. Hide Zero Values in Chart Labels in MS Excel - GRAPHS WITHOUT ZERO LABELS
(Wisdom By Data)
4. How to Skip Blank cell or cell having zero value while creating chart in MS excel_English
(Smart Office Templates)
5. Excel Charts - three methods for filtering Chart Data
(Chris Menard)
6. Only Label Specific Dates in Excel Chart Axis - Reduce clutter and look more professional!
(MyOnlineTrainingHub)
Top Articles
Latest Posts
Article information

Author: Frankie Dare

Last Updated: 04/24/2023

Views: 5898

Rating: 4.2 / 5 (73 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Frankie Dare

Birthday: 2000-01-27

Address: Suite 313 45115 Caridad Freeway, Port Barabaraville, MS 66713

Phone: +3769542039359

Job: Sales Manager

Hobby: Baton twirling, Stand-up comedy, Leather crafting, Rugby, tabletop games, Jigsaw puzzles, Air sports

Introduction: My name is Frankie Dare, I am a funny, beautiful, proud, fair, pleasant, cheerful, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.