6 Ways to Show Blank Zero in Google Sheets (2023)

This post shows you all the ways to format null values ​​as blank fields in Google Sheets.

Formatting null values ​​as spaces is a common technique used to hide null values ​​in Google Sheets. This can help make the worksheet easier to read and interpret.

With large amounts of data, it can be difficult to quickly identify which cells contain null values. Formatting null values ​​as blank fields can add a visual cue and make it easier to see patterns in your data.

Null values ​​in your data can also indicate that there is no data, and if this is the case, it may be more appropriate to display a blank field.

Read on to see all the methods to show zeros as spaces in Google Sheets.

Please use the link above to obtain your copy of the sample brochure that accompanies this publication.

Show blank zero with custom number formats

Formatting allows you to add style to the way your spreadsheet content is displayed. Google Sheets has great pre-built formatting options that you can use to give your spreadsheet a little more context.

You are not limited to the formatting options provided by Google Sheets, whichcustom number formatThe tool allows you to create a format tailored to your needs.

Before you can start creating a custom number format syntax, you must first select the range to which you want to apply the format.

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

you findcustom number formattool iFormatmenu.

1. YeahFormataba.
2. chooseNumberof the possibilities.
3. choosecustom number formatin the submenu settings.

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

1. Copy and paste the above syntax into the format text box in the Custom Number Formats dialog,
2. The content below the format entry provides an example of what the format will look like in your spreadsheet.

You can create custom formats for positive, negative, and zero values ​​by using special characters to pass format instructions. Each format syntax is separated by one.;The character and position will determine whether the format is applied to positive numbers, negative numbers, or zeros.

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

You will notice that nothing has been enterednullcategory. This is appropriate because the semicolon indicates the start of a new format declaration. And since we want the zeros to appear blank, we just leave the format declaration blank.

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

The above alternative could also be used to hide null values.

Regardless of the syntax used, zeros in the selected range will be removed after the button is clickedapplybutton.

💡Advice: You also canuse a custom number format to display zeros as dashes. That way the cell doesn't look empty.

There is no need to re-create the format syntax each time you need to apply a custom format. Google Sheets automatically saves custom formats that you can access fromcustom number formatsdialog box.

You can also find a list of recently used formats atOfoption, although this list is limited to the last three formats used.

Show blank zero with TEXT function

= TEXT(from, format)

ohTEXTThe function is another way to create and apply custom formatting.

when you useTEXTFunction to apply a custom format, changes the type from numeric to text. It is recommended that you use this method when formatting numbers that you will no longer be able to calculate.

= ARRAYFORMEL (TEXTO ( B2:B10,"####;(####);") )

Copy and paste the above formula. ISLANDMATRIXFORMELallows the TEXT function to return results for the entire column.

You now have a column where the null values ​​have been replaced by empty cells.

Display blank zero with QUERY function

ohCONSULTATIONThe function is a great tool in Google Sheets that can perform a variety of tasks using sequential commands.

=QUERY(data, query, [headers])

ohCONSULTATIONreturns the results of a SQLconsultationin youdata.

= SE ( B2:B10, "FORMATO B '####; (####); '" )

AsFORMATcommand, you can return custom formats in the query results. Copy and paste the syntax into your workbook and it will return data with zeroes formatted as empty cells.

ohTEXTmiCONSULTATIONThe functions use the same special characters to create custom formats.

ohCONSULTATIONThe function is an array formula, so it returns results for the entire column and doesn't convert numbers to text. If you don't want to change the data type of your numbers, useCONSULTATIONfunction.

Show blank zero with IF function

ohSEThe function is a popular function in Google Sheets because you can use it in so many situations.

= IF(logical_expression, value_if_true, value_if_false)

You can use it to convert null values ​​to empty fields, test if the content of a cell is null, and specify the return value if the condition is true or false.

= ARRAYFORMEL ( SE ( B2:B10 = 0," ", B2:B10 ) )

This syntax will return an empty field if the cell value is equal to zero. Otherwise, it returns the cell content as it is.

Show blank zero with find and replace

ohfind and replaceis a method you can use to replace all null values ​​with an empty value.

This is the easiest way to complete this task. I guess it wouldn't be appropriate in all situations, but it will work just fine for converting zeros to whitespace.

Follow these steps to use the Find and Replace function to replace zeros with blanks.

find and replaceIt's in the Edit menu options. When the dialog opens, follow these steps.

1. Yeahto editmenu.
2. choosefind and replaceof the possibilities.
3. Writing0NoMeetText's box.
4. leave itTo replacewith blank text box.
5. choosecertain rangeMen'sLook forpossibility. Click on the box below and select the range you want to use.
6. check aCombine all cell contentbox. It is important to check this box; otherwise, all zeros in the range will be removed. Selecting the check box will replace cells that contain only zeros.
7. click notreplace everythingpossibility. The dialog box will display the number of replacements.

When you're done, clickmadeto exit the dialog. This will replace all null values ​​in your range with a blank.

Show empty zero with an application script

Google Sheets offers a lot of customization options for its users, and the apps script is perhaps the highest level of customization you'll find.

With the Apps script, you can create custom tools that meet the needs of your project. You can create features, plugins, menus, sidebars, and more.

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

you findapplication scriptoption belowextensionsmenu.

when you clickapplication script, a code editor window will open in a new tab of your browser.

función cero() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var selectedRange = ss.getActiveRange(); selectRange.createTextFinder("0").matchEntireCell(true).replaceAllWith(" "); }function onOpen() { SpreadsheetApp.getUi() .createMenu('Blank') .addItem('Alterar ceros para blanco', 'nul') .addToUi();}

Paste the above code into the editor. This code will find and replace null values ​​with empty fields in the selected range.

Once you have pasted the syntax, clickSaving, and thencarrera. When you're done, go back to your spreadsheet and refresh the page.

The script will create a new menu item with the nameI'm blank. You'll see this new menu when the spreadsheet page reloads.

To change zeros to white, select the appropriate area and click the buttonchange zeros to whiteunder the menu.

The script works in a very similar way.find and replace, but with fewer clicks.

conclusions

There are many ways to hide your null values ​​by showing them as blank fields in Google Sheets.

ohConditional number formatIt is the easiest method to use for converting zeros to spaces and does not change the source data or create redundant columns.

Using Find and Replace applications or script methods will change the source data by removing the zeros from your data set.

ohTEXTmiCONSULTATIONFunctions can be useful when you want a dynamic solution but want to leave the source data intact.

Do you know of other methods to display zeros as spaces? Let me know below in the comments!