This post will show you all the ways to display your null values as hyphens or hyphens in Google Sheets.
Does the number zero appear frequently in your data set and you want to display it in a way that distinguishes it from other values?
Null means the absence of something, but having them appear in a dataset, especially when there are few of them, can make the dataset feel unnecessarily cluttered and overwhelming.
There's a trick to changing all the zeros in your spreadsheet: replace them with a hyphen. But you can use any character of your choice to replace zeros in your spreadsheet.
In this exercise, we'll show you methods you can use to display zeros in your spreadsheet as a dash.
The methods that will be discussed are listed below.
- custom number formats.
- TEXTfunction.
- CONSULTATIONfunction.
- SEfunction.
- find and replace.
Get your copy of the sample workbook and stick with it.
Show zero as dash with custom number formats
There are several predefined number format options in the spreadsheet.
When that's not enough, you can usecustom number formats, which allows you to set a number format of your choice.
you can accesscustom number formatsresource ofFormatmenu.
- YeahFormatmenu.
- chooseNumberof the possibilities.
- choosecustom number formatin the submenu settings.
After clickingcustom number formatsofNumberdropdown options, acustom number formatsThe dialog opens.
The text box in the dialog allows you to pass format syntax for four categories of values.
- Positive: Receive format instructions for positive numbers
- Negative: Receive format instructions for negative numbers
- null: Allows you to format declarations to display null values.
- Text: allows you to pass formatting statements to text values
The section below the text box shows what each format statement will look like. Special characters and symbols are used to send format instructions.
You can, for example,use a custom number format to display values in accounting format.
To indicate the end of the format syntax section of one category and the beginning of another, use the semicolon character;.
Usingcustom number formats, first select the cell or range of cells you want to format.
#,##0; [rojo]#,##0; -Copy and paste the above syntax intocustom number formatstext box and clickapplybutton.
💡Advice: You also canuse custom formats to display zeros as spaces!
All zeros in your data are now represented by a hyphen.
📝Observation: Custom format allows the value to remain unchanged; it only changes the way the value is displayed. When you select the cell, you will still see zerovalue displayed in the formula bar.
Display zero as hyphen with TEXT function
ohTEXTThe function does exactly what the name suggests. Convert values to text format. You can also use this to apply custom formatting to the text value.
= TEXT(from, format)- numberThis is where you enter the cells or cell ranges with the values you want to format.
- FormatThis is where you post your formatting instructions. The format instructions use symbols and characters similar to those used incustom number formats. don't argueFormatsection must be enclosed in double quotes
"".
= TEXTO (D2, "#,##0;#,##0;-" )Select an empty cell and paste the above formula. Use the fill handle to copy the formula to other cells.
📝Observation:OTEXTconverts values to text or string. Even though you see numbers, it's a text value for Google Sheets.
This means that no calculations can be made based on these numbers, which is a disadvantage. WearTEXTwill only work if no further numerical calculations are required.
Display zero as hyphen with QUERY function
ohCONSULTATIONThe function is a very powerful tool in Google Sheets that allows you to perform various types of calculations and data manipulation using SQL-like statements.
Watch thisComplete guide to the QUERY functionfor more details.
=QUERY(data, query, [header])- datais the range of cells that contain your data.
- consultationshould contain the operational command you want to run on yourdata. Everything in this argument must be in quotes.
"". - headerindicates the number of rows that are column headers. This is an optional argument, but if left blank, the function will approximate the row to the 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. Note that the query format syntax is enclosed in single quotes.'.
📝Observation:OCONSULTATIONThe function preserves the values as numbers even when a format is applied. You can easily apply additional numerical calculations to the results.
Display zero as hyphen with IF function
ohSEThe function allows you to return one of two values based on a logical test.
= IF(logical_expression, value_if_true, value_if_false)- logical_expressionthis is where you define the condition that determines which valuesSEthe function returns. Logical expressions must return a true or false Boolean value.
- value_if_truethis is where you define what to return iflogical_expressionand the truth.
- value_set_falsethis is where you define what to return iflogical_expressionit's false.
when you dominateSEsyntax, you will use it quite often because of its adaptability to different situations.
In the illustrations above, the values inunsoldThe column was already derived before they changed its format. This will not be the case forSEfunction.
Instead of calculating the values before changing the format, we useSEfunction to get the value and change any null value to a hyphen.
= SE (B2-C2 = 0, "-", B2-C2 )Copy and paste the above formula into an empty cell on the spreadsheet. Drag the fill handle down on the empty cells to copy the formula into them.
The benefit of usingSEThe feature in this situation is that you can still do calculations with the column. This is possible because text values in the column are ignored or classified as null, and calculations are performed on numeric values.
Show zero as dashes with Find and Replace
find and replaceis a function that you can use to search for characters and replace them with other characters.
You can use this function to replace all null values with a hyphen.
you have access tofind and replacefunction on the Edit menu tab.
- Yeahto editmenu.
- choosefind and replaceof the possibilities.
You can also use the keyboard shortcut.Control+hOpenfind and replacedialog box.
In the dialog there are three command sections.
- MeetThis is where you enter the character you want to appear on your spreadsheet.
- replace withthis is where you enter the replacement character
- Look forthis is where you define your search parameters.
ohall worksheetsThe command allows you to find and replace characters anywhere on the worksheet.This sheetallows you to find and replace characters in the active worksheet, whilecertain rangeThe option allows searching with a selected range.
The other sets of options are used to define how the character search should be performed.
paired upAllows character search to be case sensitive. If you check this box and have aINNoMeetbox, the search will only look for oneIN, arein.when searching0values, this will not be relevant.
ohCombine all cell contentto allowfind and replaceto return only cells that exactly matchMeetworth. If eg. check this box and search"mi", although other cells contain the word, so far the cell contains other words,find and replaceYou won't notice. Instead, it will capture cells that only contain the word."mi".
ohSearch using regular expressionsallows you to search using special characters and symbols, such as those used when defining formatting rules incustom number format.
ohSearch also in formulasThe option allows you to include formulas in a search.
before being able to usefind and replacefunction to change zeros to dashes, copy and paste values, a copy of their values, so as not to change the original source data.
you should also useconvert any formula to valuessince find and replace will not be able to replace zeros derived by formulas.
Select the column and useControl+Cto copy select an empty cell and useControl+Change+Vto insert the values.
- Select the range of cells you want to work with. then openfind and replacedialog box.
- Place0NoMeetbooks.
- draw a line-Noreplace withbooks.
- click notLook forchance and choicecertain range.
- Check the square box in frontCombine all cell contentpossibility.
- click notreplace everythingbutton.
As soon as you clickreplace everything, you will be notified that the replacement has been made and it will tell you how many zeros were replaced.
you can clickmadeto exit the dialog.
conclusions
Converting zeros to dashes can make your data set easier to read, and there are many methods you can use to accomplish this.
If you don't expect any changes to your data, use onecustom number formatsor by usingfind and replaceThe method will be the perfect option.
MenTEXToCONSULTATIONFeatures may be a more sustainable option as your data expands in the future. However, you should avoid the TEXT function if you intend to apply some more numerical calculations to your numbers.
UserSEThe function does not require an additional column to produce the result of converting zero to a hyphen.
Have you ever displayed your null values as hyphens or some other character? Do you use another method for this? Let me know below in the comments!
FAQs
How do I format zeros as dashes in Google Sheets? ›
- Highlight the table.
- Go to the Home tab.
- Click on the Conditional Formatting button.
- Choose Highlight Cells Rules.
- Choose Text That Contains.
- In the pop-up window, type a dash.
- Instead of “Light Red Fill with Dark Red Text,” go to the bottom of the drop-down list, and choose Custom Format.
Add Apostrophe (') Before the Number
All you need to do is first type an apostrophe (') symbol and then type the number. So if you want the value in the cell to be 000123, you should enter '000123. Adding an apostrophe converts the number you enter into text, and the leading zeros don't go away.
- Let us consider an Excel sheet where the data in the sheet is similar to the data shown in the below image. ...
- Then click on replace and type 0 in find what and – in replace with, then click on replace all as shown in the below image to complete our process.
- Go to the Number tab in the Format Cells menu.
- Select the Custom option in the Category section.
- Enter the #,##0;-#,##0;- format string into the Type field.
- Press the OK button.
- Select the cell in which you want to add zeros as a prefix.
- Right-click ➜ Format Option or you can also use the shortcut key Ctrl + 1 open “Format Cell Dialog Box”.
- In Category ➜ Select Custom.
- In the custom input dialog box, enter “0000000”.
- Click OK.
Google Sheets removes leading zeros if you type a number with them inside any cell. A common practice most of the users follow is changing the format of cells. If you change the format of the cells as text, you can easily store the numbers with leading zeros as the system will consider them as text inside those cells.
How do you make blank cells show a dash? ›Instead of pressing Enter, press Ctrl + Enter to enter the dash symbol in all of the selected blank cells, as shown in Figure D.
How do I automatically insert dashes in Excel? ›- Select the cell or range of cells where you want to insert the dashes.
- Click the Home tab.
- In the Font group, click the Insert drop-down arrow.
- Click Special Characters. A dialog box appears.
- In the Symbol tab, select the character you want to insert. ...
- Click Insert.
For desktop PC: press “alt+ctrl+minus” on the numeric keypad (the number section on the far right of your keyboard). The trick will not work if you press the hyphen-key on the typewriter section of the keyboard.
How do you dash on Google? ›Tap the hyphen key twice type your word, and then press the spacebar. You'll see Google Docs change the hyphens to an em dash. Windows users accessing Google Docs in a web browser can create an em dash by holding the Alt key and typing 0151 on the numpad. This shortcut also works in other apps.
How do you insert a dash symbol? ›
For Windows on a keyboard with a numeric keypad: Use Alt + 0 1 5 0 (en dash) or Alt + 0 1 5 1 (em dash) using the numeric keypad.
How do I fix number format in Google Sheets? ›- On your computer, open a spreadsheet in Google Sheets.
- Highlight the data you want to format.
- Click Format Number.
- Click Custom number format.
- Search in the menu text box to select a format. ...
- Click Apply.
Display Negative Values as Zeros
Instead, we can change the number formatting to 0;”0”;0. This will display negative numbers as zero.
Click the File tab, choose Options in the left pane and then click Advanced in the left pane. 2. In the Display options for this worksheet section, uncheck Show a zero in cells that have zero value (Figure A).