The dataset showcases months and sales amounts of different products .
When you plot that dataset in an Excel chart, zero data labels are displayed.
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.
- Right-click the column to open the Context Menu.
- Select Add Data Labels.
This is the output.
As zero values represent three product values, you need to modify each one of them.
- Right-click any zero value, and select Format Data Labels.
- 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.
The zero data label is hidden.
- To hide the other two zero data labels, follow the same procedure. This is the ouput.
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.
- 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.
This is the output.
This is the 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.
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.
- 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.
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.
- Click Close.
This is the output.
This is the 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
- How to Rotate Data Labels in Excel
- How to Change Font Size of Data Labels in Excel
- How to Move Data Labels In Excel Chart
<< Go Back To Data Labels in Excel | Excel Chart Elements | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello, can you help me with the opposite request? I’d like to hide non-zero labels – is there a way to do that?
Hello Amy,
Yes, it’s possible to hide non-zero data labels and show only the zero values in an Excel chart. You can do it using a helper column.
1. Suppose your original values are in B2:B10.
2. In a new column (say C2), enter this formula:
=IF(B2=0,B2,””)
3. Fill the formula down.
4. Select your chart → click on Data Labels → choose Format Data Labels.
5. Under Label Options, check Value From Cells and select the helper column (C2:C10).
6.Uncheck the default Value option if needed.
Now only the zero values will appear as labels, and all non-zero labels will remain hidden.
Regards,
ExcelDemy