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.

11,867 comments

  1. Online Casino

    What’s up, I check your blogs daily. Your humoristic style is awesome, keep up the good work!

  2. Bwin

    With havin so much content do you ever run into
    any problems of plagorism or copyright infringement?
    My blog has a lot of exclusive content I’ve either authored myself or outsourced but
    it appears a lot of it is popping it up all over the web without
    my agreement. Do you know any ways to help reduce content from being ripped off?
    I’d certainly appreciate it.

  3. 더킹카지노

    Hi Dear, are you in fact visiting this website daily, if so
    then you will without doubt take fastidious knowledge.

  4. 카지노사이트

    Wow, amazing blog layout! How long have you been blogging for?
    you made blogging look easy. The overall look of your web site is
    magnificent, let alone the content!

  5. game bài đổi thưởng

    This is very interesting, You’re a very skilled blogger.
    I have joined your feed and look forward to seeking more of your wonderful
    post. Also, I’ve shared your website in my social networks!

  6. baby bouncer

    Hello there! I just wish to offer you a big thumbs up for your great info you have got here on this post.
    I am returning to your web site for more soon.

  7. sm 카지노

    Hello! I know this is kind of off-topic but I needed
    to ask. Does managing a well-established website such as
    yours take a large amount of work? I’m completely new to running a blog however I do write in my journal daily.
    I’d like to start a blog so I can easily share my own experience and views online.
    Please let me know if you have any ideas or tips for new aspiring bloggers.
    Appreciate it!

  8. 윌리엄힐

    Good post. I learn something new and challenging on blogs I stumbleupon every day.
    It will always be useful to read articles from other writers
    and use something from other web sites.

  9. bet365

    hey there and thank you for your info – I’ve definitely
    picked up something new from right here. I did however expertise a few technical points using this web site,
    as I experienced to reload the website many times previous to
    I could get it to load correctly. I had been wondering if
    your web hosting is OK? Not that I’m complaining, but sluggish loading instances times
    will sometimes affect your placement in google and could damage your high-quality
    score if ads and marketing with Adwords. Well I’m adding this RSS to my email and can look out for much more of your respective exciting
    content. Ensure that you update this again soon.

  10. 베트웨이

    Greate post. Keep posting such kind of info on your site.
    Im really impressed by your blog.
    Hi there, You have done an incredible job. I’ll definitely digg it and individually recommend to my friends.
    I am sure they will be benefited from this site.

  11. 완벽한 busty girlfriend loves cum deep inside milaluv 4k 60fps

    I’m extremely inspired together with your writing talents and also with the format in your blog.
    Is that this a paid topic or did you customize
    it your self? Anyway keep up the excellent high quality writing, it is uncommon to look
    a great blog like this one nowadays..

  12. 빅카지노

    I am sure this article has touched all the internet visitors,
    its really really fastidious article on building up new blog.

  13. オンラインカジノとは

    Excellent site you have here.. It’s hard to find high quality
    writing like yours nowadays. I really appreciate individuals like you!
    Take care!!

  14. 더킹카지노

    Ahaa, its nice discussion concerning this post here at this weblog, I have read
    all that, so now me also commenting here.

  15. ルーレット

    Pretty great post. I simply stumbled upon your blog and wished
    to say that I have truly enjoyed surfing around your weblog posts.
    After all I’ll be subscribing in your rss feed and
    I’m hoping you write again soon!

  16. 카지노 룰렛

    Wonderful work! This is the type of info that are meant to be shared around the internet.
    Shame on Google for not positioning this post
    higher! Come on over and discuss with my website . Thanks =)

  17. 메리트카지노

    Good day I am so excited I found your website, I really found you by accident, while I was looking on Aol
    for something else, Nonetheless I am here now and would just
    like to say cheers for a marvelous post and a all round thrilling
    blog (I also love the theme/design), I don’t have time to go through it all
    at the moment but I have bookmarked it and also added in your RSS feeds, so
    when I have time I will be back to read much more,
    Please do keep up the superb work.

  18. 빅카지노

    Touche. Outstanding arguments. Keep up the good effort.

  19. 젖어가기

    I need to to thank you for this good read!! I absolutely loved every little bit of it.

    I’ve got you book-marked to look at new stuff you post…

  20. 제왕카지노

    What’s up, this weekend is nice for me, because this point in time i am reading this impressive educational post here at my home.

  21. blackjack rules

    Awesome things here. I am very satisfied to look your article.
    Thank you a lot and I am taking a look forward to contact you.
    Will you kindly drop me a mail?

  22. 파라오 게임

    I think this is among the most important info for me.
    And i’m glad reading your article. But wanna remark on few general things,
    The web site style is great, the articles is really excellent : D.
    Good job, cheers

  23. slot

    If some one wishes to be updated with most up-to-date technologies therefore he must be go to
    see this site and be up to date every day.

  24. 뜨거운 소녀 솔로

    Ahaa, its pleasant dialogue about this piece of writing at this place at this website, I have read all that, so at this time me also commenting here.

  25. 007 카지노 쿠폰

    Hello just wanted to give you a quick heads up. The words in your
    post seem to be running off the screen in Opera.

    I’m not sure if this is a format issue or something to do with browser compatibility but I thought I’d post to let you know.
    The design look great though! Hope you get the problem fixed soon. Thanks

  26. 골드 카지노

    Very good info. Lucky me I came across your site by chance (stumbleupon).
    I’ve bookmarked it for later!

Leave a Reply

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