Sometimes the presence of zeros in Excel data field can make histograms and other type of graphs look inaccurate. You will find below the steps to replace zeros with blank, Dash or Text in Excel.
How to Replace Zeros With Blank in Excel
Before going ahead with the steps to replace zeros with blank in Excel, you need to understand that replacing zeros with blank amounts to removing the data from cells with zero values.
When you remove zeros in Excel, all the cells containing zeros will lose their data and they will be treated as empty cells having no data in them.
If you replace zeros with Dash (-) or a Text in Excel, you may sometimes encounter errors, if cells with Dash or Text Field are referenced in certain formulas.
With this understanding, let us go ahead and take a look at the steps to replace zeros with blank, dash or text field in Excel.
1. Replace Zeros With Blank in Excel
Follow the steps below to replace zeros with blanks in 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 ‘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 zeros with dash (-). This can be achieved using custom number formatting option as available in 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. Find 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.
