How to Hide Zero Data Labels in an Excel Chart – 4 Methods

The dataset showcases months and sales amounts of different products .

When you plot that dataset in an Excel chart, zero data labels are displayed.

Hide Zero Data Labels in Excel Chart

 


Method 1 – Formatting the Data Labels

The dataset contains several zeros.

Steps

  • Select B4:E12.

  • Go to the Insert tab.
  • In Charts, select Recommended Charts.

  • In the Insert Chart dialog box, select Clustered Column.
  • Click OK.

  • After changing the chart style, this is the output.

Hide Zero Data Labels in Excel Chart

Hide Zero Data Labels in Excel Chart

This is the output.

Hide Zero Data Labels in Excel Chart

As zero values represent three product values, you need to modify each one of them.

Hide Zero Data Labels in Excel Chart

  • In the Format data labels dialog box, select Label Options and choose Number.
  • In Category, select Custom.
  • In Format Code, enter the following
#””
  • Click Add.

Hide Zero Data Labels in Excel Chart

The zero data label is hidden.

  • To hide the other two zero data labels, follow the same procedure. This is the ouput.

Hide Zero Data Labels in Excel Chart

Read More: How to Edit Data Labels in Excel


Method 2 – Using the Data Filter

 

Steps

  • Select B4:C12.

  • Go to the Insert tab.
  • In Charts, select Recommended Charts.

  • In the Insert Chart dialog box, select Line.
  • Click OK.

This is the output.

Hide Zero Data Labels in Excel Chart

  • To filter the dataset and hide zero data labels, select B4:C12.
  • Go to the Data tab.
  • Select Filter in Sort & Filter.

You will see the filter drop-down option.

  • Click the filter drop-down.
  • Uncheck zero (0).
  • Click OK.

Hide Zero Data Labels in Excel Chart

This is the output.

Hide Zero Data Labels in Excel Chart

This is the Excel chart.

Hide Zero Data Labels in Excel Chart

Read More: How to Remove Zero Data Labels in Excel Graph


Method 3 – Customizing the Cell Format

Steps

  • Select C5:E12.

  • Go to the Home tab.
  • Select Format Cells Dialog launcher in Number.

  • In the Format Cells dialog box, select Custom in Category.
  • Enter the following in Type.
0,0;;;
  • Click OK.

Hide Zero Data Labels in Excel Chart

All zero values are hidden.

  • To plot that dataset in an Excel chart, select B4:E12.

  • Go to the Insert tab and in Charts, select Recommended Charts.

  • In the Insert Chart dialog box, select Clustered Column.
  • Click OK.

  • After changing the chart style, this is the output.

Hide Zero Data Labels in Excel Chart

  • If you add  data labels, you won’t see zeros.
  • Right-click the column to open the Context Menu.
  • Select Add Data Labels.

This is the output.

Hide Zero Data Labels in Excel Chart

Read More: How to Use Conditional Formatting in Data Labels in Excel


Method 4 – Applying the NA Function

Steps

  • Replace the zero values with the NA function.
  • Select C5:E12.

  • Go to the Home tab.
  • In Editing, select Find & Select.

  • Select Replace in Find & Select.

  • In the Find and Replace dialog box, set Find what as 0.
  • Enter the following formula in Replace with.
=NA()
  • Select Find Next.
  • Click Replace.
  • As there are several zero values, you need to find and replace them one by one and replace.

Hide Zero Data Labels in Excel Chart

  • Click Close.

This is the output.

This is the Excel chart.

Hide Zero Data Labels in Excel Chart

READ MORE: [Fixed:] Excel Chart Is Not Showing All Data Labels


Things to Remember

  • While using the NA function in Find & Select, you may face problems if you apply Replace All because there are zeros in non-zero numbers like $4030. Excel can’t differentiate them and will replace them also.

Download Practice Workbook

Download the practice workbook below.

 


Related Articles


<< Go Back To Data Labels in ExcelExcel Chart Elements | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo