This post is going to show you all the ways you can display your zero values as a dash or hyphen in Google Sheets.
Does the number zero appear often in your dataset, and do you want to display them in a way to differentiate them from other values?
Zero means the absence of something, but having them appear in a dataset, especially when there are quite a few, can make the dataset look unnecessarily populated and overwhelming.
There’s a trick to changing all the zeros in your worksheet, and that is to replace them with a dash. But you could use any character of your choosing to replace zeros in your spreadsheet.
In this tutorial, we will show methods you can use to display zeros in your spreadsheet as a dash character.
The methods that will be discussed are listed as follows.
- Custom Number Formats.
- TEXT function.
- QUERY function.
- IF function.
- Find and Replace.
Get your copy of the example workbook and follow along.
Show Zero as Dash with Custom Number Formats
There are a handful of pre-set number formatting options in the spreadsheet.
When these don’t suffice you can use Custom number formats, that allow you to define a number formatting of your choice.
You can access the Custom number formats feature from the Format menu.
- Go to the Format menu.
- Select Number from the options.
- Select Custom number format from the submenu options.
After clicking on Custom number formats from the Number dropdown options, a Custom number formats dialogue box opens up.
The text box in the dialogue box allows you to pass formatting syntax to four categories of values.
- Positive: takes formatting instruction for positive numbers
- Negative: takes formatting instruction for negative numbers
- Zero: allows formatting instruction for the displaying of zero values.
- Text: allows you to pass formatting instructions for text values
The section below the text box shows you how each formatting instruction will appear. Special characters and symbols are used to pass the formatting instructions.
For example, you can use a custom number format to show values in accounting format.
To indicate the end of the formatting syntax section for a category and the beginning of another, you need to use the semi-colon character ;.
When using Custom number formats, you must first select the cell or range of cells that you want to format.
#,##0; [red]#,##0; -Copy and paste the above syntax into the Custom number formats text box and click on the Apply button.
💡 Tip: You can also use custom formats to display zeros as blanks!
All the zeros in your data are now represented by a dash.
📝 Note: Custom formatting allows the value to remain unchanged, it is only the way the value is displayed that changes. When you select the cell, you will still see the zero value displayed in the formula bar.
Show Zero as Dash with TEXT Function
The TEXT function does exactly what its name suggests. It converts values to text format. You can also use this to apply a custom format to the text value.
= TEXT ( number, format )- number is where you enter the cell or cell ranges with the values you want to format.
- format is where you pass your formatting instructions. The formatting instructions use similar symbols and characters as those used in Custom number formats. Arguments in the format section must be within double quotes
"".
= TEXT ( D2, "#,##0;#,##0;-" )Select an empty cell and paste the above formula. Use the fill handle to copy the formula to the other cells.
📝 Note: The TEXT converts values to text or string. Even though you’re seeing numbers, it is a text value to Google Sheets.
The implication is that you cannot do a calculation on these numbers, which is a bit of a drawback. Use the TEXT function only if further numerical calculations are not needed.
Show Zero as Dash with QUERY Function
The QUERY function is a very powerful tool in Google Sheets that allows you to carry out various kinds of computations and data manipulation using SQL-like statements.
Check out this full guide on the QUERY function for further details.
= QUERY ( data, query, [header] )- data is the cell range containing your data.
- query should contain the operational command you want to carry out on your data. Everything in this argument must be surrounded by quotation marks
"". - header defines the number of rows that are column headers. It’s an optional argument, but if left empty, the function will approximate the row with column headers. The argument takes numeric values.
= QUERY ( D2:D7, "format D '#,##0; [red]#,##0; -'", 0 )Copy and paste the above formula into your workbook. Notice the format syntax within the query is encased in single quote marks '.
📝 Note: The QUERY function keeps values as numbers even when a format is applied. You can use further numerical calculations on the results without issue.
Show Zero as Dash with IF Function
The IF function allows you to return one of two values based on a logical test.
= IF ( logical_expression, value_if_true, value_if_false )- logical_expression this is where you define the condition that will determine what values the IF function will return. The logical expressions should return a Boolean value of either true or false.
- value_if_true this is where you define what to return if logical_expression is true.
- value_if_false this is where you define what to return if logical_expression is false.
When you master the IF syntax, you will find yourself using it quite often because of its adaptability to various situations.
In the previous illustrations, the values in the Unsold column were already derived before changing their format. This will not be the case with the IF function.
Instead of calculating the values before changing the format, we will use the IF function to get the value and change any zero values to a dash.
= IF ( B2-C2 = 0, "-", B2-C2 )Copy and paste the above formula into an empty cell in the sheet. Drag the fill handle down the empty cells to copy the formula into them.
The benefit of using the IF function in this situation is you can still do computations with the column. This is possible because the text values in the column are ignored or classified as zero, and computations are done on the numeric values.
Show Zero as Dash with Find and Replace
Find and replace is a feature that you can use to find characters and replace them with other characters.
You can use this feature to replace any zero values with a dash character.
You will access the Find and replace feature in the Edit menu tab.
- Go to the Edit menu.
- Select Find and replace from the options.
You can as well use the keyboard shortcut Ctrl + H to open the Find and replace dialogue box.
In the dialogue box, there are three sections of commands.
- Find is where you enter the character you want to be found in your spreadsheet
- Replace with is where you enter the replacement character
- Search is where you define your search parameters.
The All sheets command allows you to find and replace characters anywhere it’s located in the whole spreadsheet. This sheet allows for finding and replacing characters within the active sheet, while the Specific range option allows for searches to be done with a selected range.
The other sets of options are used to define how the search for the character should be performed.
Match case allows the character search to be case sensitive. If you check this box and you have an A in the Find box, the search will strictly look for an A, not a. When searching for 0 values, this won’t be relevant.
The Match entire cell contents allow Find and replace to only return cells that exactly match the Find value. For instance, if you tick this box and you search for “and”, even if other cells have the word in them, so far the cell contains other words, Find and replace will not pick up on it. Instead, it will pick up on cells that only contain the word “and”.
The Search using regular expressions allows you to run searches using special characters and symbols like the ones used when defining formatting rules in Custom number format.
The Also search within formulae option allows you to include formulas in a search.
Before you can use the Find and replace feature to change zeros to dashes, you should copy and paste values a copy of your values so you don’t change the original source data.
You will also need to convert any formulas to values as the find and replace will not be able to replace zeros derived using formulas.
Select the column and use Ctrl + C to copy then select an empty cell and use Ctrl + Shift + V to paste the values.
- Select the range of cells you want to work with. Then open the the Find and replace dialogue box.
- Place 0 in the Find box.
- Place a dash - in the Replace with box.
- Click on the Search option and choose Specific range.
- Check the square box in front of the Match entire cell content option.
- Click on the Replace all button.
As soon as you click on Replace all, you will get notified that the replacement has taken place and it will inform you how many zeros were replaced.
You can click on Done to exit from the dialogue box.
Conclusions
Converting zeros to dashes can make your data set easier to read and there are many methods you can use to achieve this.
If you’re not expecting any changes with your data, then applying a Custom number formats or using the Find and Replace method will be the perfect option.
But the TEXT or QUERY functions can be a more sustainable option as your data expands in the future. However, the TEXT function should be avoided if you intend to use some further numerical calculations on your numbers.
Using the IF function doesn’t require an extra column to output the result of the conversion of zero to a dash.
Do you ever show your zero values as dashes or some other character? Do you use any other methods for this? Let me know in the comments below!
