In basic, the presence of zeros in Excel's data area can make histograms and various types of charts appear inaccurate. Below you will find the steps to replace zeros with clean content, Dash or text in Excel.
Methods to replace zeros with blanks in Excel
Before following the steps to replace zeros with appropriate values in Excel, you should know that replacing zeros with appropriate values is the same as deleting information from cells that contain zero values.
After removing zeros in Excel, all cells containing zeros will lose their information and will be treated as empty cells containing no information.
When replacing zeros with a dash (-) or text content in Excel, you may encounter errors if the cells with a dash or text content area are referenced in a specific formula.
With this understood, let's try the steps to change the zeros with the appropriate area, dash or textual content in Excel.
1. Change zeros with blanks in Excel
Follow the steps below to replace zeros with blank spaces in Excel.
1. Choose Full Information for which you want to replace the Zeros with clean cells.
2. Push the button Residence tab > click on Search and choose in the "Improvements" section and choose the Edit in the drop-down menu.
3. In the “Discover and Edit” dialog box, enter 0 in the “Find out what” area > delete the “Change with” area emptiness (do not enter anything in it) and press The options.
4. In the expanded “Discover and change” field, choose… Corresponds to the complete content of the cells possibility and click on Change everything button.
When you click the “Change All” button, all cells in your data that have zeros will instantly become blank.
2. Change zeros with Dash in Excel
Another way to deal with zero values in Excel is to replace the zeros with dash (-). You can do this by using the custom amount format function that exists in Excel.
1. Choose Full Information for which you want to change the zeros with dash.
2. Push the button Residence tab > choose Format possibility in the “Cells” group > click Format the cells… in the drop-down menu.
3. In the Format Cells dialog box, click the button Quantity and selected Personalized in the left panel. In the right panel, enter 0;-0 ;»-« in the “Type” field.
4. Click on OK to save a lot and implement this change.
When you click OK, cells with 0 values will be displayed with a dash (-). Also, if you happen to enter a zero in a cell, it will probably be changed to a dash (-).
3. Change zeros to text content in Excel
You can also change zeros with any text content field in Excel. For example, you can change zeros with “NA”, “None”, or any other text content.
1. Choose Full Information when you want to replace zeros with text content.
2. Push the button Residence tab > choose Format possibility in the “Cells” group > click Formatting cells… in the drop-down menu.
3. In the Format Cells dialog box, click the button Quantity and selected Personalized in the left panel. In the right panel, enter 0;-0 ;[Red]»ND» in the “Type” field.
4. Click on OK to save a lot and implement this change.
When you click OK, all cells with 0 values will display the text “NA” in purple.
Word : If you want to display "NA" in a common shade, just sort 0;-0; «NA.
4. Finding zeros in Excel
If you don't need to remove zeros instantly, you can first uncover zeros in the information area, select all zeros, and remove them if necessary.
1. Choose the complete set Information with null values
2. Push the button Residence tab > click on Search and choose in the "Improvements" section and choose the Edit in the drop-down menu.
3. In the “Discover and Edit” dialog box, enter 0 in the “Find out what” area > delete the “Change with” area emptiness (do not enter anything in it) and press The options.
4. In the expanded “Discover and change” field, choose… Corresponds to the complete content of the cells possibility and click on More Information button.
5. When you click Reveal All, cells with zero values are highlighted.
6. Keep the Management key and press the button Key A to select all cells that contain 0.
Now that you have selected all the cells with 0 values, you can remove the zeros, replace the zeros with any character, or highlight the cells with zeros.
- Methods to find duplicates in Excel using conditional formatting
- Methods to create a pivot table in Excel
My name is Javier Chirinos and I am passionate about technology. Ever since I can remember, I have been interested in computers and video games, and that passion has turned into a job.
I have been publishing about technology and gadgets on the Internet for over 15 years, especially in mundobytes.com
I am also an expert in online marketing and communication and have knowledge in WordPress development.