How to Remove Zero Data Labels in Excel Graph (3 Easy Ways)

If you are looking for ways to remove zero data labels in an Excel graph, this article is for you. So, let’s start with the main article to know detailed procedures for removing zero data labels.

Download Workbook


3 Ways to Remove Zero Data Labels in Excel Graph

Here, we have the following dataset containing records of marks for 3 subjects of some students. Among the students, two of them got 0 in two different subjects. After plotting them as Line Chart, Column Chart, and Pie Chart we can see that the Line Chart is conspicuously showing zero data labels. Seemingly, the other two forms are avoiding the zero data labels. So, in the following sections, we will discuss more of the ways to remove the zero data labels from the Line Chart.

how to remove zero data labels in excel graph

We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


Method-1: Using NA Function to Remove Zero Data Labels in Excel Graph

In this method, we are going to replace zero in Physics for Emily and in Maths for Sheldon with #N/A to avoid zero data labels.

NA function

Read More: How to Add Data Labels in Excel (2 Handy Ways)


Case-01: Usage of NA Function with Find and Replace Option

Here, we will replace the normal zero values with #N/A using the NA function.

Steps:
Firstly, we have to find the zero values in the dataset and then replace them with #N/A.
➤ Select the range and then go to the Home Tab >> Editing Group >> Find & Select Dropdown >> Replace Option.

how to remove zero data labels in excel graph

After that, the Find and Replace dialog box will open up.
➤ Type the following in the first two boxes.
Find what → 0
Replace with → =NA()
➤ Select the Replace All option.

NA function

Afterward, the Microsoft Excel message box will appear which informs about 2 replacements.
➤ Click on OK.

NA function

After that, the zero values will be replaced by #N/A and the graphs are not showing zero data labels here anymore.

how to remove zero data labels in excel graph


Case-02: Usage of NA Function for Formula

Here, we will show the way of replacing zero with #N/A in a formula. In the Minimum column, we have used the MIN function to have the minimum value between the marks of Maths and Chemistry and the graph is showing the minimum values with zero data label.

how to remove zero data labels in excel graph

Steps:
➤ After removing the previous formula, enter the following formula in cell E4.

=IF(MIN(C4:D4)=0,NA(),MIN(C4:D4))

If the minimum value is zero then the IF function will return #N/A otherwise the minimum value.

➤ Drag down the Fill Handle tool.

NA function

In this way, the Zero value for Sheldon has been superseded with #N/A. And the graph has also removed zero data labels.

NA function


Similar Readings


Method-2: Using Filter Option to Remove Zero Data Labels in Excel Graph

This method can only be used if your graph is plotted with single column values like we have plotted the following graph for the students with their Maths number only.

how to remove zero data labels in excel graph

Steps:
➤ Select the dataset and then go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Filter Option.

Filter option

Then, the filter dropdown option will appear on the column headers.
➤ Click on the dropdown symbol in the Maths column.

Filter option

Uncheck the box with the number 0 and then press OK.

how to remove zero data labels in excel graph

Finally, we have removed the marks 0 in the row for Sheldon and our graph has also eliminated this 0 data label.

Filter option

Read More: How to Add Two Data Labels in Excel Chart (with Easy Steps)


Method-3: Using Custom Number Format to Format Data Labels of Chart

Without data labels, column charts may ignore zero data labels, but when we activate the data label option of the chart we can see the zero data labels for the Physics and Maths series also in the chart. To remove this label you can follow this section.

how to remove zero data labels in excel graph

Steps:
➤ Select the data labels of the Physics series and then Right-click here.
➤ Choose the Format Data Labels option.

custom number format

On the right side, the Format Data Labels wizard will appear.
➤ Go to the Label Options and then scroll down to the following options.

how to remove zero data labels in excel graph

➤ Under the Number option, select the Category as Custom, in the Format Code box type #”” and click on the Add button.

custom number format

In this way, we have removed the zero data label from the Physics series.

custom number format

Similarly, we will remove the zero data label from the selected Maths series as well.

how to remove zero data labels in excel graph

Finally, we have removed all of the zero data labels from the following chart.

custom number format

Read More: How to Format Data Labels in Excel (with Easy Steps)


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

practice


Conclusion

In this article, we tried to remove zero data labels in an Excel graph. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo