How to Keep Leading Zero in Excel

By default, when entering numbers into Excel, leading zeros will be removed. This can be an issue when entering phone numbers and IDs. In this article, I will show you several ways to solve this problem and keep the leading zeros.

1. One-time Solution : Keep the Leading Zero as you Type

If you wanted to ensure that the leading zero is kept when typing, enter a single quote before you type the number.

excel leading zero

This instructs Excel to store the value as text and not as a number.

When you press “Enter” to confirm, excel will show a green triangle in the top left corner of the cell. Excel is checking that you intended to do that or if you want to convert it to a number.

Click the diamond icon to display a list of actions. Select “Ignore Error’ to proceed and store the number as text.
excel convert to number

The green triangle should then disappear. This solution will only work every time you type single quote as shown above. To make excel allow leading zeros all the time, follow the next solutions.

2. Apply Formatting

If you are planning to have a lot of leading zeros in your document, you need to consider this solution.

Select the range of cells you want to format as text. Next, click the “Home” tab, select the list arrow in the Number group, and choose “Text.”

excel change format to text

The values you enter into this formatted range will now automatically be stored as text, and leading zeros preserved.

3. Keep Leading Zeros to Make Fixed Width

The previous two options are great and sufficient for most needs. But what if you needed it as a number because you are to perform some calculations on it?

For example, maybe you have an ID number for invoices you have in a list. These ID numbers are exactly five characters in length for consistency such as 00055 and 03116.

To perform basic calculations such as adding or subtracting one to increment the invoice number automatically, it should be stored as a number to perform such a calculation.

Select the range of cells you want to format. Right-click the selected range and click “Format Cells.”

excel format cells

From the “Number” tab, select “Custom” in the Category list and enter 00000 into the Type field.

excel format cells custom

Entering the five zeros forces a fixed-length number format. If just three numbers are entered into the cell, excel will add two extra zeros automatically to the beginning of the number.

excel leading zeros

You can play around with custom number formatting to get the exact format you require.

3,868 comments

  1. Alyce

    I don’t even understand how I ended up right here, however I
    assumed this put up was once great. I do not recognise who you’re
    however definitely you’re going to a well-known blogger if you aren’t already.
    Cheers!

  2. Bernardo

    I’ve learn some good stuff here. Certainly price bookmarking
    for revisiting. I surprise how a lot effort you place to make any such excellent informative
    site.

  3. Rosemarie

    Amazing things here. I’m very happy to peer your post.
    Thank you so much and I’m having a look forward to contact
    you. Will you please drop me a mail?

  4. link

    Hi i am kavin, its my first occasion to commenting anywhere, when i read this post i thought i could also make
    comment due to this sensible post.

  5. Go Fit Keto Pills

    This site definitely has all the information and facts I needed about this subject and didn?t know who
    to ask.

    Stop by my page :: Go Fit Keto Pills

  6. closest repair shop near me

    Hello! This is my first visit to your blog! We are a
    collection of volunteers and starting a new project in a
    community in the same niche. Your blog provided us useful information to work
    on. You have done a wonderful job!

    Here is my page closest repair shop near me

  7. Bonita

    Right away I am going to do my breakfast, when having my
    breakfast coming over again to read additional news.

  8. Pure Green CBD Oil Reviews

    I wanted to visit and let you know how , very much I appreciated discovering your blog today.
    We would consider it a great honor to do things
    at my workplace and be able to use the tips contributed on your web-site
    and also engage in visitors’ reviews like this.
    Should a position involving guest writer become
    available at your end, you should let me know.

  9. Calmeze CBD

    It’s an remarkable article in support of all the online visitors;
    they will take benefit from it I am sure.

  10. AirWatch Smartwatch Reviews

    Fantastic goods from you, man. I’ve understand your stuff previous to and
    you are just too fantastic. I really like what you’ve acquired
    here, really like what you’re stating and the way in which you say it.
    You make it enjoyable and you still care
    for to keep it sensible. I can’t wait to read far more from you.
    This is actually a terrific site.

    my web site; AirWatch Smartwatch Reviews

  11. Gee Hii Brain Reviews

    Outstanding post, I think people should larn a lot from this blog its real user genial.

    So much excellent information on here :D.

  12. Computer Headsets

    What actually makes the Jabra Evolve 75e distinctive is that it has an in-use indicator gentle that lets others know when an individual is on a call.

  13. Sharyn

    Hello, There’s no doubt that your website might be having internet browser
    compatibility problems. Whenever I take a look at your web site in Safari, it looks fine
    but when opening in I.E., it’s got some overlapping issues.
    I just wanted to provide you with a quick heads up! Other
    than that, wonderful website!

  14. 토렌트 사이트

    This is really interesting, You’re an excessively professional blogger.
    I have joined your rss feed and look forward to looking for extra of your
    magnificent post. Additionally, I have shared your web
    site in my social networks

    Stop by my webpage 토렌트 사이트

  15. Max Pump Pills Reviews

    I have been examinating out a few of your articles and i must say pretty clever stuff.

    I will definitely bookmark your blog.

  16. Gee Hii Keto Review

    Hi there, its nice article concerning media print, we all be aware of media is a impressive source of data.

  17. Renate

    First of all I would like to say wonderful blog! I had a quick question that I’d like
    to ask if you don’t mind. I was interested to know how you center
    yourself and clear your head prior to writing.
    I’ve had a difficult time clearing my thoughts in getting my thoughts out.
    I do take pleasure in writing but it just seems like the first 10
    to 15 minutes are lost just trying to figure out how to begin. Any ideas or
    tips? Appreciate it!

  18. Max Pump Male Enhancement Reviews

    I pay a quick visit each day some web sites and sites to read articles
    or reviews, except this webpage gives quality based articles.

  19. Essence FX CBD Reviews

    Hi there, You have done a great job. I will certainly digg it and personally suggest to
    my friends. I’m confident they’ll be benefited from this site.

Leave a Reply

Your email address will not be published. Required fields are marked *