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. Payday loans

    This excellent website truly has all the info I wanted concerning this subject and didn’t know who to ask.

  2. uncork wine bottle

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

  3. 365 mobile

    Hello just wanted to give you a quick heads up. The text in your post seem to be running off the screen in Ie.
    I’m not sure if this is a formatting issue or something to do with web browser compatibility but
    I figured I’d post to let you know. The layout look great though!
    Hope you get the problem fixed soon. Many thanks

  4. Titanium Glasses

    I’m really enjoying the design and layout of
    your site. 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?
    Superb work!

  5. 코인 카지노

    Excellent article. Keep posting such kind of information on your page.
    Im really impressed by your blog.
    Hey there, You have performed an excellent
    job. I’ll certainly digg it and for my part suggest to my friends.
    I’m sure they will be benefited from this website.

  6. honey dispenser

    I know this if off topic but I’m looking into starting my own blog
    and was curious what all is required to get set
    up? I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very internet savvy so I’m not 100% sure.
    Any recommendations or advice would be greatly appreciated.
    Appreciate it

  7. 더킹카지노

    I love your blog.. very nice colors & theme. Did you make this website yourself
    or did you hire someone to do it for you? Plz respond as I’m looking to create my own blog and would like to know
    where u got this from. thanks

  8. ネットカジノ

    Heya this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code
    with HTML. I’m starting a blog soon but have no coding expertise so
    I wanted to get advice from someone with experience.

    Any help would be greatly appreciated!

  9. 벳365 배당

    Attractive component to content. I just stumbled upon your web site and in accession capital to say that I get in fact loved account your blog posts.

    Any way I will be subscribing on your augment and even I achievement you
    get admission to persistently quickly.

  10. 우리카지노

    If you would like to get much from this post then you
    have to apply these strategies to your won web site.

  11. no deposit bonus

    Hello there! Do you know if they make any plugins to protect against
    hackers? I’m kinda paranoid about losing everything I’ve worked
    hard on. Any recommendations?

  12. honorary law degree

    Since the admin of this web page is working, no uncertainty very rapidly it will be
    well-known, due to its quality contents.

  13. カジノ オンライン

    Marvelous, what a webpage it is! This weblog gives helpful information to us, keep it up.

  14. high cotton

    Hello! This post could not be written any better! Reading
    this post reminds me of my good old room mate! He always kept chatting about this.
    I will forward this post to him. Pretty sure he will have
    a good read. Many thanks for sharing!

  15. 인터넷바카라

    I was very happy to find this site. I wanted to thank you for ones time due to this
    fantastic read!! I definitely really liked every little bit of it and i also have you bookmarked to check out new stuff in your blog.

  16. 바카라사이트

    Hi, I do believe this is an excellent blog.

    I stumbledupon it 😉 I will revisit yet again since i have book marked it.
    Money and freedom is the greatest way to change,
    may you be rich and continue to help others.

  17. 더킹 카지노

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

  18. vocelli pizza

    Its like you read my thoughts! You seem to know a lot approximately
    this, like you wrote the e-book in it or something. I believe
    that you can do with a few p.c. to pressure the message house a bit, but
    instead of that, this is wonderful blog. A fantastic read.
    I’ll certainly be back.

  19. 좋은 머리를 얻기 나의 potnas 소녀

    Thanks , I have just been looking for info about this topic for
    ages and yours is the best I have discovered till now.
    However, what concerning the conclusion? Are you certain about the
    source?

  20. 22bet app

    Right here is the right webpage for anyone who would like to understand this topic.
    You realize a whole lot its almost tough to argue
    with you (not that I actually will need to…HaHa). You certainly put a fresh spin on a topic which
    has been discussed for a long time. Excellent stuff, just great!

  21. Titanium ore

    It’s going to be end of mine day, however before end I am reading this wonderful piece of writing to increase my experience.

  22. 카지노사이트

    What’s up to every body, it’s my first go to see
    of this website; this website consists of amazing and in fact good material in favor of
    readers.

  23. ポーカー

    hello there and thank you for your information – I
    have certainly picked up anything new from right here. I did however expertise several technical issues using this web
    site, as I experienced to reload the site many times previous to I could get it to load correctly.
    I had been wondering if your web host is OK?
    Not that I am complaining, but sluggish loading instances times will very frequently affect your placement
    in google and can damage your high-quality score if
    ads and marketing with Adwords. Anyway I’m adding this
    RSS to my email and could look out for a lot more of your respective interesting content.
    Ensure that you update this again very soon.

  24. 나의 호스트

    Have you ever considered about including a little bit more than just your articles?

    I mean, what you say is fundamental and everything.
    Nevertheless just imagine if you added some great photos or
    videos to give your posts more, “pop”! Your content is excellent but with pics and clips, this site could
    undeniably be one of the very best in its niche. Superb blog!

  25. electronic supply

    First off I want to say great blog! I had
    a quick question that I’d like to ask if you do not
    mind. I was interested to know how you center yourself and clear your mind prior to writing.
    I’ve had difficulty clearing my mind in getting my thoughts out there.
    I do enjoy writing however it just seems like the first 10 to 15 minutes tend to be wasted simply just trying
    to figure out how to begin. Any suggestions or hints?
    Thank you!

  26. 우리카지노

    Undeniably believe that which you said. Your favorite reason appeared to be on the web the easiest thing to be aware of.
    I say to you, I certainly get irked at the same time as folks think about issues that they just don’t recognize about.
    You controlled to hit the nail upon the highest and also outlined
    out the entire thing with no need side-effects , other folks
    can take a signal. Will likely be again to get more. Thanks

  27. casumo

    Its not my first time to go to see this website, i am
    browsing this web site dailly and get nice information from here every day.

Leave a Reply

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