How can you remove data labels from a chart?

https://www.techrepublic.com/wp-content/uploads/2018/07/20180719_SupressZeros_Brandon.mp4

Editor’s note: In the video, Brandon Vigliarolo walks you through a couple of ways for suppressing 0 values in Excel charts. For this demo, he uses Microsoft Office 365. The steps are similar to what Susan Harkins describes in the following tutorial.

Charting 0s isn’t wrong, but you won’t always want to display them in your Excel charts. Your data and the chart’s purpose will guide you in this decision. When you don’t want to display these values, you have a few choices, and some work better than others. We’ll review a few that offer quick but limited results with minimum effort.

LEARN MORE: Office 365 Consumer pricing and features

About the example data

Figure A shows the data and initial charts that we’ll use throughout this article. Right now, the charts display 0 values. The downloadable .xlsx and .xls files contain the data and charts. Your results might vary, depending on your version of Excel and your chart defaults. If you work through the instructions with the example workbook, be sure to undo each solution before you start the next. Simply close the file and reopen without saving.

Figure A

The pie and single line charts reflect the data in column B for Vendor 1. The other two charts have three data series: Vendor 1, Vendor 2, and Vendor 3. This setup simplifies all the examples. Now that you’re familiar with the example data, let’s review a few methods for suppressing the 0 values in our example charts. Some will work with limited results, and some won’t work at all — but ahead and try them.

Easiest but limited

You might try removing the 0 value altogether if it’s a literal 0 and not the result of a formula. Unfortunately, this simplest approach doesn’t always work as expected. The stacked bar responds well to this solution. The pie chart doesn’t chart the missing 0s, but the legend still displays the category label. Neither line chart handles the missing 0s well, as you can see in Figure B (if you removed the 0 values in the sheet, re-enter them before you continue).

Figure B

You can hide the 0s by unchecking the worksheet display option called Show a zero in cells that have zero value. Here’s how:

1. Click the File tab and choose Options. In Excel 2007, click the Office button and then click Excel options. In Excel 2003, choose Options from the Tools menu and skip to #3.
2. Choose Advanced in the left pane.
3. In the Display options for this worksheet section, choose the appropriate sheet from the drop-down menu.
4. Uncheck the Show a zero in cells that have zero value option (as shown in Figure C).
   Figure C
   
5. Click OK.

The 0 values still exist; you can see them in the Format bar, but Excel won’t display them. This method has little to no impact. For the most part, the chart treats the 0 values as if they’re still there, because they are.

You might also try using the following format that hides 0s:

1. Select the data range.
2. Click the Number group’s dialog launcher (Home tab). In Excel 2003, right-click the selected range and choose Format Cells.
3. In the resulting dialog box, choose Custom from the Category list.
4. In the Type control, enter 0,0;;; (as shown in Figure D).
   Figure D
   
5. Click OK.

The stacked bar and pie charts won’t chart the 0 values, but the pie chart will display the category labels (as you can see in Figure E). If this is a one-time charting task, just delete the single category label. Because they’re so easy to apply, try deleting the 0s or formatting them first, but don’t expect a panacea for every chart.

Figure E

Charting a filtered data set

If you have a single data series, you can filter out the 0 values and chart the results. Like the methods discussed above, it’s a limited choice. Use it when it works, but it won’t always work. Now, let’s add a filter to the Vendor 1 column:

1. Click inside the data range. In Excel 2003, select the entire range, including the header row.
2. On the Data tab, click Filter in the Sort & Filter group. Doing so will add a filter to all of the columns, not just column B, but you can ignore all but the filter for column B. In Excel 2003, choose Filter from the Data menu. Then, choose AutoFilter.
3. Click Vendor 1’s drop-down and uncheck 0. In Excel 2002, select Custom, choose the Does not equal option from the first drop-down, and then enter 0.
4. Click OK to filter the column, which will filter the entire row. Don’t worry about that (be sure to remove the filter when you’re done).

Figure F shows the two charts based on the filtered data in column B. Neither displays the 0 value or the category label. This method is my least favorite because the chart updates, displaying the 0 values, when you remove the filter. On the other hand, if your chart is a one-time task, filtering offers a quick fix.

Figure F

Replace 0s with NA()

Perhaps the most permanent fix is to replace literal 0 values with the NA() function using Excel’s Find and Replace feature. If the data is updated regularly, you might even enter NA() for 0s from the get-go, which will eliminate the problem altogether. However, that’s not always practical.

Excel won’t chart #N/A! values. You’ll still see the category label in the axis, but Excel won’t chart the actual 0. Now, let’s use Excel’s Replace feature to replace the 0 values in the example data set with the NA() function:

1. Select the data set (in this case, it’s B2:D9)
2. Click Find & Select in the Editing group on the Home tab, and choose Replace. In Excel 2003, choose Replace from the Edit menu. In all versions, you can also press [Ctrl]+[H].
3. Enter 0 in the Find what control.
4. Enter =NA() in the Replace control.
5. If necessary, click Options to display additional settings.
6. Check the Match entire cell contents option (as shown in Figure G).
7. Click Replace All, and Excel will replace the 0 values.
8. Click Close, and then click OK to dismiss the confirmation message.

Figure G

None of the charts display the #N/A! values, but they still display the category label in the axis and the legend (as shown in Figure H). You can manually delete them from the legends, but not the axis.

Figure H

If you’re working with the results of formulas that might return 0, instead of literal values, you can use an IF() function to return the #N/A! error using the following syntax:

=IF(formula=0,NA(),formula)

Figure I shows just such a case. The MIN() function returns the minimum value for each month. The IF() function returns #N/A! if the result is 0:

=IF(MIN(B2:D2)=0,NA(),MIN(B2:D2))

Figure I

The example’s contrived, but don’t let that bother you. The 0 values aren’t charted, but Excel continues to display their category labels on the axis.

No panacea

There isn’t an easy one-size-fits-all solution for the problem of 0-less charts. If you’re displaying 0s for reporting purposes, you’ll need to maintain two data sets — one for reporting and one for charting. Removing the category label from the axis dynamically is even harder. On the other hand, if your requirements aren’t quite so strict, one of the solutions I’ve discussed should be adequate.

TechRepublic Premium Exclusives Newsletter

Save time with the latest TechRepublic Premium downloads, including original research, customizable IT policy templates, ready-made lunch-and-learn presentations, IT hiring tools, ROI calculators, and more. Exclusively for you!