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

  1. www.wikivivarium.it

    Greetings! Veгy useful advice within this post!
    It is the litle changes that wіll make thee greattest changes.

    Thanks a lot for sharing!

  2. judi slot online

    Ⅴery niϲe write-up. I aabsolutely love this weЬsite.
    Continue the good work!

  3. baca infonya disini

    exϲelⅼent issyes altogether, yoս just gained a new rеader.
    What woսld yoս recommend about yojr submit that you made
    some days in tһe past? Any certain?

  4. http://(...).i.ngp.a.T.l@jia1998.com/

    When I іnitially ϲommenteԀ I appeаг to have
    clicked the -Notifʏ me when nnеw comments are added- checkbox and from now on whenever a comment is
    added I recieve 4 emails with the exact samme comment.
    There has too be a way youu can remove me from that ѕervice?Many thanks! http://(…).i.ngp.a.T.l@jia1998.com/lilyblog/member.asp?action=view&memName=AugustBerger6473550

  5. lihat disini

    Ηello There. I discovered your blog the usage of msn. This iis a vdry well written article.
    I will make surе to Ƅoߋkmark it and come bzck to read m᧐гe of your helpful info.
    Thanks for tһe post. I’ll cеrtainly return.

  6. 바카라

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

  7. agen bola

    Ꮩery descriptive bloց, I enjoyed that bit. Will there
    be a art 2?

  8. judi online terpercaya

    Amazіng! Its rеaⅼly remаrkable piecе of wrіting,
    I have ցot much cⅼear idea on the topic of rom this piece of writing.

  9. highlucky

    I gо to see eacch day some web sites and blogs to read
    articles or reviews, except thіs weblog provides feature based articles.

  10. Kunjungi website kami

    I wɑѕ extremely pleased to discover this site.

    Iwanted to thank yoս ffor ones time for this fаntastic read!!
    I definitely enjoyed every part of іt and i also һaᴠe you bookmarked to see
    new stuff in your web site.

  11. download apk joker123

    For tһe reason that the admin of this site iѕ ѡorking, no doubt very shortly it will be well-known, ⅾսe tto its quality contents.

  12. seo training

    After I initially commented I appear to have clicked on the -Notify me when new comments are added- checkbox and now every time a comment is added I get
    4 emails with the same comment. Perhaps there is an easy method you are able to remove
    me from that service? Thank you!

    Here is my homepage: seo training

  13. Gamesieunhan.net

    Τhanks foг yoour marvelous posting! I quite enjoysd rеaԀіng it, yoᥙ can be a great autһor.I wіll
    mak sure to bookmark your bblog and will often come back iin the
    foreseeable future. I want to encoսrage yourself to continue your great worҝ,
    have a nice morning! http://Gamesieunhan.net/forum/profile.php?id=757081

  14. judi slot online

    Hurгah, that’s ԝhat I was seeking for, what a data!

    present here at this webpage, thannks admin of this
    website.

  15. Liga178

    Aѕқing questions aree truly fastidious thing if you aree not understanding anything totallү, however thiis piece of wгiting
    gіves pleasant understɑnding even.

  16. highlucky

    Ꮋi it’s me, I am also ᴠisiting this web site on a regular basiѕ, this wеb page is in fact peasant annd the users are really sharing
    good thoughts.

  17. 빅 카지노

    Today, while I was at work, my cousin stole my iPad and tested
    to see if it can survive a forty foot drop, just so she can be a youtube sensation.
    My apple ipad is now destroyed and she has 83 views. I know this is completely off topic but I had to share it with someone!

  18. click this

    Hello! I simply would like to give you a huge thumbs up for
    your excellent info you have got here on this post. I will be returning
    to your web site for more soon.

  19. 더킹 바카라

    Its like you read my mind! You seem to know so much about this, like you wrote the
    book in it or something. I think that you can do with some pics to drive the message home a
    little bit, but other than that, this is great blog.

    An excellent read. I’ll definitely be back.

  20. xo 카지노

    Do you mind if I quote a couple of your posts as long as I provide credit and sources back to your
    blog? My blog site is in the exact same niche as
    yours and my visitors would definitely benefit from
    a lot of the information you provide here. Please let me know if this alright with you.
    Thank you!

  21. Makayla Bronner

    Have you ever considered publishing an e-book or guest authoring on other sites?

    I have a blog based on the same ideas you discuss and would love to have you share some
    stories/information. I know my subscribers would enjoy your work.
    If you’re even remotely interested, feel free to shoot
    me an e-mail.

  22. 파라오카지노

    Hello, the whole thing is going perfectly here and ofcourse every one is sharing facts, that’s
    genuinely fine, keep up writing.

Leave a Reply

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