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.

12,533 comments

  1. インターカジノ 出金

    Good day! I know this is kinda off topic but I was
    wondering which blog platform are you using for this website?
    I’m getting fed up of WordPress because I’ve had issues with hackers and I’m looking at options for another platform.
    I would be great if you could point me in the direction of a good platform.

  2. online casino

    This paragraph will assist the internet viewers for setting up
    new blog or even a weblog from start to end.

  3. Titanium Glasses

    Wow, this paragraph is fastidious, my sister is analyzing these things,
    therefore I am going to inform her.

  4. ‎カジノ

    Why people still use to read news papers when in this technological world everything is available
    on net?

  5. web site

    Hi there, this weekend is nice designed for me, because this tijme i am reading thks great informative paragraph hede at my
    home.
    web site

  6. Kathryne Norseworthy

    When I to begin with commented, I clicked the “Notify me when new responses are additional” checkbox and now each time a remark is extra I get many emails While using the very same remark. Is there any way you’ll be able to take away individuals from that support? Many thanks.

  7. waykujeory

    Заработок на играх стал реальностью и ссылка https://games-hyip.ru/ каждый может узнать о том, как его найти. На странице опубликован список лучших онлайн игр, в которых можно вывести деньги на реальные кошельки. Список не только дает возможность узнать об этих играх, но и предоставляет их короткую характеристику. Статья дает информацию о бонусах, акциях, а также кошельках – на которые можно выводить деньги. Таким образом, можно выбрать наиболее комфортную игру и насладиться досугом с финансовой выгодой.

  8. Berniece

    Great blog. Great deals of beneficial information here.

  9. slot online

    Hi, i think that i saw you visited my web site thus i came to “return the favor”.I’m trying to find things to enhance my site!I suppose its
    ok to use a few of your ideas!!

  10. liquidity lock

    I read this article completely on the topic of the difference of most
    up-to-date and preceding technologies, it’s amazing article.

  11. aurogra 100

    Hey there! I simply wish to give you a big thumbs up
    for the excellent information you’ve got right here on this post.

    I’ll be coming back to your site for more soon. http://herreramedical.org/aurogra

  12. シンフォギア スロット

    I’m not sure exactly why but this site is loading extremely slow for me.
    Is anyone else having this issue or is it a problem on my end?
    I’ll check back later on and see if the problem still exists.

  13. graham automobile

    Greate post. Keep writing such kind of info on your page.
    Im really impressed by your site.
    Hello there, You’ve done a fantastic job. I’ll certainly digg it and personally recommend to my friends.
    I am sure they will be benefited from this website.

  14. papers

    Today, while I was at work, my cousin stole my iPad
    and tested to see if it can survive a twenty five foot drop, just so she can be a youtube
    sensation. My iPad is now broken and she has 83 views.

    I know this is entirely off topic but I had to share it with someone!

  15. figis gallery

    I feel this is among the most vital info for me.
    And i’m glad reading your article. But wanna commentary
    on few general issues, The web site taste is ideal, the articles is truly excellent : D.
    Good activity, cheers

  16. video poker

    What’s up to every one, the contents existing at this web site are genuinely remarkable
    for people knowledge, well, keep up the nice work fellows.

  17. 더킹카지노 코드

    If some one wants expert view about blogging then i recommend him/her to pay a quick
    visit this website, Keep up the nice job.

  18. 10cric live casino

    Aw, this was an exceptionally nice post. Spending some time and
    actual effort to generate a great article… but what can I say… I hesitate a
    whole lot and never seem to get anything done.

  19. 007카지노

    Pretty nice post. I simply stumbled upon your blog and wished to mention that I’ve
    truly loved browsing your weblog posts. After all I’ll be
    subscribing to your rss feed and I am hoping you write again very soon!

  20. 퍼스트카지노

    Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that
    automatically tweet my newest twitter updates. I’ve been looking for
    a plug-in like this for quite some time and was hoping maybe
    you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new
    updates.

  21. live roulette

    Your means of telling the whole thing in this paragraph is genuinely good,
    every one be able to easily understand it, Thanks a lot.

Leave a Reply

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