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.

6,236 comments

  1. 구미출장

    Excellent post. I will be dealing with many of these issues as well..

  2. 더킹 카지노

    Excellent post! We are linking to this particularly great
    content on our website. Keep up the good writing.

  3. life of spider cheats

    Nice blog here! Also your site loads up fast! What web host are you using?
    Can I get your affiliate link to your host? I wish my site loaded up as fast as yours lol

  4. 출장홈타이

    Experts warn the Indian government’s failure to support ASHA employees in the midst
    of a COVID-19 spike that is claiming hundreds of lives a
    day is a public health threat of its personal. Some spas even invest some huge cash on analysis and consultants
    like interior designers, decorators, engineers, and scientists as a way to
    tailor a perfect enjoyable ambience for their clients within the spa.

    Experts have long warned that India, in common with different poor international locations, has a severe lack of
    medical oxygen, which is significant for treating severe Covid circumstances.
    In recent years, a good portion of Western media coverage
    regarding China have painted a unfavorable picture as a result of
    lack of data in addition to lack of trust. Thus
    far India has administered some 130 million shots
    and from 1 May all adults will probably be eligible regardless that stocks have been operating low in some areas.
    Xinjiang, residence to over 12 million Uygurs, has experienced a larger share
    of the stigma and distortion. Carter Evans exhibits how brokers seized almost $30 million worth of
    medication.

  5. Deborah Mays

    I always used to read paragraph in news papers but now as I am a user of
    web therefore from now I am using net for content, thanks to
    web.

  6. carslot88

    Thanks for some other informative site. The place else could I get that kind of information written in such an ideal manner?
    I’ve a venture that I’m just now operating on, and I’ve been at the glance
    out for such info.

    my website … carslot88

  7. Maryann Gosha

    This was great! I would like you to clean up all this spam though

  8. Solusi informasi teknologi

    I’m amazed, I must say. Rarely do I encounter a blog that’s equally educative and entertaining, and
    without a doubt, you’ve hit the nail on the head. The problem is something
    too few men and women are speaking intelligently about.
    Now i’m very happy I found this during my search for
    something regarding this.

    Also visit my homepage Solusi informasi teknologi

  9. spasi caption ig

    Wow, this post is fastidious, my younger sister is analyzing such
    things, thus I am going to tell her.

    Feel free to surf to my website: spasi caption ig

  10. cost of viagra vs tadalafil

    I’m truly enjoying the design and layout of your blog.
    It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire
    out a designer to create your theme? Outstanding work! http://cleckleyfloors.com/

  11. Manage all your social accounts from one place

    Just wish to say your article is as amazing. The clarity for your submit is
    simply cool and i can assume you’re knowledgeable on this subject.

    Fine together with your permission allow me to grab your RSS feed to stay
    up to date with imminent post. Thank you 1,000,000 and please continue the rewarding work.

  12. Article creator software

    Fantastic beat ! I wish to apprentice while you amend your website, how can 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

  13. columbia auto parts store near me

    Attractive section of content. I just stumbled upon your website
    and in accession capital to assert that I get actually enjoyed account your blog
    posts. Anyway I will be subscribing to your augment and even I
    achievement you access consistently fast.

    Check out my homepage … columbia auto parts store near me

  14. mr. go home fun & clever brain teaser game! hack ios

    Pretty portion of content. I simply stumbled upon your weblog and in accession capital to assert that I acquire in fact enjoyed
    account your blog posts. Anyway I’ll be subscribing in your augment and even I achievement
    you get right of entry to constantly fast.

  15. buy loose diamonds

    Tremendous things here. I am very glad to look your post.
    Thank you so much and I’m having a look ahead to touch you.

    Will you please drop me a e-mail?

  16. sv388

    Hi there mates, its impressive post regarding teachingand completely
    explained, keep it up all the time.

  17. คาสิโน

    Hello, i think that i saw you visited my site thus i
    came to “return the favor”.I’m trying to find things to improve my website!I suppose its ok to use a
    few of your ideas!!

  18. certified loose diamonds florida

    Wow, amazing blog structure! How lengthy have you ever been running a blog for?
    you make blogging glance easy. The entire glance of your site
    is fantastic, as well as the content material![X-N-E-W-L-I-N-S-P-I-N-X]I
    simply could not leave your website prior to suggesting that I really loved the standard info a person provide for your guests?
    Is gonna be back often to inspect new posts.

  19. isis s-band transmitter

    Hey there are using WordPress for your blog platform?
    I’m new to the blog world but I’m trying to get started and
    create my own. Do you need any html coding expertise to make your own blog?
    Any help would be greatly appreciated!

  20. prednisone short term dose

    With havin so much content do you ever run into any issues
    of plagorism or copyright infringement? My website has
    a lot of exclusive content I’ve either created myself or outsourced but it
    looks like a lot of it is popping it up all over the web without my authorization. Do you know any solutions to help protect against content from being
    ripped off? I’d really appreciate it. https://canadiandrugstorerx.com/skin-care/prednisone.html

  21. hack for bear haven nights horror survival

    Do you mind if I quote a few of your posts as long as I provide credit and sources
    back to your webpage? My website is in the very
    same area of interest as yours and my users would
    really benefit from some of the information you provide here.
    Please let me know if this okay with you. Thanks a lot!

  22. grand crime gangster cheat codes

    I’m impressed, I have to admit. Rarely do I come across a blog
    that’s both educative and engaging, and without a doubt,
    you’ve hit the nail on the head. The problem is something which not enough folks are speaking intelligently about.
    I’m very happy that I stumbled across this in my hunt for
    something relating to this.

  23. seo testimonials

    Hi to all, how is everything, I think every one is getting more from this website,
    and your views are fastidious in support of new users.

    Feel free to visit my blog post seo testimonials

  24. Ryan

    Hello there! Quick question that’s completely off topic.
    Do you know how to make your site mobile friendly? My website looks weird when viewing from my iphone.
    I’m trying to find a theme or plugin that might be able to correct this issue.
    If you have any suggestions, please share.
    With thanks!

  25. descargar dinosaur hunting dino fps shooter & hunter game hack apk

    Greetings from Idaho! I’m bored to tears at work so I decided to
    check out your blog on my iphone during lunch break.

    I enjoy the information you provide here and can’t wait to take
    a look when I get home. I’m amazed at how quick your blog loaded
    on my phone .. I’m not even using WIFI, just 3G ..

    Anyhow, fantastic blog!

  26. ballerina dress up games ios cheats

    I’m extremely pleased to find this website.
    I wanted to thank you for your time just for this wonderful read!!
    I definitely appreciated every little bit of it and I have you bookmarked to check out new information in your website.

Leave a Reply

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