Labels (for zero valued cells) when set to show series name won't dissappear.

[TassosK]

Hi,

I'm trying to create a graph with labels that will appear only when there are non-zero values in the cells. When I tick label contains Value and I add in the cusotm number format #"" the label dissappears when the cell value is zero. If I tick Label contains "Series Name" then the label always appears. Is there a way, custom format etc. to have the same behavious as when I'm using the cell value in the label?

Thank you all,

Tassos.

PS: O365 on Win 11 Pro 

[#2]

@TassosK 

Unfortunately, there is not a direct way to achieve this using Excel's standard chart labeling features without some form of workaround. To achieve dynamic data labels in Excel that show the series name only when the value is non-zero, you can use a combination of data labels and a helper column. This method works around the limitation of using custom number formats directly with series names.

Here is how you can do it:

1. Create a Helper Column:

• Add a helper column to your data set that combines the series name with the value, but only if the value is non-zero.

2. Set Up Your Data:

• Assume you have your data in columns A (Series Names) and B (Values).
• In column C, create a formula that will result in an empty string if the value in column B is zero, and the series name if the value is non-zero.

For example, if your data starts from row 2:

C2: =IF(B2=0, "", A2)

3. Create Your Chart:

• Select your data and insert the chart as usual.

4. Add Data Labels:

• Click on the chart to select it, then click on the data series to which you want to add labels.
• Right-click and choose Add Data Labels > Add Data Labels.
• Right-click on the data labels and choose Format Data Labels.

5. Set Data Labels to Use Helper Column:

• In the Format Data Labels pane, check the box for Value from Cells.
• Select the range in your helper column (e.g., C2:C10).

This way, the data labels will display the series name when the value is non-zero and will be blank when the value is zero.

Step-by-Step Example:

Assume your data is structured like this:

Series	Values
A	0
B	10
C	0
D	20

 1. Helper Column:

• In cell C2, enter the formula =IF(B2=0, "", A2) and drag it down.
• Your helper column will look like this:

Series	Values	Helper
A	0
B	10	B
C	0
D	20	D

2 . Create Chart:

• Select columns A and B and insert your desired chart type (e.g., Line, Column).

3. Add Data Labels:

• Click the data series in your chart.
• Right-click and choose Add Data Labels.

4. Format Data Labels:

• Right-click on the data labels and choose Format Data Labels.
• In the Format Data Labels pane, check Value from Cells.
• Select the range C2:C5.

This method ensures that the series names only appear when the corresponding value is non-zero, effectively mimicking the behavior you want.

Note:

• This approach is manual but offers a reliable way to control the appearance of data labels based on cell values.
• Make sure to update the helper column formula if you add more data.

The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.