Zero values often create difficulties while visualizing data in a chart. They even lead to mistakes in data interpretation. Thus, hiding the zero values is often very important. In this article, we will discuss 5 effective methods to hide zero values in Excel chart.
In this discussion, we will learn 5 effective methods to hide zero values in Excel charts. Firstly, we will use the Filter command to hide the values. Secondly, we will format the data labels to do the task. Then, we will customize the cell format to hide the zero values. Next, we will opt for the Replace command to hide the values. Finally, we will use the MIN function to complete the task. We will use the sample dataset below to illustrate the methods.
1. Using Filter Command to Hide Zero Values in Excel Chart
In this method, we will use the Filter command to hide the zero values. The Filter command allows us to filter out the data that we don’t want to show from our dataset. So that, we can represent our dataset in an efficient and attractive way. We will use this feature of the Filter command here. Follow the outlined steps below to do the task.
Step 1:
- Firstly, select the entire dataset.
- In this case, we will select the cells in the range B4:C9.
- Then, go to the Insert tab.
- Hoover down to the Chart group.
- From the Chart group, select Recommended Charts.
Step 2:
- From the recommended charts, select any chart of your preference.
- In your case, we will select the Clustered Column chart.
- Finally, click OK.
- As a result, the chart will be added to our dataset.
Step 3:
- Then, right-click on the chart.
- From the available options, select Add Data Labels.
Step 4:
- Consequently, you will find that the zero value is present in your chart.
Step 5:
- After that, go to the Home tab.
- Then, navigate to the Editing group.
- From that group, select Sort & Filter.
- Finally, from the drop-down options select the Filter command.
- Consequently, a filter option will be added to the header rows.
Step 6:
- Then, select the filter option of the header row that contains the zero value.
Step 7:
- Afterward, from the filter dialogue box, go to the Number Filters
- Then, uncheck the box beside the Zero option.
- Finally, select OK.
Step 8:
- Consequently, we will see that our chart is free of zero values.
2. Formatting Data Labels to Hide Zero Values in Excel Chart
In this instance, we will format the data labels in the chart to hide the zero values. The data labels give the description of the data present in the particular chart. We will customize those data labels to hide the zero data label. Follow the ensuing steps to do so.
Step 1:
- Firstly, we will select the entire dataset
- In this case, the selected data is in the cell range of B4:C9.
- Then, go to the Insert tab.
- From there, move your cursor to the Recommended Charts tab in the Chart group.
Step 2:
- From the recommended charts, select any chart that suits your needs.
- In this case, we will go with the Clustered Column chart.
- Finally, click OK.
- Consequently, the chart will be included in our dataset.
Step 3:
- Following that, right-click the chart.
- Choose Add Data Labels from the list of options.
Step 4:
- Consequently, we will find a zero value in our chart.
Step 5:
- Right-click the zero value off the chart after that.
- Then, select Format Data Labels from the available options.
- Consequently, the Format Data Labels dialogue box will appear to the right of the screen.
Step 6:
- Then, firstly, select the Label Options.
- Secondly, click on the Number option.
- Thirdly, under the Category box, change the number category to Custom.
- Then, type the following under the Type box
#””
Step 7:
- As a result, you will find that the zero value is vanished from your chart.
3. Customizing Cell Format to Hide Zero Values
In this method, we will alter the cell format to hide the zero values in the chart. We will customize the type of data present in the cells. This will allow us to hide the data in a particular cell. Follow the steps below to do that.
Step 1:
- Firstly, select the dataset containing the zero value.
- Secondly, go to the Home tab.
- From there, go to the Number group.
- Finally, click on the small arrow sign to the right bottom corner of the group.
- Consequently, the Format Cells dialogue box will appear on the screen.
Step 2:
- From the Format Cells dialogue box, firstly, select the Number tab.
- Then, choose the Custom option.
- After that, write down the following under the Type option,
0,0;;;- Finally, click OK.
Step 3:
- Consequently, the zero value will vanish from your dataset.
Step 4:
- After that, select the entire dataset.
- In this case, we will opt for the cells in the range B4:C9 .
- Then, click on the Insert tab.
- Navigate down to the Chart option.
- From the Chart group, click on the Recommended Charts tab.
Step 5:
- Choose the chart of your choice from the recommended charts.
- In this instance, we will select the Cluster Column chart.
- Finally, click OK.
- Consequently, the graph will be included in our dataset.
Step 6:
- After that, right-click on the graph.
- Pick Add Data Labels from the menu of choices.
Step 7:
- As a result, we will observe that our char does not contain any zero value.
4. Applying Replace Command to Hide Zero Values in Excel Chart
In this example, we will use the Replace command to hide zero values in the chart. The Replace command supersedes one particular value in cell with another value. We will use this feature here to replace the zero values with #N/A. Follow the subsequent steps to accomplish the task.
Step 1:
- Firstly, select the dataset containing the zero value.
- In our case, it is in the range of C5:C9.
- Secondly, go to the Home tab.
- From there, visit the Editing group.
- Then, click on the Find & Select tab.
- Finally, from the drop-down options select the Replace command.
- Consequently, the Find and Replace dialogue box will be on the screen.
Step 2:
- In the dialogue box, firstly, write down zero in the Find what box.
- Secondly, write down the following in the Replace with box,
=NA()- Thirdly, click Replace.
- Finally, click on the Find Next tab.
- Continue this until the zero value is replaced by the NA function.
Step 3:
- We will find that the zero value is replaced by #N/A.
Step 4:
- Then, choose the entire dataset.
- In this instance, we’ll select the cells in the B4:C9
- Afterward, select the Insert tab.
- Go down to the Chart group by navigating.
- Select the Recommended Charts option under the Chart group.
Step 5:
- Then, choose the chart of your choice from the recommended charts.
- In this instance, we will choose the Cluster Column chart.
- Finally, click OK.
- Consequently, the graph will find the chart below our dataset.
Step 6:
- Right-click on the chat next.
- From the available options, select Add Data Labels.
Step 7:
- Consequently, we will find that the #N/A value is added in place of the zero value.
5. Use of Excel MIN Function to Hide Zero Values
Finally, in this method, we will resort to the MIN function to hide the zero values. The MIN function search through cell values and returns the smallest value. We will combine the MIN function with the IF function to hide the zero values. Follow the upcoming steps to do that.
Step 1:
- Firstly, select the D5 cell and write down the following formula
=IF(MIN(C5:C5)=0,NA(),MIN(C5:C5))- Then, hit Enter.
Step 2:
- Consequently, we will find that the minimum value is added to the cell.
Step 3:
- Then, move the cursor down to the last data cell.
- As a result, the #N/A value will be added in place of the zero value.
Step 4:
- Now, select the first and the last column of the dataset.
- In our case, the first column is in the range of B4:B9 and that of last column is C4:C9.
- Then, click on the Insert tab.
- Following that, select the Recommended Charts option from the Chart group.
Step 5:
- From the Recommended Charts option, we will select the Custer Column chat.
- You can choose any chart that suits your cause.
- Finally, select OK.
Step 6:
- After that, right-click on the chart representing our dataset.
- From the list available, select Add Data Labels.
Step 7:
- As a result, we will find that the zero value in our chart is replaced by #N/A .
Download Practice Workbook
Conclusion
Zero values cause some real problems in Excel charts. After going through this article, the readers will be able to handle the zero values in efficient ways. This will help them to interpret their dataset in the chart properly.
And how to how to hide blank/ zero values from a graph in excel second axis?
I want to how to hide blank/ zero values from a graph in excel but in a serie that is in the second axis. How can I do that?
Hello Daniel. You can do this by using the Find & Replace feature of Excel. From the Home tab >> under Editing group >> go to Find & Select option >> choose Replace. Then you will see the Find and Replace dialog box. Write 0 in Find what box >> write =NA() in Replace with box >> press in Find Next button >> if the cell has 0 value then press Replace button >> otherwise press Find Next.
In this way change all the 0 values into =NA(). Don’t press Replace All as there may have numeric 0 with the numbers.
Now insert your chart. You will get both the axis have no zero values. Below, I have attached an image where I used two axis and remove zero value from both axis.
If you still face any problem then please provide us your worksheet in Exceldemy Forum.
No.
When I choose the data to plot in the secondary axis, the zeros continue appearing.
See: https://prnt.sc/ZiMHb7ILcOP8
Here you must use IFERROR function to get blank cell for null values. Again, you need to apply formula like if there is no data then the date will be blank also.
You must use Date format as Horizontal axis. Then you will get the chart auto updated up to valued cells. Double-click on Horizontal axis >> from Format Axis window (right side of Excel sheet) >> Axis Options >> Axis Type >> check Date axis.