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.

10,939 comments

  1. Titanium earrings

    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 website to come back later on. Many thanks

  2. web site

    This is my first time go to see at here and i am in fact happy to read all at alone place.

    web site

  3. marriage annulment

    This post will assist the internet visitors for building up
    new web site or even a blog from start to end.

  4. letou8868

    Thanks on your marvelous posting! I actually enjoyed reading it,
    you happen to be a great author. I will be sure to bookmark your blog and definitely will come back in the future.
    I want to encourage you continue your great work, have a nice
    weekend!

  5. atx auctions

    I loved as much as you will receive carried out right here.

    The sketch is attractive, your authored material stylish.
    nonetheless, you command get got an shakiness over that you
    wish be delivering the following. unwell unquestionably come further formerly again since exactly the same nearly very
    often inside case you shield this hike.

  6. game bai net

    I am not sure where you’re getting your info, but good
    topic. I needs to spend some time learning more or understanding
    more. Thanks for great info I was looking for this information for
    my mission.

  7. 우리카지노

    Unquestionably believe that which you said. Your favorite justification seemed to be on the web the simplest thing to be aware of.
    I say to you, I certainly get annoyed while people think about worries that they just don’t know about.
    You managed to hit the nail upon the top and defined out the whole
    thing without having side effect , people can take
    a signal. Will likely be back to get more. Thanks

  8. 솔레어카지노

    My partner and I absolutely love your blog and find a lot of
    your post’s to be precisely what I’m looking for. Does one offer guest writers to write content for you personally?

    I wouldn’t mind writing a post or elaborating on some of the subjects you write regarding here.
    Again, awesome website!

  9. live casino games

    Hi my family member! I want to say that this article
    is amazing, nice written and include approximately
    all important infos. I’d like to see more posts like this .

  10. 007카지노

    It’s awesome for me to have a website, which is helpful
    for my know-how. thanks admin

  11. 솔레어카지노

    Thanks for your personal marvelous posting! I seriously enjoyed reading it,
    you happen to be a great author.I will always bookmark your blog and definitely will come back down the road.
    I want to encourage you to ultimately continue your great posts, have a
    nice morning!

  12. Bit casino

    hey there and thank you for your info – I have certainly picked up something new from
    right here. I did however expertise several technical points
    using this web site, since I experienced to reload the website a lot of times previous to I could
    get it to load correctly. I had been wondering if your hosting is OK?

    Not that I am complaining, but slow loading instances times will often affect your placement in google and could
    damage your high quality score if ads and marketing with Adwords.
    Anyway I am adding this RSS to my e-mail and
    can look out for much more of your respective fascinating content.

    Make sure you update this again very soon.

  13. london stock exchange

    I do not even know how I ended up here, but I thought this post was
    good. I do not know who you are but definitely you’re going to a famous blogger if you
    are not already 😉 Cheers!

  14. ลัก กี้ คลับ

    Hmm it appears like your blog ate my first comment (it was
    super long) so I guess I’ll just sum it up what I wrote
    and say, I’m thoroughly enjoying your blog.
    I too am an aspiring blog blogger but I’m still new to the whole thing.

    Do you have any recommendations for beginner blog writers?
    I’d really appreciate it.

  15. ket qua xo so jackpot

    Good day! Would you mind if I share your blog with my
    myspace group? There’s a lot of people that I think would really appreciate your content.
    Please let me know. Thanks

  16. Venom 2 Let There Be Carnage full movie online free

    You can use your open public library. Your open public library could have Venom 2 Let There
    Be Carnage film that one could try out at no cost.
    Get in touch with your local local library to
    discover the things they carry. Venom 2 Let There
    Be Carnage full movie online free

  17. jungle raja casino login

    Hello to every single one, it’s really a fastidious for me to pay a quick visit this web page, it
    contains priceless Information.

  18. Sbotop

    Hello friends, fastidious piece of writing and good arguments commented here, I am actually enjoying by these.

  19. haitian cake

    This site certainly has all of the info I wanted concerning this subject and
    didn’t know who to ask.

  20. laos tourism

    Your style is really unique in comparison to other folks I
    have read stuff from. Thank you for posting when you have the opportunity, Guess I will just bookmark this web site.

  21. buffet restaurants

    I’m not that much of a internet reader to be honest but your
    sites really nice, keep it up! I’ll go ahead and
    bookmark your site to come back in the future. Many thanks

  22. 우리카지노

    Very shortly this site will be famous among all blogging
    and site-building visitors, due to it’s nice articles or reviews

  23. 솔레어카지노

    Wonderful goods from you, man. I have understand your stuff previous to and you are just
    too magnificent. I actually like what you’ve acquired here,
    certainly like what you are saying and the way in which you say
    it. You make it entertaining and you still care for
    to keep it smart. I can not wait to read much more from you.
    This is really a wonderful website.

  24. online casinos

    My partner and I stumbled over here coming from a different
    page and thought I might as well check things out. I like what I see so now i am
    following you. Look forward to looking into your web page repeatedly.

  25. 10cric login

    Good day! This post could not be written any better!
    Reading through this post reminds me of my old room mate!
    He always kept chatting about this. I will forward this page to him.
    Fairly certain he will have a good read. Thank you for
    sharing!

  26. islands restaurant

    Thankfulness to my father who informed me on the topic of this weblog, this web site is truly remarkable.

  27. インディアンポーカー

    Excellent goods from you, man. I have understand your stuff previous to and you are
    just extremely magnificent. I actually like
    what you have acquired here, certainly like what you’re stating and the way in which you say it.
    You make it entertaining and you still care for to keep it smart.
    I can not wait to read far more from you.
    This is actually a terrific web site.

Leave a Reply

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