How can I hide 0% value in data labels in an Excel Bar Chart

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I would like to hide data labels on a chart that have 0% as a value. I can get it working when the value is a number and not a percentage. I could delete the 0% but the data is going to change on a daily basis.

I am doing a if statement to calculate which column to put the data into.Data is shown below
enter image description here

I have 2 bars one green and one red. When the value is above 8% the red bar shows and when the value is below 8%. At one time i can only show one bar. I would like to hide the 0% as the bar isnt being show.

enter image description here

asked Sep 3 '14 at 10:22

Inkey

1211210

You must have two columns for the data to show like this. In your source data, create a statement that if the result is zero to replace it with "#N/A"

– wbeard52
Sep 3 '14 at 13:48

add a comment |

I am doing a if statement to calculate which column to put the data into.Data is shown below
enter image description here

enter image description here

asked Sep 3 '14 at 10:22

Inkey

1211210

You must have two columns for the data to show like this. In your source data, create a statement that if the result is zero to replace it with "#N/A"

– wbeard52
Sep 3 '14 at 13:48

add a comment |

I am doing a if statement to calculate which column to put the data into.Data is shown below
enter image description here

enter image description here

asked Sep 3 '14 at 10:22

Inkey

1211210

I am doing a if statement to calculate which column to put the data into.Data is shown below
enter image description here

enter image description here

microsoft-excel microsoft-excel-2010 charts

asked Sep 3 '14 at 10:22

Inkey

1211210

asked Sep 3 '14 at 10:22

Inkey

1211210

asked Sep 3 '14 at 10:22

Inkey

1211210

asked Sep 3 '14 at 10:22

Inkey

1211210

asked Sep 3 '14 at 10:22

Inkey

1211210

You must have two columns for the data to show like this. In your source data, create a statement that if the result is zero to replace it with "#N/A"

– wbeard52
Sep 3 '14 at 13:48

add a comment |

You must have two columns for the data to show like this. In your source data, create a statement that if the result is zero to replace it with "#N/A"

– wbeard52
Sep 3 '14 at 13:48

You must have two columns for the data to show like this. In your source data, create a statement that if the result is zero to replace it with "#N/A"

– wbeard52
Sep 3 '14 at 13:48

add a comment |

3 Answers
3

active

oldest

votes

The quick and easy way to accomplish this is to custom format your data label.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; and click Add.

Close out of your dialog box and your 0% labels should be gone.

This works because Excel looks to your custom format to see how to format Postive;Negative;0 values. By leaving a blank after the final ;, Excel formats any 0 value as a blank.

answered Sep 3 '14 at 12:20

dav

7,75532243

1

Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to).

– Dan Henderson
Feb 16 '16 at 19:58

add a comment |

If using Office 2013 or later, type the format as mentioned above by Dav and Erik combined, i.e.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; "" and click Add.

After this, select the newly added format in the Type box.

Close out of your dialog box and your 0% labels should be gone.

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

answered Jul 24 '18 at 9:40

Ever Neel

111

add a comment |

You can also set the =NA() value in your empty Cells.

answered Sep 3 '14 at 12:23

robert

19614

1

The chart shows #N/A

– Inkey
Sep 3 '14 at 12:38

I was thinking more for the data-values than the labels.

– robert
Sep 3 '14 at 12:39

add a comment |

protected by Community♦ Feb 1 at 23:00

Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

The quick and easy way to accomplish this is to custom format your data label.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; and click Add.

Close out of your dialog box and your 0% labels should be gone.

This works because Excel looks to your custom format to see how to format Postive;Negative;0 values. By leaving a blank after the final ;, Excel formats any 0 value as a blank.

answered Sep 3 '14 at 12:20

dav

7,75532243

1

Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to).

– Dan Henderson
Feb 16 '16 at 19:58

add a comment |

The quick and easy way to accomplish this is to custom format your data label.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; and click Add.

Close out of your dialog box and your 0% labels should be gone.

This works because Excel looks to your custom format to see how to format Postive;Negative;0 values. By leaving a blank after the final ;, Excel formats any 0 value as a blank.

answered Sep 3 '14 at 12:20

dav

7,75532243

1

Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to).

– Dan Henderson
Feb 16 '16 at 19:58

add a comment |

The quick and easy way to accomplish this is to custom format your data label.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; and click Add.

Close out of your dialog box and your 0% labels should be gone.

This works because Excel looks to your custom format to see how to format Postive;Negative;0 values. By leaving a blank after the final ;, Excel formats any 0 value as a blank.

answered Sep 3 '14 at 12:20

dav

7,75532243

The quick and easy way to accomplish this is to custom format your data label.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; and click Add.

Close out of your dialog box and your 0% labels should be gone.

This works because Excel looks to your custom format to see how to format Postive;Negative;0 values. By leaving a blank after the final ;, Excel formats any 0 value as a blank.

answered Sep 3 '14 at 12:20

dav

7,75532243

answered Sep 3 '14 at 12:20

dav

7,75532243

answered Sep 3 '14 at 12:20

dav

7,75532243

answered Sep 3 '14 at 12:20

dav

7,75532243

1

Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to).

– Dan Henderson
Feb 16 '16 at 19:58

add a comment |

1

Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to).

– Dan Henderson
Feb 16 '16 at 19:58

Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to).

– Dan Henderson
Feb 16 '16 at 19:58

add a comment |

If using Office 2013 or later, type the format as mentioned above by Dav and Erik combined, i.e.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; "" and click Add.

After this, select the newly added format in the Type box.

Close out of your dialog box and your 0% labels should be gone.

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

answered Jul 24 '18 at 9:40

Ever Neel

111

add a comment |

If using Office 2013 or later, type the format as mentioned above by Dav and Erik combined, i.e.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; "" and click Add.

After this, select the newly added format in the Type box.

Close out of your dialog box and your 0% labels should be gone.

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

answered Jul 24 '18 at 9:40

Ever Neel

111

add a comment |

If using Office 2013 or later, type the format as mentioned above by Dav and Erik combined, i.e.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; "" and click Add.

After this, select the newly added format in the Type box.

Close out of your dialog box and your 0% labels should be gone.

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

answered Jul 24 '18 at 9:40

Ever Neel

111

If using Office 2013 or later, type the format as mentioned above by Dav and Erik combined, i.e.

Select a data label.

Right click and select Format Data Labels

Choose the Number category in the Format Data Labels dialog box.

Select Custom in the Category box.

In the format code box, enter 0%;-0%; "" and click Add.

After this, select the newly added format in the Type box.

Close out of your dialog box and your 0% labels should be gone.

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

answered Jul 24 '18 at 9:40

Ever Neel

111

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

edited Jul 24 '18 at 13:14

robinCTS

4,03741527

answered Jul 24 '18 at 9:40

Ever Neel

111

answered Jul 24 '18 at 9:40

Ever Neel

111

answered Jul 24 '18 at 9:40

Ever Neel

111

add a comment |

You can also set the =NA() value in your empty Cells.

answered Sep 3 '14 at 12:23

robert

19614

1

The chart shows #N/A

– Inkey
Sep 3 '14 at 12:38

I was thinking more for the data-values than the labels.

– robert
Sep 3 '14 at 12:39

add a comment |

You can also set the =NA() value in your empty Cells.

answered Sep 3 '14 at 12:23

robert

19614

1

The chart shows #N/A

– Inkey
Sep 3 '14 at 12:38

I was thinking more for the data-values than the labels.

– robert
Sep 3 '14 at 12:39

add a comment |

You can also set the =NA() value in your empty Cells.

answered Sep 3 '14 at 12:23

robert

19614

You can also set the =NA() value in your empty Cells.

answered Sep 3 '14 at 12:23

robert

19614

answered Sep 3 '14 at 12:23

robert

19614

answered Sep 3 '14 at 12:23

robert

19614

answered Sep 3 '14 at 12:23

robert

19614

1

The chart shows #N/A

– Inkey
Sep 3 '14 at 12:38

I was thinking more for the data-values than the labels.

– robert
Sep 3 '14 at 12:39

add a comment |

1

The chart shows #N/A

– Inkey
Sep 3 '14 at 12:38

I was thinking more for the data-values than the labels.

– robert
Sep 3 '14 at 12:39

The chart shows #N/A

– Inkey
Sep 3 '14 at 12:38

I was thinking more for the data-values than the labels.

– robert
Sep 3 '14 at 12:39

add a comment |

protected by Community♦ Feb 1 at 23:00

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Csdrhrt