How to Add Trailing Zeros In Excel (3 Suitable Ways)

Trailing zeros in Excel are zeros that appear at the end of a number to keep a fixed format. For example, the number 7 can be shown as 7.00, or the number 123 can be displayed as 123000.

Normally, Excel removes extra zeros and only shows the basic number. If you type 12.50, Excel will display it as 12.5. But sometimes you may need those zeros to make your data look consistent, such as in product IDs, prices, or financial reports.

Excel has several ways to add trailing zeros. You can format the numbers directly, or use formulas to display them in the exact way you need.

In this article, we’ll learn simple methods to add trailing zeros in Excel with clear examples.

In the following dataset, we have a simple product list. Column A contains Product IDs, Column B shows the Product Names, and Column C will display the formatted result with trailing zeros.

Our goal is to display each Product ID as a 6-digit code with trailing zeros where needed.

One of the easiest ways to add trailing zeros is by combining the REPT and LEN functions. This method appends zeros to the end of your number until it reaches a fixed length.

Let’s apply this method to make all Product IDs six digits long.

Here’s how to do it:

➤ Open your dataset in Excel.
➤ Click on cell C2 and enter this formula:

➤ Press Enter. Excel will display the Product ID from A2 with trailing zeros added. For example: 1 becomes 100000.

➤ Drag the fill handle down to apply the formula to the rest of the rows.

If you want numbers to always display a certain number of decimal places, you can use the FIXED function. This adds trailing zeros after the decimal point, which is useful for prices, measurements, or financial data.

Here’s how to do it:

➤ Open your dataset in Excel.
➤ Click on cell C2 and enter the formula:

➤ Press Enter. Excel will display the number with exactly 2 decimal places. For example: 1 becomes 1.00.

➤ Drag the fill handle down to apply the formula to the rest of the rows.

If you want full flexibility to add trailing zeros to numbers in Excel, a VBA function is a powerful option. This method works for large datasets and allows you to set both the number and the total length, so zeros are added automatically at the end.

Here’s how to do apply this method:

Step 1: Create the Custom Function

➤ Open your dataset in Excel.
➤ Press  Alt  +  F11  to open the VBA editor.
➤ Go to Insert >> Module.

➤ Paste this code:

Function AddTrailingZeros(num As Double, totalLength As Integer) As String
    AddTrailingZeros = num & String(totalLength - Len(CStr(num)), "0")
End Function

Step 2: Apply the Function in Your Worksheet

➤ Press  Alt  +  Q  to return to Excel.
➤ Click on cell C2 and enter:

➤ Press Enter. Excel will display the number in A2 with trailing zeros to make it 6 digits long. For example: 1 becomes 100000.

➤ Drag the fill handle down to apply the formula to the rest of the rows.

Frequently Asked Questions

How do I add trailing zeros in Excel?

You can add trailing zeros combining the functions like REPT and LEN. For a certain number of decimal places, you can use the FIXED function. And for more flexibility, you can also create a custom VBA function. The method you choose depends on whether you want the result as text or as a number.

Can I add trailing zeros without changing the number to text?

No. Whenever you add zeros at the end of a number, Excel stores the result as text. Only Custom Number Formatting can display zeros without changing numeric values, but it can’t add true trailing zeros at the end.

How do I add trailing zeros at the end of a number?

To add zeros after a number, use:

=A2&REPT(“0”,6-LEN(A2))

Or use a custom VBA function for dynamic results across large datasets.

Wrapping Up

Adding trailing zeros in Excel is useful when you need consistent formatting for IDs, product codes, or decimal values. The methods we covered here help you add trailing zeros in different scenarios, whether for integers or decimals.

However, adding trailing zeros will convert the numbers into text, which ensures the zeros are displayed correctly at the end.

Choose the method that works best for your dataset. Using these techniques, you can make your Excel sheets look neat, professional, and easy to read.