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

  1. 샌즈카지노

    Hmm is anyone else having problems with
    the images on this blog loading? I’m trying to
    find out if its a problem on my end or if it’s the blog.

    Any feed-back would be greatly appreciated.

  2. 카지노사이트

    Hi would you mind stating which blog platform you’re using?
    I’m planning to start my own blog soon but I’m having
    a tough time making a decision between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design and style seems different then most blogs and I’m
    looking for something completely unique. P.S My
    apologies for being off-topic but I had to ask!

  3. betting online

    Hi there, I do think your website may be having browser compatibility problems.
    Whenever I look at your website in Safari,
    it looks fine but when opening in Internet Explorer, it’s got some overlapping issues.

    I merely wanted to provide you with a quick heads up! Apart from that,
    excellent website!

  4. 늙은 낯선 사람 빌어 먹을 젊은 속이는 입

    Hi there! This post couldn’t be written much better! Reading through this post reminds me of my previous
    roommate! He always kept talking about this. I’ll forward this
    post to him. Pretty sure he’s going to have a good read.
    I appreciate you for sharing!

  5. Titanium

    Have you ever thought about including a little bit
    more than just your articles? I mean, what you say is important and all.
    But just imagine if you added some great images or video clips to
    give your posts more, “pop”! Your content is excellent
    but with images and video clips, this website could certainly be one of the greatest in its niche.
    Excellent blog!

  6. 메리트 카지노 등록 코드

    Wonderful blog! I found it while browsing on Yahoo News.
    Do you have any suggestions on how to get listed in Yahoo
    News? I’ve been trying for a while but I never seem to get there!

    Thanks

  7. 비윈

    I will right away clutch your rss as I can not in finding
    your e-mail subscription link or e-newsletter service. Do you have any?
    Please allow me recognise so that I may subscribe. Thanks.

  8. calafate planta medicinal

    Greetings! Very helpful advice in this particular article!
    It is the little changes that make the most important changes.
    Many thanks for sharing!

  9. betway registration

    Hi! 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 suggestions?

  10. henry rose perfume

    I believe everything said was very logical. But, what about
    this? suppose you composed a catchier post title?
    I ain’t saying your information is not good., but suppose you added a title
    that makes people desire more? I mean How to Keep Leading Zero in Excel – Dryan Christoph Personal Website is kinda vanilla.
    You should glance at Yahoo’s front page and note how
    they create post titles to get people to click.
    You might add a video or a related picture or two to get people excited about everything’ve got to say.
    Just my opinion, it might make your blog a little livelier.

  11. ทางเข้าrb88

    Your style is very unique compared to other people I have read stuff from.
    Thanks for posting when you’ve got the opportunity,
    Guess I will just book mark this blog.

  12. online betting sites

    Sweet blog! I found it while browsing on Yahoo News.
    Do you have any suggestions on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there!
    Thanks

  13. 007카지노

    Appreciating the hard work you put into your site and in depth information you provide.
    It’s good to come across a blog every once in a while that isn’t the same old rehashed material.

    Wonderful read! I’ve saved your site and I’m including your RSS feeds to my Google account.

  14. Casino Trực Tuyến

    You can definitely see your enthusiasm in the work you write.
    The arena hopes for even more passionate writers
    such as you who are not afraid to mention how they believe.
    At all times go after your heart.

  15. 원제 nasty bitches assholes

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

  16. cuca를 살펴보기

    Oh my goodness! Impressive article dude! Thanks, However I am experiencing difficulties with your RSS.
    I don’t understand why I can’t join it. Is there anybody else getting similar RSS problems?

    Anyone who knows the answer will you kindly respond?

    Thanks!!

  17. 더나인카지노

    Its not my first time to go to see this web site, i am browsing this site
    dailly and obtain good facts from here every day.

  18. bingo games

    What’s up colleagues, nice piece of writing and good arguments
    commented at this place, I am in fact enjoying by these.

  19. godaddy customer service

    Thanks for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your further write ups thank you once again.

  20. ベラ ジョン カジノ ルーレット

    Howdy, i read your blog from time to time and i
    own a similar one and i was just wondering if you get a lot of spam comments?
    If so how do you reduce it, any plugin or anything you can recommend?
    I get so much lately it’s driving me crazy so any support is very much
    appreciated.

  21. 샌즈카지노

    Hello there I am so thrilled I found your
    site, I really found you by error, while I was looking on Aol for something else, Regardless I am
    here now and would just like to say many thanks for a fantastic post and a all round thrilling blog (I also love the theme/design),
    I don’t have time to read it all at the moment but
    I have bookmarked it and also added your RSS feeds, so when I have
    time I will be back to read a lot more, Please do keep up the awesome
    b.

  22. Live Casino House

    What’s up mates, pleasant paragraph and good arguments commented
    here, I am actually enjoying by these.

  23. golf putters

    My relatives every time say that I am wasting my
    time here at web, but I know I am getting knowledge every day by reading thes pleasant content.

  24. 브랜드명 상품명 red toes footjob hannah grace

    I do not even know how I ended up here, but I assumed
    this post was once great. I do not know who you’re however
    certainly you’re going to a famous blogger in case
    you aren’t already. Cheers!

  25. เกม ไพ่ คู่

    I do believe all of the ideas you’ve offered on your post.
    They’re really convincing and will certainly work.

    Nonetheless, the posts are very short for novices.
    May you please extend them a little from subsequent time?
    Thank you for the post.

  26. she got the big black titties 근처 오락거리

    Right here is the perfect site for anybody who wants to understand
    this topic. You understand a whole lot its almost tough to
    argue with you (not that I really would want to…HaHa).
    You certainly put a new spin on a topic that has been written about for ages.
    Excellent stuff, just great!

  27. sửa máy lạnh

    Good article. I will be going through many of these issues as well..

  28. 뜨거운 키스

    It’s an awesome post designed for all the web viewers;
    they will get benefit from it I am sure.

  29. bets

    At this time it looks like Expression Engine is the preferred blogging platform available right now.
    (from what I’ve read) Is that what you’re using on your blog?

Leave a Reply

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