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.

1,293 comments

  1. joanniesjabber.com

    Some times its a pain in the ass to read what website owners wrote but this web site is very user friendly!

  2. Rating

    Howdy very nice website!! Guy .. Beautiful ..
    Wonderful .. I will bookmark your web site and take the feeds also?
    I’m glad to search out so many useful information right here in the post, we
    want develop extra strategies in this regard, thank you for sharing.
    . . . . .

    My web site Rating

  3. Safety Rules

    I have been browsing online more than 2 hours today, yet I never found any interesting article like
    yours. It is pretty worth enough for me.

    Personally, if all web owners and bloggers made good content
    as you did, the internet will be much more useful than ever before.

    Here is my website Safety Rules

  4. Read more

    Hello there I am so grateful I found your blog, I really found
    you by accident, while I was looking on Digg for something else, Regardless I am here now and would just
    like to say thanks for a tremendous post and a all round
    entertaining blog (I also love the theme/design), I don’t
    have time to Read more it all at the minute but I have book-marked it
    and also included your RSS feeds, so when I have
    time I will be back to read a lot more, Please do keep up
    the awesome work.

  5. Retail shop

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

    Feel free to visit my web-site: Retail shop

  6. love is in the Air

    Hey! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying
    to get my blog to rank for some targeted keywords but I’m not seeing very good success.
    If you know of any please share. Many thanks!

    My web-site – love is in the Air

  7. More info

    Why users still make use of to read news papers when in this technological world the whole thing is existing on net?

    Here is my site :: More info

  8. healthycbdhub.com

    Hi there very nice web site!! Guy .. Beautiful ..
    Amazing .. I will bookmark your blog and take the feeds additionally?
    I’m happy to seek out a lot of helpful information right here
    within the put up, we’d like work out extra strategies
    in this regard, thanks for sharing. . . . . .

    my blog :: Contact Us; healthycbdhub.com,

  9. pest free

    you’re actually a excellent webmaster. The site loading velocity is incredible.
    It sort of feels that you are doing any distinctive trick.
    Furthermore, The contents are masterpiece. you’ve done a wonderful job in this topic!

    Here is my web site: pest free

  10. 일산요정

    Hi, I do think this is a great blog. I stumbledupon it 😉
    I’m going to come back yet again since i have saved as a favorite it.
    Money and freedom is the best way to change, may you be rich and continue
    to guide other people.

  11. 일산명월관

    I think this is one of the most important info for me.
    And i am glad reading your article. But should remark on few general things,
    The website style is ideal, the articles is really great
    : D. Good job, cheers

  12. 일산명월관

    Generally I don’t learn post on blogs, but I wish to say that this write-up
    very compelled me to try and do so! Your writing taste
    has been surprised me. Thanks, quite great article.

  13. 일산명월관

    Wow, that’s what I was looking for, what a
    material! present here at this webpage, thanks admin of this
    website.

  14. 일산명월관

    Hmm it seems like your site ate my first comment (it was super long) so I guess I’ll just sum it up what I submitted 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 suggestions for beginner blog writers?
    I’d definitely appreciate it.

  15. 일산요정

    hi!,I love your writing so a lot! share we communicate more approximately
    your post on AOL? I require a specialist on this space to unravel my problem.
    May be that’s you! Having a look ahead to peer you.

  16. 일산요정

    I could not resist commenting. Well written!

  17. 일산명월관

    It is not my first time to pay a visit this website, i am visiting this web site dailly and
    get fastidious information from here daily.

  18. 일산명월관

    Tremendous things here. I am very happy to look your article.
    Thanks a lot and I’m looking ahead to touch you. Will you kindly drop me a e-mail?

  19. discuss

    Write more, thats all I have to say. Literally, it seems as though
    you relied on the video to make your point. You definitely know what youre talking about, why waste your intelligence on just posting
    videos to your site when you could be giving us something informative to read?

  20. 먹튀검증업체

    It’s truly a nice and useful piece of info. I am
    satisfied that you simply shared this useful info with us.
    Please keep us up to date like this. Thank you for sharing.

    Check out my page; 먹튀검증업체

  21. hlb379

    Saved as a favorite, I like your website!

  22. togel online sgp

    I have been surfing on-line greater than 3 hours as of late, but I by no means
    found any interesting article like yours. It is beautiful value
    enough for me. In my opinion, if all website owners and bloggers made
    good content material as you probably did, the internet can be
    a lot more helpful than ever before.

  23. chwilówki elbląg

    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

    Also visit my homepage chwilówki elbląg

  24. Tianzong9.com

    An intriguing discussion is worth comment. There’s no doubt that that you should write more about this subject matter,
    it may not be a taboo subject but usually people don’t talk about these topics.

    To the next! Many thanks!!

    Also visit my web site :: Tianzong9.com

  25. luxury headphones

    The only thing I don’t like about this headphone is battery backup will not be what we expected.
    With a actually wonderful pure sound expertise, the HD 650’s are
    specifically crafted for lengthy but snug music periods so that you don’t need to sacrifice consolation while concentrating
    on your work. Despite all that detail and speed,
    all of it comes together as naturally airy, light and pure.
    While transients were crisp, the Susvara averted that
    edge and push employed by some of it is opponents – the type of drive that makes you marvel at the
    level of “detail” for 2-three minutes before you get a splitting headache.
    It has a very nice tone that trades a small amount of detail for smoothness and warmth.
    But Tranya, like others, includes giant and small buds you may swap out
    to secure the very best match. Nothing out of place on a
    USD 600 product, except this one has an extra zero at the end.
    Another level to notice is that these cans supply nearly zero isolation. I preferred these
    cans related through balanced cables and used these
    all through the evaluate period. Tough call right here.
    I already get grief from different audiophiles over 4 digit worth tag cables.

  26. สูตรบาคาร่า

    Howdy! Do you know if they make any plugins to assist with Search Engine Optimization?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good success.
    If you know of any please share. Appreciate it!

Leave a Reply

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