How to make a dash instead of a zero in a table

In some reports, it is preferable to put a dash instead of a zero in cells with a zero value, because a dash is more appropriate than a zero in a cell. To improve the readability of tables, there are several ways to notice 0 by (-).
Changing the number format
Using a custom format
Using the IF function
Using the TEXT function
Using Find and Replace
With VBA

Changing the number format

A dash instead of a zero is often used in financial statements. A special format has been added to Excel for this purpose.

1. Select the column you want to replace.
2. Go to the “Home” tab.
3. Call the context menu in the “Number” block.
4. Select the “Accounting” format, set the desired number of decimal places and omit the currency designation at the end of the number, if necessary.
5. Click OK.

As a result, zeros were displayed as a dash.

Using a custom format

The custom format allows you to display the data in accordance with the desires of the Excel user. It can also be used to replace zero values with a dash.

1. Select the range of cells in which changes will be made.
2. Go to the “Home” tab.
3. In the “Number” block, press the arrow button to open the menu.
4. Select the “Custom” format type.
5. In the Type field, enter a combination #,##0;-#,##0;-
6. Click OK.

Then you can remove the extra zeros and bring to the desired form of the number. If you click on a cell with a dash, zero will still be displayed in the formula bar, because the value of the cells is zero, and the dash is just a form of displaying this value.

Using the IF function

The IF function will check each cell for a null value, and if this expression is true, put a dash sign.

1. Place the cursor in the first cell in the range.
2. Write the formula for checking for zero in the form: =IF(C3=0,”-“,C3 )
3. Check if the formula is correct.
4. Copy the formula to subsequent cells in the column.

Note that cells with dashes will have a text value.

Using the TEXT

The TEXT function also displays zero as a dash.

1. Create an empty column next to the column where you want to replace the zero with a dash.
2. In the first cell of this column, write a formula like =TEXT(C3,”#,##0;-#,##0;-” )
3. Copy the formula to the rest of the cells in the column by dragging down the bottom right corner of the cell.

Check that all zeros have been replaced with a dash. In this case, the cell values are replaced with text. This must be taken into account if the data will be subject to any changes, for example, arithmetic calculations.

Using Find and Replace

This is the simplest way to replace a zero with a dash.

1. Highlight the range in which you want to replace.
2. Go to the “Find and Replace” section in the Excel menu or by pressing the keyboard shortcut Ctrl+H
3. Enter 0 in the Find field, and – in the Replace field.
4. Check the “Match entire cell contents” option so that 0 in the numbers will not be replaced by a dash.
5. Click the “Replace All” button.
6. Close the “Find and Replace” window.

As a result of the replacement, all zeros in cells will be replaced by dashes, except for zeros in numbers.

With VBA

VBA is a great way to handle many Excel tasks gracefully. Let’s create a macro that will automatically replace zeros with dashes.

1. Go to the “Developer” tab. If it is not in the menu, press the keyboard shortcut Alt+F11.
2. In the window that appears, go to the “Insert” tab.
3. Select the “Module” section.
4. Dial the following code:
   Sub ReplaceZeros()
   Dim selectedRange As Range
   Set selectedRange = Application.Selection
   For Each i In selectedRange.Cells
   If i.Value = 0 And Len(i.Value) > 0 Then
   i.Value = “-”
   End If
   Next i
   End Sub
   
5. Close all windows. The macro will be saved automatically .
6. Select the range of cells to replace 0 with a dash.
7. Go to the “View” tab and open the “Macros” section.
8. Select the ReplaceZeros macro and click the Run button.

Please note that all zeros in the selected range have been replaced with dashes.

Any of the above methods is easy. It is important to pay attention to the format of the cells after the replacement, if any actions will be performed on the data of the cells in the future. Remember to also encrypt your important Excel files to prevent unauthorized third parties from accessing them. Even if you forget the cipher, special programs will help you Excel password recovery.