Durjoy Paul
Sometimes you have some zero values in our dataset. When you plot that dataset in a chart, it will show zero data labels in the chart which is quite irritating to see. In Microsoft Excel, we can hide those data labels quite easily. In this article, we will show how to hide zero data labels in an Excel chart effectively. I hope you find this article informative and gain more information regarding Excel chart labels.
Download Practice Workbook
Download the practice workbook below.
4 Easy Ways to Hide Zero Data Labels in Excel Chart
To hide zero data labels in an Excel chart, we have found four different methods through which we can have a clear idea about this topic. All of these are fairly easy to understand. We basically utilize the Excel commands to hide zero data labels in an Excel chart. To show all the methods perfectly, we take a dataset that includes months and several product sales amounts.
When we plot that dataset in an Excel chart, we will see the following chart where you have several zero data labels in an Excel chart.
In the following four methods, we have to hide zero data labels in an Excel chart. So, follow the methods carefully.
1. Formatting Data Labels
Our first method is based on formatting data labels. In this method, we do formatting in the Excel chart and hide the zero data labels. To show the method properly, we take a dataset that contains several zeros.
To understand this method properly, you have to follow the steps properly.
Steps
- First, select the range of cells B4 to E12.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select the Recommended Charts option.
- Then, it will open up the Insert Chart dialog box.
- From there, select the Clustered Column chart.
- Finally, click on OK.
- After changing the chart style, we will get the following chart. See the screenshot.
- Then, right-click on the column, and it will open up the Context Menu.
- From there, select Add Data Labels.
- It will give the following results where we have three zero data labels in the Excel chart. See the screenshot.
- As the zero values represent three product values of a certain month, we have to modify them one after another.
- Then, right-click on any zero value, and the Context Menu will appear.
- From there, select Format Data Labels.
- As a result, the Format data labels dialog box will appear.
- Then, in the Label Options tab, go to the Number section.
- After that, from the Category drop-down option, select Custom.
- Then, in the Format Code section, write the following
#””- After that, click Add.
- As a result, one zero data label is hidden from the Excel chart. See the screenshot.
- To hide the other two zero data labels, do the same process in the Format data labels dialog box. See the screenshot.
Read More: How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
2. Using Data Filter
Our second is based on using the data filter. In this method, we will filter the dataset and then, uncheck the zero value from the dataset, it will eventually hide zero data labels in an Excel chart, To show this method, we take the following dataset.
Steps
- First, select the range of cells B4 to C12.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select the Recommended Charts option.
- Then, it will open up the Insert Chart dialog box.
- From there, select the Line chart.
- Finally, click on OK.
- After changing the chart style, we will get the following chart. See the screenshot.
- Now, we need to filter our dataset to hide the zero data labels in an Excel chart.
- First, select the range of cells B4 to C12.
- Then, go to the Data tab in the ribbon.
- After that, select Filter from the Sort & Filter group.
- It will filter our dataset. See the screenshot and you will see the filter drop-down option.
- Click on the filter drop-down option.
- Then, uncheck the zero (0).
- After that, click on OK.
- It will hide the zero value from the dataset. See the screenshot.
- As a result, it will hide the zero value from the Excel chart also. See the screenshot.
Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)
Similar Readings
- Excel IFERROR Function to Return Blank Instead of 0
- How to Apply VLOOKUP to Return Blank Instead of 0 or NA
- Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- How to Hide Chart Series with No Data in Excel (4 Easy Methods)
3. Customizing Cell Format
Our thor method is based on customizing the cell format. In this method, we will customize the cell format. It will eventually, hide the zero values from the dataset and do the same for the Excel chart. To understand the method properly, you need to follow the steps.
Steps
- First, We need to hide to number from the dataset which will eventually hide the zero data labels from the Excel chart.
- Select the range of cells C5 to E12.
- Then, go to the Home tab in the ribbon.
- After that, select the Format Cells Dialog launcher from the Number group which is at the bottom right corner.
- It will open up the Format Cells dialog box.
- Then, select Custom from the Category section.
- After that, write the following in the Type section.
0,0;;;- Finally, click on OK.
- As a result, you will see all the zero values are hidden from the dataset. See the screenshot.
- Now, we need to plot that dataset in an Excel chart.
- So, select the range of cells B4 to E12.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select the Recommended Charts option.
- Then, it will open up the Insert Chart dialog box.
- From there, select the Clustered Column chart.
- Finally, click on OK.
- After changing the chart style, we will get the following chart. See the screenshot.
- Then, if you add the data labels, you won’t get those zero data labels because all of them are hidden from the dataset.
- Right-click on the column, and it will open the Context Menu.
- Then, select Add Data Labels from there.
- It will give you the desired solution where all the zero data labels are hidden. See the screenshot.
Read More: How to Hide Rows with Zero Values in Excel Using Macro (3 Ways)
4. Applying NA Function
Another good way to hide zero data labels in an Excel chart is to apply the NA function in place of zero in the dataset. To understand the method clearly, follow the steps
Steps
- First, we need to replace the zero values with the NA function.
- So, select the range of cells C5 to E12.
- Then, go to the Home tab in the ribbon.
- From the Editing section, select Find & Select drop-down option.
- After that, select Replace from the Find & Select drop-down option.
- Then, the Find and Replace dialog box will occur.
- Set the Find what box as 0.
- Then, write the following formula in the Replace with section.
=NA()- Select Find Next to find the zero value in the dataset.
- After that, click on Replace.
- As we have several zero values, so we need to find them one by one and replace them.
- Then, click on Close to apply the change.
- It will make the changes in your dataset accordingly. See the screenshot.
- Now, if you see the Excel chart, you will see there are no zero data labels in the Excel chart.
Things to Remember
- While using the NA function by the Find & Select option, you may face some problems if you apply the Replace All Because we have zero in our non-zero numbers like $4030. Here, Excel can’t differentiate the difference so, they will replace this also. That’s why you need to go one by one by clicking the Find Next option and then Replace.
Conclusion
To hide zero data labels in an Excel chart, we have shown four different methods through which we can easily do the work. All of these methods are user-friendly. We utilize several Excel commands to hide zero data labels in an Excel chart. I think you enjoy this article thoroughly. We tried to cover all possible areas to hide zero data labels in an Excel chart. If you have further questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.
