Adnan Masruf
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.
Download the Practice Workbook
5 Handy 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.
Read More: Excel Chart: Ignore Blank Axis Labels (with Easy Steps)
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 gives 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.
Read More: How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)
Similar Readings
- How to Leave Cell Blank If There Is No Data in Excel (5 Ways)
- Use VLOOKUP to Return Blank Instead of 0 (7 Ways)
- How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
- Excel IFERROR Function to Return Blank Instead of 0
- How to Apply VLOOKUP to Return Blank Instead of 0 or NA
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.
Read More: How to Ignore Blank Cells with Formulas in Excel Chart
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.
Read More: How to Ignore Blank Series in Legend of Excel Chart
5. Utilizing 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 .
Read More: How to Hide Chart Series with No Data in Excel (4 Easy Methods)
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.
Related Articles
- How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
- Hide Rows with Zero Values in Excel Using Macro (3 Ways)
- How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- Automatically Hide Rows with Zero Values in Excel
- How to Show Dash Instead of Zero in Excel (4 Easy Methods)
- Exclude Zero Values with Formula in Excel (3 Easy Ways)
- How to Use XLOOKUP to Return Blank Instead of 0
