Copyright infringement

How to suppress 0 values in an Excel chart

There is no one-size-fits-all solution to remove 0 values ​​from an Excel chart. Here’s a look at some of the ways.

Editor’s note: In the video, Brandon Vigliarolo walks you through two ways to suppress 0 values ​​in excel Graphs. For this display, he uses Microsoft Office 365. The steps are similar to what Susan Harkins describes in the next lesson.

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

More information: Office 365 Consumer pricing and features

About example data

Figure A It shows the raw data and charts that we’ll be using throughout this article. At the moment, the graphs are showing 0 values. .xlsx and .xls files contain xls downloadable on data and schemas. Your results may vary, depending on your version of Excel and the defaults for the chart. If you’re working through the instructions using the sample workbook, be sure to undo each solution before starting the next. Simply close the file and reopen it without saving.

Figure A

Figure A

Pie charts and single line charts reflect the data in column B for Resource 1. The other two charts contain three data series: Vendor 1, Resource 2, and Resource 3. This setup simplifies all the examples. Now that you’re familiar with sample data, let’s review some ways to suppress zero values ​​in our example charts. Some will work with limited results, others won’t at all – but before that and give them a try.

Easier but limited

You might try removing 0 entirely if it is a literal 0 and not the result of a formula. Unfortunately, this simpler approach does not always work as expected. Stacked tape responds well to this solution. The pie chart does not draw missing zeros, but the legend still displays the category label. Neither line chart handles the missing zero very well, as you can see in Figure B (If you removed the 0 values ​​in the sheet, re-enter them before continuing).

Figure B

Figure B

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

  1. Click on 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 go to #3.
  2. Choose Advanced options in the left pane.
  3. In the Display options for this worksheet section, choose the appropriate sheet from the drop-down list.
  4. Deselect the Show a zero in cells that contain a zero value option (as shown in Figure C).
    Figure C
    Figure C
  5. Click OK.

0 values ​​are still present; You can see it in the format bar, but Excel won’t display it. This method has little or no effect. For the most part, the schema treats zero values ​​as if they were still there, because they are.

You can also try using the following format which hides 0 seconds:

  1. Select the data range.
  2. Click the Number Set dialog launcher (Home tab). In Excel 2003, right-click on the selected range and choose Format Cells.
  3. In the resulting dialog, choose Custom from the Category list.
  4. In the type control, enter 0,0;;; (As shown in figure d).
    figure d
    figure d
  5. Click OK.

The stacked bar and pie chart will not plot 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 layout task, simply delete one category label. Because they are easy to apply, try deleting or formatting zeros first, but don’t expect a blanket treatment of every schema.

Figure E

Figure E

Draw a filtered data set

If you have a single data series, you can filter out the zero values ​​and plot 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 resource column 1:

  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 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 the Resource 1 drop-down list and deselect 0. In Excel 2002, select Custom, choose the Not equals option from the first drop-down list, and enter 0.
  4. Click OK to filter the column, which will filter the entire row. Don’t worry about it (be sure to remove the filter when you’re done).

shape and Displays the charts based on the filtered data in column B. Neither display the value 0 or the category label. This method is my least favorite because the schema gets updated, and returns 0 values, when the filter is removed. On the other hand, if your schema is a one-time task, filtering offers a quick fix.

shape and

shape and

replace 0s with NA()

Perhaps the most permanent solution is to replace the literal 0 values ​​with the NA() function using Excel’s Find and Replace feature. If the data is updated regularly, you can even enter NA() for zeros from the beginning, which will eliminate the problem completely. However, this is not always practical.

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

  1. Select the dataset (in this case, it’s B2:D9)
  2. Click Find and 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 search for the control.
  4. Enter =NA() in the Replace Control.
  5. If necessary, click Options to view additional settings.
  6. Select the option Match entire cell contents (as shown in shape g).
  7. Click Replace All, and Excel will replace the values ​​0.
  8. Click Close, then click OK to dismiss the confirmation message.

shape g

shape g

None of the graphs display the error #N/A! values, but it still displays the category label in the axis and legend (as shown in shape h). You can delete them manually from the legends, but not from the hub.

shape h

shape h

If you are working with results of formulas that may return 0, rather than literals, you can use the IF() function to return #N/A! Error using the following syntax:

= if (formula = 0, NA(), formula)

first figure It just shows 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))

first figure

first figure

The example is innovative, but don’t let that bother you. Values ​​0 are not plotted, but Excel continues to display their category labels on the axis.

no medicine

There is no easy one-size-fits-all solution to the zero-charts problem. If you are displaying 0s for reporting purposes, you will need to keep two sets of data – one for reporting and one for graph. It is getting more and more difficult to remove the category label from the axis dynamically. On the other hand, if your requirements are not very stringent, then one of the solutions I have discussed should be suitable.

see also

Microsoft Excel