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

  1. カジノ ベット

    Excellent post. I was checking continuously this blog and I’m impressed!

    Very useful information specifically the last part 🙂 I care for
    such information a lot. I was seeking this particular info for a very long time.

    Thank you and good luck.

  2. cannabis promo

    I really like what you guys tend to be up too. This kind of clever
    work and reporting! Keep up the awesome works guys I’ve
    incorporated you guys to my own blogroll.

  3. 바카라 사이트

    Ahaa, its fastidious discussion concerning this piece of
    writing here at this web site, I have read all that, so now me also commenting at this place.

  4. 더킹카지노

    Hello would you mind letting me know which webhost
    you’re using? I’ve loaded your blog in 3 completely different
    web browsers and I must say this blog loads a lot faster then most.
    Can you suggest a good web hosting provider at a reasonable price?

    Cheers, I appreciate it!

  5. bet365 online sports

    If some one wishes expert view regarding blogging then i
    recommend him/her to pay a visit this web site, Keep up the
    pleasant job.

  6. sports betting sites

    I think this is one of the most vital information for me.
    And i am glad reading your article. But should remark on few general things,
    The site style is wonderful, the articles is really great : D.
    Good job, cheers

  7. 0-9]

    I am not positive where you are getting your information,
    but great topic. I must spend a while learning much more or working out more.

    Thanks for fantastic info I was on the lookout for this info for my mission.

  8. 빌어 먹을 나의 gfs bestfriend

    What’s Taking place i’m new to this, I stumbled upon this I’ve found It absolutely useful and it has helped
    me out loads. I am hoping to contribute & assist other users like its aided me.
    Great job.

  9. 넷마블 포커

    These are really fantastic ideas in on the topic of blogging.
    You have touched some good points here. Any way
    keep up wrinting.

  10. bingo wild hacked apk

    Hello there, I discovered your website by way of Google at the same time as looking for
    a related topic, your site came up, it looks great.

    I have bookmarked it in my google bookmarks.
    Hello there, simply turned into alert to your blog thru Google, and found that it’s
    truly informative. I’m going to watch out for brussels.
    I’ll appreciate for those who proceed this in future.

    Many people shall be benefited from your writing.
    Cheers!

  11. random[a-z

    Hello There. I found your blog the use of msn. That is an extremely well
    written article. I’ll be sure to bookmark it and return to read extra
    of your useful information. Thanks for the post.

    I will definitely return.

  12. male nurse

    This excellent website really has all of the information and facts I
    needed about this subject and didn’t know who to ask.

  13. เกมยิง ออนไลน์

    Fantastic beat ! I would like to apprentice while
    you amend your site, how could 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
    offered bright clear concept

  14. Darmowe porno

    I know this site presents quality based content and other data, is there any other site which provides these kinds of information in quality?

  15. Casino-X

    Hi there, I found your website via Google whilst looking for a similar matter, your site
    came up, it seems great. I have bookmarked it in my google bookmarks.

    Hello there, just turned into aware of your blog thru Google, and located that it’s really informative.

    I’m going to be careful for brussels. I will be grateful if
    you proceed this in future. Many people might be
    benefited from your writing. Cheers!

  16. Websites

    Remarkable! Its actually amazing article, I have got much clear idea on the
    topic of from this post.

  17. random[a-z

    I’m gone to tell my little brother, that he should also pay a quick visit this
    blog on regular basis to take updated from latest news.

  18. forum warga jogja

    Thank you for the auspicious writeup. It in fact was a amusement account
    it. Look advanced to more added agreeable from you!
    By the way, how can we communicate?

    Also visit my page: forum warga jogja

  19. post

    Hello, all is going nicely here and ofcourse every one is sharing data, that’s
    genuinely good, keep up writing.

  20. cờ tướng úp

    Hey there 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 figured I’d post to let you know.
    The style and design look great though! Hope you get the problem
    resolved soon. Thanks

  21. Med spa Nashville

    This is my first time go to see at here and i am really impressed
    to read all at one place.

  22. Kareen Eifler

    Your new valuable vital details imply much an individual like me and very a lot more to my Business office workers. With thanks; from Absolutely everyone of us.

  23. 더나인카지노

    Thanks for the good writeup. It in fact used
    to be a leisure account it. Glance complicated to far introduced agreeable from you!

    By the way, how could we be in contact?

  24. Anotepad.com

    Hi there! I just wanted to ask if you ever have
    any issues with hackers? My last blog (wordpress) was hacked and I ended up losing a few
    months of hard work due to no data backup. Do you have any solutions to protect against hackers?

    my web-site – 안전놀이터 (Anotepad.com)

Leave a Reply

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