How to Add a Zero in Front of a Number in Excel

Excel automatically removes leading zeros from numbers because it treats them as numerical values. However, there are many situations where leading zeros are important, such as phone numbers, employee IDs, product codes, and account numbers. Learning how to add a zero in front of a number in Excel helps you display your data correctly without losing important formatting.

1. Table of Contents

   Toggle

   Format the Cells as Text

One of the easiest ways to keep a zero at the beginning of a number is to format the cells as text before entering the data.

Excel will then treat the entry as text rather than a number.

• Select the cells
• Go to the Home tab
• Choose Text from the Number Format menu

2. Type an Apostrophe Before the Number

You can also enter an apostrophe (‘) before the number.

The apostrophe will not appear in the cell, but Excel will keep the leading zero.

• Click the cell
• Type an apostrophe first
• Enter the number with the leading zero

3. Use Custom Number Formatting

If you want all numbers to display with a specific number of digits, use a custom format.

This method preserves the numeric value while displaying leading zeros.

• Select the cells
• Right-click and choose Format Cells
• Select Custom and enter a format such as 00000

4. Use the TEXT Function

The TEXT function can convert numbers into a format that includes leading zeros.

This is useful when working with formulas.

• Use the formula =TEXT(A1,”00000″)
• Replace A1 with the relevant cell reference
• Adjust the number of zeros as needed

5. Apply the Format to Multiple Cells

Once you have chosen a method, apply it consistently across all relevant cells.

This ensures your data remains uniform and easy to read.

• Select the entire data range
• Apply the chosen format
• Check the results for consistency

6. Verify the Displayed Values

After adding leading zeros, review your spreadsheet to ensure the numbers appear correctly.

This helps prevent formatting errors.

• Check several entries
• Confirm zeros are displayed properly
• Save your worksheet

Also Read: How to Add Speaker Notes in PowerPoint