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.

1,293 comments

  1. دانلود آهنگ جدید

    وب سایت های زیادی هستند که در زمینه انتشار موزیک های ایرانی و
    خارجی فعالیت می کنند، اما بسیاری از
    آن ها پس از مدتی دچار مشکلات زیادی خواهند شد، احتمالا
    شما هم هنگام دانلود آهنگ جدید از سایت های ایرانی با تبلیغات
    پرتعداد روبرو خواهید شد،
    هر چه به دنبال لینک دانلود هستید به هدفتان نمی رسید.
    در این وضعیت احتمالا کلافه شده و
    سایت را ترک می کنید و به سراغ لینک های دیگر می
    روید.

    اگر از این وضعیت خسته شده اید و می خواهید تمامی موزیک های روز
    را به صورت یکجا دانلود کنید صفحه اصلی گلسارموزیک
    هم اکنون در دسترس شماست.

  2. private proxy

    Greetings from Florida! I’m bored at work so I decided to browse your blog
    on my iphone during lunch break. I enjoy the information you present here and can’t wait to take a look
    when I get home. I’m amazed at how quick your blog
    loaded on my mobile .. I’m not even using WIFI, just 3G ..
    Anyways, amazing site!

  3. Phoebe

    What’s up, its fastidious article concerning media print, we all be familiar with media
    is a fantastic source of facts.

    Also visit my website – basalam (Phoebe)

  4. Movie Maker Gratuit

    Somebody essentially assist to make severely posts I would state.

    This is the very first time I frequented your web page and up to now?
    I surprised with the analysis you made to create
    this actual submit incredible. Wonderful process!

  5. Cheap Private proxies

    If some one wishes expert view concerning running a blog then i recommend him/her to pay a
    visit this weblog, Keep up the nice job.

  6. jasa vektor wajah terbaik

    Hey there just wanted to give you a brief heads up and let you know a few of
    the images aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different internet browsers and both show
    the same outcome.

  7. Sa pa

    My partner and I stumbled over here by a different website
    and thought I might as well check things out. I like what
    I see so now i am following you. Look forward
    to looking at your web page yet again.

  8. Filmora Gratuit

    Hello, I read your blog daily. Your writing style is awesome, keep doing what
    you’re doing!

  9. Private Proxy

    Heya i’m for the first time here. I came across this board and I find It truly useful & it helped me
    out much. I hope to give something back and aid others like you aided me.

  10. index backlinks fast

    I was recommended this website by my cousin. I am not sure whether this post
    is written by him as no one else know such detailed about my
    trouble. You’re amazing! Thanks!

  11. Best Link Indexing

    I delight in, lead to I discovered exactly what I was having a look for.
    You have ended my four day lengthy hunt! God Bless you man. Have a nice day.
    Bye

  12. vertigo pendant light replica

    Hi! I’ve been reading your blog for some time now and
    finally got the bravery to go ahead and give you a shout out from Atascocita
    Tx! Just wanted to say keep up the good work!

  13. backlink indexing

    Someone necessarily lend a hand to make seriously posts I’d state.

    This is the first time I frequented your web page and to this
    point? I amazed with the analysis you made to make this particular put up extraordinary.

    Wonderful task!

  14. hydroponics singapore

    I got this web site from my pal who informed me concerning this
    web page and now this time I am browsing this site and reading very informative articles or reviews here.

  15. african dashiki women

    Terrific work! That is the type of information that should be shared
    across the internet. Disgrace on the seek engines for not positioning this put up upper!
    Come on over and consult with my web site . Thanks =)

  16. Telecharger Systeme d'exploitation Mac iso

    Does your website have a contact page? I’m having trouble locating
    it but, I’d like to send you an e-mail. I’ve got some recommendations for your blog you might be interested in hearing.
    Either way, great blog and I look forward to seeing it grow
    over time.

  17. Undertale Gratuit

    Howdy I am so thrilled I found your site, I really found you by error,
    while I was searching on Yahoo for something else, Nonetheless I am here now and would just
    like to say cheers for a fantastic post and a
    all round enjoyable blog (I also love the theme/design),
    I don’t have time to read it all at the minute
    but I have book-marked it and also added your RSS feeds, so when I
    have time I will be back to read much more,
    Please do keep up the great job.

  18. seo companies in Nigeria

    Have you ever thought about publishing an ebook or guest authoring on other websites?
    I have a blog based upon on the same topics you
    discuss and would really like to have you share some stories/information.
    I know my visitors would appreciate your work.
    If you are even remotely interested, feel free to send me an e mail.

  19. Manie

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

  20. distorting

    you’re in point of fact a good webmaster. The website loading speed is amazing.
    It sort of feels that you are doing any unique trick.
    Furthermore, The contents are masterpiece. you have
    performed a wonderful task in this matter!

  21. 출장마사지

    I hope your writing will be known to many people.

  22. Duncan

    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?
    Excellent work!

  23. laurence

    Hello my family member! I want to say that this post is amazing,
    nice written and come with approximately all vital infos.
    I would like to look extra posts like this .

  24. Hassie

    I know this site presents quality based articles and additional stuff, is there any other site which offers such information in quality?

  25. slew

    This info is priceless. When can I find out more?

Leave a Reply

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