The presence of Zeros in an Excel Data Field can make Histograms and other types of Graphs look inaccurate. In such cases, the solution is to replace Zeros with Blank, Dash or Text in Microsoft Excel.
However, you need to be aware that replacing Zeros with Blanks practically amounts to removing Data from cells with Zero values. Hence, when you remove Zeros in Microsoft Excel, all cells containing zeros will lose their Data and they will be treated as empty cells having no data in them.
Similarly, if you replace zeros with Dash (-) or Text in an Excel spreadsheet, you may encounter errors; if Cells with Dash or Text Field are being referenced in Formulas.
Related: How to Add Prefix or Suffix in Excel.
Replace Zeros With Blank in Excel
If you have clearly understood the likely impact of replacing Zeros with blanks in Microsoft Excel, you can go ahead and replace Zeros with Blank, Dash or any Text field in Excel.
1. Replace Zeros With Blank in Excel
You can follow the steps below to replace Zeros with Blanks in Microsoft Excel.
1. Select the Entire Data in which you want to replace Zeros with Blank cells.
2. Click on the Home tab > click on Find & Select in ‘Editing’ section and select the Replace option in the drop-down menu.
3. In the ‘Find and Replace’ dialog box, enter 0 in ‘Find what’ Field > leave the ‘Replace with‘ field empty (enter nothing in it) and click on Options.
4. In the expanded ‘Find and Replace‘ box, select Match entire cell contents option and click on Replace All button.
Once you click on ‘Replace All’ button, all the cells in your data having zeros will immediately become blank.
2. Replace Zeros with Dash in Excel
Another way of dealing with zero values in Excel is to replace them with dash (-). This can be achieved by using the Custom Number Formatting option as available in Microsoft Excel.
1. Select the Entire Data in which you want to replace zeros with dash.
2. Click on the Home tab > select Format option in ‘Cells’ group > click on Format Cells… in the drop-down menu.
3. In Format Cells dialog box, click on the Number tab and select Custom option in left-pane. In the right-pane, enter 0;-0;”–” in the ‘Type’ field.
4. Click on OK to save and implement this change.
Once you click on OK, Cells with 0 values will be showing a dash (–). Also, if you enter zero in any cell, it will be replaced with Dash (-).
3. Replace Zeros with Text in Excel
You can also replace zeros with any text field in Excel. For example, you can replace zeros with ‘NA’, ‘Not Available’ or any other text.
1. Select the Entire Data in which you want to replace zeros with Text.
2. Click on the Home tab > select Format option in ‘Cells’ group > click on Format Cells… in the drop-down menu.
3. In Format Cells dialog box, click on the Number tab and select Custom option in left-pane. In the right-pane, enter 0;-0;[Red]”NA” in the ‘Type’ field.
4. Click on OK to save and implement this change.
Once you click on OK, all the Cells with 0 values will be showing the text ‘NA’ in red colour.
Note: If you want to show ‘NA’ in regular colour, simply type 0;-0;”NA”.
4. Manually Find & Replace Zeros in Excel
In case you do not want to remove the zeros right away, you can first find zeros in the data field, select all zeros and deal with them as required.
1. Select the Entire Data having Zero values
2. Click on the Home tab > click on Find & Select in ‘Editing’ section and select the Replace option in the drop-down menu.
3. In ‘Find and Replace’ dialog box, enter 0 in ‘Find what’ Field > leave the ‘Replace with’ field empty (enter nothing in it) and click on Options.
4. In the expanded ‘Find and Replace’ box, select Match entire cell contents option and click on Find All button.
5. Once you click on Find All, the cells with zero values will become highlighted.
6. Hold the Control Key and Press the A Key to select all the cells that have 0 in them.
Now, that you have selected all the cells having 0 values, you can delete the zeros, replace zeros with any character or highlight cells having zeros.