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

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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.

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


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


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


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.

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


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.


Related Articles


<< Go Back to Customize Excel Charts | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

5 Comments
  1. And how to how to hide blank/ zero values from a graph in excel second axis?

  2. 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?

    • Reply Avatar photo
      Musiha Mahfuza Mukta Jun 21, 2023 at 3:22 PM

      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.

  3. No.
    When I choose the data to plot in the secondary axis, the zeros continue appearing.
    See: https://prnt.sc/ZiMHb7ILcOP8

    • Reply Avatar photo
      Musiha Mahfuza Mukta Jun 21, 2023 at 6:02 PM

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo