How to remove 0 in Excel

You can watch a video tutorial here.

Excel is frequently used for calculations and has many ways in which to format numbers. When working with numbers and performing calculations, many times you will have cells that contain only zeroes. There may be cases where you either want to remove the zeroes completely or change the display so that you know which columns have zero values. For example, if you are using the AVERAGE() function to find an average value, it will consider cells that have zeroes in them. In this case, you have to remove the zero so that the average value is not affected. In other cases, you may want to change the display of zeroes so that you can focus on the cells that have non-zero values.

Option 1 – Change the display

Step 1 – Select the cells

• Select the cells for which the display is to be changed

Step 2 – Choose the comma-style format

• Go to Home > Number
• Click the Comma Style button
• The zeroes change to hyphens and decimal places are added

Step 3 – Remove the decimal places

• Click the Decrease Decimal button twice to remove the decimal places

Step 4 – Check the result

• Zeroes are replaced by hyphens so it is easier to see which cells have zeroes
• Only the display is changed and the zero is still included in the calculation of the average

Option 2 – Use Find & Replace

Step 1 – Open the Find & Replace box

• Go to Home > Editing
• Expand the Find & Select dropdown
• Select Replace

OR

• Press Ctrl+H

Step 2 – Enter the details

• Under Find what: type ‘0’
• Leave Replace with: blank
• Click Replace All

Step 3 – Acknowledge the change

• Click OK in the confirmation box
• Click Close  to close the Find & Replace box

Step 4 – Check the result

• The zeroes are removed 
• The computed average values change as the empty cells are not considered