Sometimes, the presence or absence of zeros in the Excel data fields may cause histograms and other graphs appear distorted. The following are steps to replace zeros using text, dash or blank in Excel.
How to replace zeros with Blanks Excel? Excel
Before you begin the steps needed to replace zeros by blank in Excel it is important to be aware that replacing zeros with blank numbers is equivalent to taking the data from cells that contain zero values.
If you remove zeros from Excel the cells that contain zeros will lose data and will be considered empty cells that have no data within the cells.
If you substitute zeros with Dash (–) or Text in Excel, you could encounter errors in the event that cells with Dash and Text Field are referenced in specific formulas.
So we can now proceed and review the steps for replacing zeros by blank, dash or text fields in Excel.
1. Replace Zeros with Blanks in Excel
The steps are below for replacing zeros by Blanks within Excel.
1. Select the Entire Data that you would like to replace zeros using blank cells.
2. Click on the Home tab, then click Find and Select under the section ‘Editing’ and select Replace from the menu dropdown.
3. In ‘Find and Replace dialog box, enter zero in the ‘Find what Field’ and leave the ‘Replace field empty (enter the number 0) and then click Options.
4. In the expanded “Find and Replace box, click on the”Match entire cell contents” and click the Replace All option.
When you click the “Replace All the cells in your data that have zeros and click ‘Replace All’, all the cells in your database that contain zeros will instantly become blank.
2. Replace Zeros by Dash in Excel
Another approach to deal with Zero values within Excel is to substitute zeros with dashes. (-). This can be accomplished by using the customized number formatting options found in Excel.
1. Select the Entire Data that you would like to replace zeros with dashes.
2. Click on the Home tab > choose the”Format” in the “Cells” group. Click on the Format cell… on the drop-down menu.
3. In Format Cells dialog box, click the Number tab and choose the Custom in the left-pane. In the right-pane type the numbers 0;0 ;”-” within the field “Type.
4. Click on OK to save and apply this modification.
After you click OK, cells with zero values will show Dash (-). If you type the value zero within any of your cells the cell will be replaced by Dash (-).
3. Replace zeros with text in Excel
You can also substitute zeros using any text field that is available in Excel. For instance, you could replace zeros by replacing “NA”, “Not Available and any additional text.
1. Select the Entire Data that you wish to replace zeros by Text.
2. Click on the Home tab, select”Format” in the “Cells” group. Click on the Format cell… from the dropdown menu.
3. In Format Cells dialog box, select the Number tab, and then select Custom in the left-pane. In the right-pane, type 0;-0;[Red]”NA” into the field ‘Type.
4. Click on OK to save the change and then implement the modification.
After you click OK, all the cells with zero values will display the word ‘NA’ in red.
NOTE: If you want to display “NA” in normal colour, just use 0;-0;”NA”.
4. Locate Zeros within Excel
If you don’t want to get rid of the zeros immediately You can find zeros within the data field Select all zeros, then handle them as you wish.
1. Select the Entire Data with zero values
2. Click on the Home tab and then click Find and Select in the ‘Editing’ section and select Replace from the menu dropdown.
3. In ‘Find and Replace dialog box, enter the number 0 in the ‘Find What Field’ and leave the ‘Replace the field blank (enter no value in it) and click Options.
4. In the expanded “Find and Replace box, choose the”Match all cell contents” and then click the Find All button.
5. Once you click on Find All, the cells with no values will be highlighted.
6. Hold the Control Key and press the A Key , to highlight all cells that have zero in them.
Nowthat you’ve selected all cells with zero values, you are able to erase the zeros, or substitute zeros with any other characters, or highlight cells with zeros.
