How to remove trailing zeros in Microsoft Excel

In Microsoft Excel, trailing zeros refer to the zeros that appear after the decimal point in a number. For example, the number 12.5000 has three trailing zeros. While these zeros do not affect the value of the number, they can be displayed unnecessarily and make the data harder to read or compare. 

In this tutorial, we will learn how to remove trailing zeros in Microsoft Excel. In Excel, one can use various methods to remove trailing zeros. These methods include utilizing the “Format Cells” option, the LEFT function, or utilizing the “Decrease Decimal” button in the Home tab.

For instance, we have a list that shows the prices of some products. The prices are formatted in a way that they have leading zeros. Our aim is to remove the trailing zeros.

Method 1: Applying a Custom Format to Remove Trailing Zeros

Step 1 – Choose the Cells 

• Choose the cells that contain numbers with trailing zeros.

Step 2 – Open the Format Cells Dialog Box

• Open the “Format Cells” dialog box by pressing the CTRL+1 keys.

Step 3 – Select the “Custom” Option

• Select the option labeled “Custom”.

Step 4 – Enter “0.###” in the Type Field

• Enter “0.###” in the Type field.
• Hit the OK button in the dialog box.

Method 2: Utilizing the “Decrease Decimal” Button

Step 1 – Choose the Cell

• Choose the cell that contains the number with trailing zeros.

Step 2 – Locate the “Decrease Decimal” Button

• Locate the “Decrease Decimal” button in the “Numbers” section of the Home tab.
• Strike the “Decrease Decimal” button until all the trailing zeros are removed.

Step 3 – Remove the Trailing Zeros From Each Number

• Follow the above-mentioned steps to remove trailing zeros from each number.

Method 3 – Utilizing the LEFT Function to Remove Trailing Zeros 

If the significant figures are consistent across multiple numbers, the LEFT function can effectively remove them. However, if the number of significant figures varies, it may not be feasible to remove them using the LEFT function. Therefore, we will use a different dataset that contains numbers with consistent significant figures i.e. 5 significant figures, to demonstrate the method of removing them.

Step 1 – Choose an Empty Cell

• Choose an Empty cell in which you want to remove the trailing zeros from a number.

Step 2 – Enter the LEFT Function

• Enter the LEFT function in the cell.
• The structure of the function will be: 

            LEFT(B2,5) 

• Where B2 is the cell that contains a number with trailing zeros.
• The second parameter i.e. 5 is the number of figures to be returned by the LEFT function.
• Strike the Enter key.

Step 3 – Utilize Autofill to Remove Trailing Zeros From Each Cell

• Utilize Autofill to remove trailing zeros from each cell.