Hiding zero values in Excel chart or diagram, legend and labeling

excel excel-formula excel-charts
J.Justus picture J.Justus · Jun 12, 2017 · Viewed 26k times · Source

As you can see in the attached image, my diagram accesses the table on the left. Though the values are changing and it appears that sometimes a value is 0. In this case the entire row of the table neither should be shown in the diagram nor in the legend or labeling.

How can I implement such a diagram? Example: Table with diagram (In the example, the gas costs should not be displayed)

Answer

Koby Douek picture Koby Douek · Jun 12, 2017
  1. Right click at one of the data labels, and select Format Data Labels from the context menu
  2. In the Format Data Labels dialog, Click Number in left pane, then select Custom from the Category list box, and type #"" into the Format Code text box, and click Add button to add it to Type list box.
  3. Click Close button to close the dialog. Then you can see all zero data labels are hidden.

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More