This post is going to show you all the ways you can format zero values as blanks in Google Sheets.

Formatting zero values as blanks is a common technique used to hide 0 values in Google Sheets. This can help make the spreadsheet easier to read and interpret.

With large amounts of data, it can be difficult to quickly identify which cells contain zero values. Formatting zero values as blanks can add a visual cue and makes it easier to see patterns in the data.

Zero values in your data might also indicate that no data exists, and if this is the case showing a blank could be more suitable.

Read on to see all the methods for displaying zeros as blanks in Google Sheets.

Use the above link to get your copy of the example workbook to follow along with this post.

Show Zero as Blank with Custom Number Formats

Formatting allows you to add style to the way the contents of your spreadsheet appear. Google Sheets has some great pre-set formatting options that you can use to provide a bit more context to your spreadsheet.

You’re not limited to the formatting options provided by Google Sheets, as the Custom number format tool allows you to create a format that is tailored to your need.

Before you start to create a Custom number format syntax, you should first select the range where you want to apply the format.

From our sample data, the range with the data we want to format is in the second column. So, we select the second column.

You will find the Custom number format tool in the Format menu.

1. Go to the Format tab.
2. Select Number from the options.
3. Select Custom number format from the submenu options.

####;(####);

1. Copy and paste the above syntax into the format text box in the Custom number formats dialogue box,
2. The contents below the format input give you a preview of how the formatting will appear in your spreadsheet.

You can create custom formats for positive, negative, zero values by using special characters to pass formatting instructions. Each format syntax is separated by a ; character and the position will dictate whether the format applies to positive numbers, negative numbers, or zeros.

The hashtag # symbol used in the syntax acts as a placeholder for non-zero values.

You will notice that there’s nothing entered for the Zero category. That is appropriate because the semi-colon indicates the start of a new formatting instruction. And since we want zeros to display as blank, we just leave the formatting instruction empty.

####;(####);" "

The above alternative could also be used to hide the zero values.

Whichever syntax you use, the zeros in the selected range will be removed after you click on the Apply button.

💡 Tip: You can also use a custom number format to show zeros as dashes. This way the cell doesn’t look empty.

You don’t have to recreate the format syntax every time you need to apply a custom format. The Google Sheets automatically saves custom formats which you can access from the Custom number formats dialogue box.

Also, you can find a list of recently used formats in the Numbers option although this list is limited to the last three applied formats.

Show Zero as Blank with the TEXT Function

= TEXT ( number, format )

The TEXT function is another way you can create and apply custom formatting.

When you use the TEXT function to apply a custom format, it changes the type from numeric to text. It’s recommended that you use this method when you are applying formatting to numbers which you won’t make any further calculations.

= ARRAYFORMULA ( TEXT ( B2:B10, "####;(####); " ) )

Copy and paste the above formula. The ARRAYFORMULA allows the TEXT function to return results for the entire column.

Now you have a column where the zero values have been replaces with blank cells.

Show Zero as Blank with the QUERY Function

The QUERY function is an incredible tool in Google Sheets that can perform a variety of tasks using sequel commands.

= QUERY ( data, query, [headers] )

The QUERY returns the results of an SQL query on your data.

= QUERY ( B2:B10, "FORMAT B '####; (####); '" )

With the FORMAT command, you can return custom formats in your query results. Copy and paste the syntax into your workbook and it will return the data with zeros formatted as blank cells.

The TEXT and QUERY functions use the same special characters for creating custom formats.

The QUERY function is an array formula, so it returns results for the entire column and it doesn’t convert the numbers to text. If you want to don’t want to change the data type of your numbers, you should use the QUERY function.

Show Zero as Blank with the IF Function

The IF function is a popular function in Google Sheets because you can use it in various situations.

= IF ( logical_expression, value_if_true, value_if_false )

You can use it to change zeros to blank by testing if the content of a cell is zero and specifying the value that will be returned if the condition is true or false.

= ARRAYFORMULA ( IF ( B2:B10 = 0, " ", B2:B10 ) )

This syntax will return a blank if the cell value is equal to zero. Otherwise, it will return the cell contents as is.

Show Zero as Blank with Find and Replace

The Find and replace is a method you can use to replace any zero values with a blank value.

This makes it the simplest way of completing this task. It wouldn’t be appropriate in every situation mind you, but it will work perfectly for changing zeros to blanks.

Follow these steps to utilize the Find and replace feature to replace zeros with blanks.

Find and replace is in the Edit menu options. When you open the dialogue box, follow these steps.

1. Go to the Edit menu.
2. Select Find and replace from the options.
3. Enter 0 In the Find text box.
4. Leave the Replace with text box blank.
5. Select Specific range as the Search option. Click on the next box and select the range you want to use.
6. Check the Match entire cell contents box. It’s important to check this box otherwise all zeros will be removed in the range. When you check the box, cells that contain only zeros will be replaced.
7. Click on the Replace all option. The dialogue box will show you the number of replacements made.

When you’re done, click on Done to exit the dialogue box. This will replace all the zero values in your range with a blank.

Show Zero as Blank with an Apps Script

Google Sheets provide a lot of customization options for its users, and the Apps script is perhaps the highest level of customization you will find.

With the Apps script, you can create custom tools that suit your project needs. You can create functions, add-ons, menus, sidebars, and lots more.

You can think of it as a DIY tool that you can use to create almost anything with the spreadsheet.

You will find the Apps Script option under the Extensions menu.

When you click on Apps Script, a code editor window will open in a new tab on your browser.

  function zero() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var selectedRange = ss.getActiveRange();
    selectedRange.createTextFinder("0").matchEntireCell(true).replaceAllWith(" ");
  }

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Blank')
      .addItem('Change zeros to blank', 'zero')
      .addToUi();
}

Paste the above code into the editor. This code will find and replace zero values with blanks in your selected range.

After pasting the syntax, click on Save, and then Run. When you’re done with this, go back to your spreadsheet and refresh the page.

The script will create a new menu item with the name Blank. You will see this new menu after your spreadsheet page finishes reloading.

To change zeros to blank, select the appropriate range and click on the Change zeros to blank submenu.

The script works a lot like Find and Replace, but with fewer clicks.

Conclusions

There are many options for hiding your zero values by showing them as blanks in Google Sheets.

The Conditional number format is the easiest method to use for changing zeros to blanks, and it doesn’t change the source data or create redundant columns.

Using the Find and replace or apps script methods will change the source data by removing the zeros from your dataset.

The TEXT and QUERY functions can be useful when you want a dynamic solution but want to leave the source data untouched.

Do you know of other methods for showing zeros as blanks? Let me know in the comments below!