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,870 comments

  1. GeeHii Keto

    Regards for this fantastic post, I am glad I observed this web site on yahoo.

  2. HeaterPro Space Heater

    Some genuinely marvellous work on behalf of the owner
    of this website, absolutely outstanding content material.

  3. Speakers

    The Ultra Bookshelf unreservedly stakes a claim
    as one of the best bookshelf audio system under $1,000.

  4. Patricia

    Hey there, I think your site might be having browser compatibility issues.
    When I look at your website in Safari, it looks fine but when opening in Internet Explorer,
    it has some overlapping. I just wanted to give you a quick heads up!
    Other then that, excellent blog!

  5. Jonelle

    Hi there every one, here every person is sharing such experience, thus it’s good to read this blog,
    and I used to visit this weblog all the time.

  6. Julio

    It’s really very complicated in this active life to listen news on Television, thus I simply use the web for that purpose, and obtain the newest news.

  7. Adolph

    Hi there all, here every person is sharing these kinds of experience, so it’s nice to read this weblog, and I used to go
    to see this webpage all the time.

  8. GeeHii Keto Pills

    What’s up everyone, it’s my first visit at this web site, and paragraph is in fact fruitful in favor of me, keep up posting these posts.

  9. Check Now

    I like what you guys are up also. Such smart work and reporting! Carry on the superb works guys I have incorporated you guys to my blogroll. I think it will improve the value of my website 🙂

  10. Marilyn

    Incredible! This blog looks just like my old one!
    It’s on a totally different subject but it has pretty much the same layout and
    design. Outstanding choice of colors!

  11. Juana

    This is my first time visit at here and i am actually impressed to read
    everthing at alone place.

  12. Sheldon

    Everyone loves what you guys are usually up too. Such clever work and exposure!
    Keep up the excellent works guys I’ve added you guys
    to my personal blogroll.

  13. Lesley

    I’m not that much of a online reader to be honest but your blogs
    really nice, keep it up! I’ll go ahead and bookmark your site to
    come back later. Cheers

  14. Francesco

    Incredible story there. What happened after? Take care!

  15. Cleveland

    My relatives every time say that I am wasting my time here at
    web, however I know I am getting experience every
    day by reading such pleasant posts.

  16. Charlotte

    No matter if some one searches for his essential thing, so he/she wants to be available that in detail, therefore
    that thing is maintained over here.

  17. Elena

    Great beat ! I wish to apprentice while you amend your website,
    how could i subscribe for a blog web site? The account aided me a acceptable deal.
    I had been a little bit acquainted of this your broadcast
    provided bright clear concept

  18. Mattress store in Denver

    I love what you guys tend to be up too. This kind of clever work and coverage!
    Keep up the fantastic works guys I’ve incorporated you guys to my personal blogroll.

  19. Aidan

    Hello there, I believe your blog might be having internet browser compatibility issues.
    Whenever I look at your site in Safari, it looks fine however, when opening in IE,
    it has some overlapping issues. I merely wanted to provide you with a quick heads
    up! Apart from that, excellent site!

  20. Beulah

    Unquestionably believe that which you stated. Your favorite justification seemed to be on the internet the easiest thing
    to be aware of. I say to you, I definitely get annoyed while people think about worries that they plainly don’t know about.
    You managed to hit the nail upon the top and defined out the whole thing without having side effect , people could take a signal.
    Will probably be back to get more. Thanks

  21. Brandie

    Everything is very open with a very clear clarification of the challenges.
    It was truly informative. Your site is very helpful.

    Many thanks for sharing!

  22. Kenneth

    Quality posts is the crucial to interest the viewers to visit
    the site, that’s what this web page is providing.

  23. Sophie

    Excellent way of telling, and fastidious paragraph to obtain facts concerning my presentation focus, which
    i am going to deliver in university.

  24. 2019 project

    It’s the best time to make a few plans for the long run and it’s time to be happy. I’ve learn this publish and if I may just I want to counsel you few fascinating issues or suggestions. Perhaps you can write subsequent articles relating to this article. I wish to learn even more issues about it!

  25. joker 388

    What’s up, I read your blogs on a regular basis.
    Your writing style is awesome, keep it up!

  26. Rosie

    Truly when someone doesn’t understand then its up to other visitors that they will assist, so here it happens.

  27. Mary

    Hi there, every time i used to check weblog posts here early in the
    daylight, as i like to find out more and more.

  28. Ken

    Generally I do not learn article on blogs, however I
    wish to say that this write-up very compelled me to check
    out and do so! Your writing style has been surprised me.
    Thanks, very great post.

Leave a Reply

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