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.

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

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.

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

you findcustom number formattool iFormatmenu.

  1. YeahFormataba.
  2. chooseNumberof the possibilities.
  3. choosecustom number formatin the submenu settings.
6 Ways to Show Blank Zero in Google Sheets (3)
  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.

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

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.

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

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

6 Ways to Show Blank Zero in Google Sheets (6)
= 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.

6 Ways to Show Blank Zero in Google Sheets (7)
= 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.

6 Ways to Show Blank Zero in Google Sheets (8)
=QUERY(data, query, [headers])

ohCONSULTATIONreturns the results of a SQLconsultationin youdata.

6 Ways to Show Blank Zero in Google Sheets (9)
= 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.

6 Ways to Show Blank Zero in Google Sheets (10)
= 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.

6 Ways to Show Blank Zero in Google Sheets (11)
= 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.

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

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.

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

you findapplication scriptoption belowextensionsmenu.

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

6 Ways to Show Blank Zero in Google Sheets (14)
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.

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

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.


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!


How do I show blank 0 in Google Sheets? ›

A clever work around though can be found by using conditional formatting.
  1. Select all cells (Ctrl + A) click Format > Conditional Formatting.
  2. Use the dropdown to select Equal To and type 0.
  3. Tick the text tickbox and change the text color to white, this will effectively hide all your zero values.

How to fill empty cells with 0 with value above below in Google sheet? ›

Fill Blank Cells With Value Above in Google Sheets

If you want to populate a cell with the value above, you can very simply enter a formula (that refers to the cell above) in the first blank cell, and then use the CTRL key to copy the formula to multiple cells.

How do you make a cell blank if there is no value in Google Sheets? ›

How do you make a cell blank if another cell is blank in Google Sheets? You can combine the ISBLANK formula with the IFERROR function. An example of the mixed formula is as follows: =IF(ISBLANK(A1), IFERROR(0/0,), A1/B1).

How do I make a cell stay blank if no value in Google Sheets? ›

If you want to end up with a truly 'blank' value, you can use the expression IFERROR(0/0) . This is different from an empty string which is what you get when you use "" .

How do I merge blank cells with value above in Excel? ›

Select Home > Merge & Center. If Merge & Center is dimmed, make sure you're not editing a cell or the cells you want to merge aren't inside a table. Tip: To merge cells without centering the data, select the merged cell and then select the left, center or right alignment options next to Merge & Center.


Top Articles
Latest Posts
Article information

Author: Cheryll Lueilwitz

Last Updated: 06/01/2024

Views: 6753

Rating: 4.3 / 5 (74 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Cheryll Lueilwitz

Birthday: 1997-12-23

Address: 4653 O'Kon Hill, Lake Juanstad, AR 65469

Phone: +494124489301

Job: Marketing Representative

Hobby: Reading, Ice skating, Foraging, BASE jumping, Hiking, Skateboarding, Kayaking

Introduction: My name is Cheryll Lueilwitz, I am a sparkling, clean, super, lucky, joyous, outstanding, lucky person who loves writing and wants to share my knowledge and understanding with you.