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.

7,418 comments

  1. diamonds thus giving

    Tremendous issues here. I am very glad to peer your post.
    Thanks a lot and I’m looking forward to contact you.
    Will you please drop me a mail?

  2. idnpoker

    Great post. I used to be checking continuously this blog and I am inspired!
    Extremely useful information specifically the final section 🙂 I care for such information a lot.

    I was seeking this particular info for a
    very long time. Thank you and best of luck.

  3. bandar togel diskon terbesar

    Hi excellent website! Does running a blog like this take a large amount of work?
    I have very little expertise in coding but I was hoping to
    start my own blog in the near future. Anyways, if you have any suggestions or techniques for new blog owners please share.
    I understand this is off topic but I simply wanted to ask.
    Thank you!

  4. 바카라사이트

    I am no longer certain where you’re getting
    your information, but great topic. I must spend some time
    learning much more or figuring out more. Thank
    you for excellent information I used to be searching for this information for my
    mission.

  5. baby shark adventure ios cheats

    Hi! Quick question that’s entirely off topic. Do you know how
    to make your site mobile friendly? My website looks weird when viewing from my apple
    iphone. I’m trying to find a template or plugin that might be able to fix this problem.

    If you have any suggestions, please share. Thank you!

  6. Slot deposit pulsa

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get three e-mails with the same comment.
    Is there any way you can remove people from that service? Thank you!

  7. 샌즈 카지노

    Very energetic article, I enjoyed that bit. Will there be
    a part 2?

  8. Costa Rei appartamenti

    I used to be suggested this web site via my cousin.
    I’m no longer positive whether or not this submit is written by
    means of him as nobody else realize such distinct about my problem.
    You’re incredible! Thanks!

    Have a look at my web-site :: Costa Rei appartamenti

  9. judi slot uang asli

    Superb post however , I was wondering if you could write a litte
    more on this topic? I’d be very grateful if you could elaborate a little bit further.
    Kudos!

  10. silentnight mattress

    I really like what you guys are usually up too. This type
    of clever work and reporting! Keep uup the very good works guys
    I’ve incorporated you guys to mmy personal blogroll.

  11. diamond investements

    This post will assist the internet users for creating new blog or even a weblog from start to end.

  12. diamond clarity

    You ought to be a part of a contest for one of the most useful sites online.

    I’m going to recommend this blog!

  13. situs slot online

    Hi, Neat post. There is a problem with your
    web site in web explorer, would check this? IE nonetheless is the market chief and a large component to other folks will leave out your great writing due
    to this problem.

  14. https://demoslotjoker.net

    Hi it’s me, I am also visiting this website on a regular basis,
    this web site is truly pleasant and the viewers are
    actually sharing good thoughts.

  15. cake maker baking kitchen mod

    I think this is one of the most significant information for me.
    And i’m glad reading your article. But wanna remark on some general things, The web site style
    is great, the articles is really nice : D. Good job, cheers

  16. my tubi hack apk

    My partner and I stumbled over here from a different web address
    and thought I should check things out. I like what I see so
    now i am following you. Look forward to looking at your web page again.

  17. Aundrea

    This site was… how do you say it? Relevant!! Finally I’ve found something which helped me.
    Appreciate it!

  18. puzzle page crossword android cheats

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time
    a comment is added I get three emails with the same comment.
    Is there any way you can remove people from that service?
    Thanks a lot!

  19. here is how

    Simply wish to say your article is as amazing. The clarity on your publish is simply nice and that i can suppose you’re an expert on this subject.
    Fine along with your permission allow me to clutch your RSS feed to keep updated with forthcoming post.

    Thank you a million and please continue the enjoyable work.

  20. https://sites.google.com/view/thodienlanh/sua-chua-dien-lanh-ha-noi

    Hello, I think your site might be having browser compatibility issues.
    When I look at your blog site 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, great blog!

  21. covamSlopy

    С помощью ссылки https://2gis.ru/ivanovo/firm/70000001034942437 можно узнать расположение сервисного центра COREX. Также линк дает достаточно информации о режиме работы компании, предоставляет контакты, с помощью которых можно связаться со специалистами. Здесь также можно найти ссылки на социальные сети сервиса, оставить свой отзыв об опыте сотрудничества или прочитать отклики других пользователей. На соответствующей вкладке опубликована информация обо всех услугах, а также какими способами можно их оплатить.

  22. Zoila

    It’s perfect time to make a few plans for the future and
    it is time to be happy. I’ve read this put up and if I
    could I wish to counsel you some interesting things or suggestions.
    Perhaps you can write subsequent articles relating to
    this article. I desire to read even more issues about
    it!

  23. hydroxychloroquine side effects

    Good day! This is kind of off topic but I need some guidance from
    an established blog. Is it tough to set up your
    own blog? I’m not very techincal but I can figure things out
    pretty fast. I’m thinking about setting up my own but I’m not sure where to begin. Do you have any ideas or suggestions?
    Thanks http://hydroxychloroquined.online/

  24. disco ducks android cheats

    Hello there! This post could not be written much better!
    Looking at this article reminds me of my previous roommate!
    He always kept preaching about this. I will forward this article to him.
    Pretty sure he will have a very good read.
    I appreciate you for sharing!

  25. gold price live hacks

    Greetings! Very useful advice within this post! It’s the
    little changes that produce the biggest changes. Thanks for sharing!

  26. 우리카지노

    Undeniably believe that which you stated.
    Your favorite justification seemed to be on the net the
    easiest thing to be aware of. I say to you, I definitely
    get annoyed while people consider 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 can take a signal.
    Will probably be back to get more. Thanks

Leave a Reply

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