How to exclude x-axis labels if y-axis values are 0 or blank in Google Sheets?

There are three different methods to use if you want to exclude x-axis labels if the corresponding y-axis values are 0 in a Google Sheets chart.

Google Sheets chart (chart editor) has no such option to omit x-axis labels based on y-axis values. Here are my three different approaches to solve this.

1. Using the Filter menu.
2. With the help of Slicer (recommended).
3. Use the Query function.

The easiest way to exclude x-axis labels from a chart if the corresponding y-axis values are 0 or blank is by simply hiding the rows containing the 0/null values.

It’s a manual method and you can use this on any chart types including Line, Column, Pie, Candlestick and so on.

If there are a large number of records in your data, this method has the following limitations. So I don’t recommend you to use it.

1. It’s a time taking process to individually hiding rows.
2. When you hide, there are chances to accidentally hide rows containing values in the y-axis.

So I suggest the earlier mentioned three methods to exclude x-axis labels incase of blank/zero values in the y-axis.

Different Methods to Omit Certain X-Axis Labels in Google Sheets Charts

Filter Menu to Exclude X-Axis Labels If Y-Axis Values Are 0 or Blank

First of all, select the range that you want to filter. Always include additional rows at the last of the range to include future values.

Here I am selecting the range A2:B17 even though the data range is A2:B9.

Then go to Data > Create a filter to create a filter for the selected range.

Now you can see two drop-downs – once in cell A1 and the other in cell B2. Click the drop-down in cell B2 and uncheck ‘Blanks’ as well as ‘0’ or either of the ones depending on your requirement.

Click the “Ok” button.

This way you can omit the x-axis labels if y-axis values are 0 or blank.

Pros and Cons

Pros:

• Easy to set up and omit x-axis labels in a Google Sheets Chart.
• This method hides x-axis labels even if your chart is moved to its own sheet (check the 3 dots on the chart to see the option ‘Move to own sheet’).

Cons:

• Sometimes you may want to edit the row containing 0 or blank. Then you need to go to the Filter dropdown and uncheck the blank/0. Then enter the value in the required row and again access the dropdown to check the blanks/0s.

Slicer to Exclude X-Axis Labels If Y-Axis Values Are 0 or Blank (Recommended)

Another method is using the Slicer it has the same drawback as per the filter method above if the chart and source data are in the same tab.

So keep the source data in one tab and the chart and slicer in another tab. See that method.

Assume your source data is in ‘Sheet1’. Plot your chart in “Sheet2”.

If you have already your chart in ‘Sheet1″, click on it and copy using Ctrl+C or by click on the 3 vertical dot menu ‘Copy chart’ option.

Go to ‘Sheet2’ and paste it using Ctrl+V or right-click and paste from the shortcut menu that pops up. Then feel free to delete the chart in ‘Sheet1’.

Slicer – Steps:

We are in ‘Sheet2’ in which now the chart resides, right?

Follow the below steps to get a Slicer with already connection set to ‘Sheet1’.

• Click on the chart to select it.
• Then go to the menu Data > Slicer to insert a Slicer floating filter tool.
• Select the field (column) “Volume” and adjust the ‘Data range’ if you want.

• Drag and position the Slicer outside the chart area.
• Click “All” on the Slicer and uncheck ‘Blanks’ and ‘0’ similar to the Filter menu filtering. That’s all.

Pros:

• Filtering out blanks and 0s will only reflect in the chart, not in the source data. So you can edit the source data in the future hassle-free.

Cons:

Won’t work if you move the chart to own sheet.

Exclude X-Axis Labels Using Query

This is probably a familiar method for many of you. This method is like this. Filter chart source data to a new range using a formula and use that range to plot the chart.

Here is that formula suitable for our range.

Formula # 1:

=query(Sheet1!A2:B,"Select A,B where B>0",1)

It has only one disadvantage compared to the other methods. Wha is that?

You are creating an extra range of data only for the chart.

I have explained all the possible methods to exclude x-axis labels if y-axis values are 0 or blank in Google Sheets.

I suggest you use the Slicer method and the last one using Query.