How to Hide Zero Values in Excel Chart (5 Easy Ways)

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.

Sample Data


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.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 3:

  • Then, right-click on the chart.
  • From the available options, select Add Data Labels.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 4:

  • Consequently, you will find that the zero value is present in your chart.

5 Handy Methods to Hide Zero Values in Excel 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.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 6:

  • Then, select the filter option of the header row that contains the zero value.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 7:

  • Afterward, from the filter dialogue box, go to the Number Filters
  • Then, uncheck the box beside the Zero option.
  • Finally, select OK.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 8:

  • Consequently, we will see that our chart is free of zero values.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 3:

  • Following that, right-click the chart.
  • Choose Add Data Labels from the list of options.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 4:

  • Consequently, we will find a zero value in our chart.

5 Handy Methods to Hide Zero Values in Excel 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.

5 Handy Methods to Hide Zero Values in Excel Chart

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
#””

5 Handy Methods to Hide Zero Values in Excel Chart

Step 7:

  • As a result, you will find that the zero value is vanished from your chart.

5 Handy Methods to Hide Zero Values in Excel Chart

Read More: How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)


Similar Readings


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.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 3:

  • Consequently, the zero value will vanish from your dataset.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 6:

  • After that, right-click on the graph.
  • Pick Add Data Labels from the menu of choices.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 7:

  • As a result, we will observe that our char does not contain any zero value.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

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.

5 Handy Methods to Hide Zero Values in Excel Chart

Step 3:

  • We will find that the zero value is replaced by #N/A.

Sample Data

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.

Sample Data

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.

Sample Data

Step 6:

  • Right-click on the chat next.
  • From the available options, select Add Data Labels.

Sample Data

Step 7:

  • Consequently, we will find that the #N/A value is added in place of the zero value.

Sample Data

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.

Sample Data

Step 2:

  • Consequently, we will find that the minimum value is added to the cell.

Sample Data

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.

Sample Data

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.

Sample Data

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.

Sample Data

Step 6:

  • After that, right-click on the chart representing our dataset.
  • From the list available, select Add Data Labels.

Sample Data

Step 7:

  • As a result, we will find that the zero value in our chart is replaced by #N/A .

Sample Data

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

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo