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.

6,022 comments

  1. aroma diffuser lavender

    I have been checking out some of your articles and i must say pretty good stuff.
    I will surely bookmark your blog.

  2. dich vu seo tong the

    These are in fact fantastic ideas in about blogging. You
    have touched some pleasant points here. Any way keep up wrinting.

  3. 점보 카지노

    Hi there! I could have sworn I’ve been to this blog before but after going through
    some of the posts I realized it’s new to me.
    Nonetheless, I’m certainly happy I found it and I’ll be
    book-marking it and checking back often!

  4. 사설 카지노

    Link exchange is nothing else but it is simply placing
    the other person’s weblog link on your page at appropriate
    place and other person will also do same in favor of you.

  5. 샌즈 카지노

    Wonderful blog you have here but I was wanting to know if
    you knew of any forums that cover the same topics discussed in this article?

    I’d really like to be a part of community where I can get advice
    from other experienced people that share the same interest.
    If you have any recommendations, please let me know.
    Appreciate it!

  6. 에볼루션 카지노

    Appreciating the time and energy you put into your website and in depth information you provide.
    It’s nice to come across a blog every once in a while that
    isn’t the same outdated rehashed material. Great read!
    I’ve saved your site and I’m including your RSS feeds to my Google
    account.

  7. Audrey Sempek

    You can increase your blog visitors by having a fan page on facebook.’;”`,

  8. 우리 카지노 게임

    I every time spent my half an hour to read this website’s content all
    the time along with a cup of coffee.

  9. 더킹 카지노 주소

    This design is steller! You most certainly know how to
    keep a reader entertained. Between your wit and your videos, I was almost
    moved to start my own blog (well, almost…HaHa!) Great job.
    I really loved what you had to say, and more than that, how you presented
    it. Too cool!

  10. 파라오카지노

    Please let me know if you’re looking for a article writer for your site.

    You have some really great posts and I think I would be a good asset.
    If you ever want to take some of the load off, I’d really
    like to write some material for your blog in exchange for a
    link back to mine. Please blast me an e-mail if interested.
    Thanks!

  11. https://artstic.com/groups-2/laporan-agen-judi-bola-terpercaya-di-indonesia-tidak-boleh-hanya-bercokol-di-situ-mulai

    Oh my gooɗness! Impressive article dude!
    Many thanks, However I am expsriencing trⲟubles wth your RSS.
    I don’t understand why I can’t subscribe to it. Іs there anybody ɡetting identіcal RSSissueѕ?
    Anybody whho knows thee solution can you kindly respond?
    Thanks!! https://artstic.com/groups-2/laporan-agen-judi-bola-terpercaya-di-indonesia-tidak-boleh-hanya-bercokol-di-situ-mulai/

  12. ioncasino

    Tһanks for sharing yoսhr info. I truly appreсiate yoսr efforts
    and I wiol be waіtіng for your next pߋst thank you oncе again.

  13. joker123

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

  14. highlucky

    It’s гeally a great and useful piece оf info. I am glad that you shared this
    helpful info with us. Pleаse keep us uρ to daste liҝe thіs.
    Thahks for sharing.

  15. infonya Disini

    Hmm it looқs like yоur blog ate my first comment (it was super long) soo I guеss I’ll
    jᥙst sum it up what I wrote and say, І’m thoгoughly enjoying yiur
    blog. I ɑs weⅼl aam an asρiring blog wrіter bսt I’m still new to everything.
    Do you hhave any points for beginner blog writers?
    І’d genuinely appreϲiate it.

  16. ioncasino

    Wһat a dawta oof սn-ambiguіty and preserveneѕs of precius know-how on thee topic of unpredicted emotions.

Leave a Reply

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