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. หาเงิน พิเศษ

    I think this is one of the most vital information for me. And i’m glad reading your article.

    But want to remark on few general things, The site style is great, the articles is really excellent : D.
    Good job, cheers

  2. งานออนไลน์

    Hey! Someone in my Facebook group shared this site with us so I came to look it
    over. I’m definitely enjoying the information. I’m
    book-marking and will be tweeting this to my followers!
    Outstanding blog and terrific design.

  3. lacbet

    I like the valuable info you provide in your articles. I’ll bookmark your blog and
    check again here frequently. I am quite certain I will learn plenty of new stuff right here!
    Good luck for the next!

  4. 카지노사이트

    Hi there to every body, it’s my first go to see of
    this website; this website consists of remarkable and truly excellent information for visitors.

  5. bigsloto

    We are a group of volunteers and opening a new scheme in our community.
    Your web site offered us with helpful information to work on. You have done a formidable process and our
    entire neighborhood will be thankful to you.

  6. giocare

    My programmer is trying to convince me to move to .net from PHP.
    I have always disliked the idea because of the expenses.
    But he’s tryiong none the less. I’ve been using Movable-type on various websites for about a
    year and am nervous about switching to another platform.
    I have heard very good things about blogengine.net.
    Is there a way I can transfer all my wordpress posts into
    it? Any help would be greatly appreciated!

  7. เครดิตฟรี ทดลองเล่น

    สล็อตออนไลน์ เป็นอีกหนึ่งเกม เครดิตฟรี ทดลองเล่น ที่ยอดนิยมมายาวนานหลายปีไม่มีตก สายรัดเอวสโนว์ออนไลน์ที่ คาสิโน สามารถเล่นได้ทุกเพศทุกวัย ด้วยแนวทางเล่นที่ง่ายแสนง่ายใช้เพียงแค่ปลายนิ้วกดที่ปุ่มสปินให้วงล้อหยุดหมุน ตรงที่มีภาพเรียงเช่นเดียวกัน3 ถึง 5
    รูปภาพขึ้นไป ด้านในเกมยังมี เครื่องหมายพิเศษมากมายก่ายกองที่ให้อัตราการแจกจ่ายที่ต่างกันออกไปไม่ว่าจะเป็น แจ็กพอเพียงต โบนัส ฟรีสปิน และยังรวมไปถึง เครดิตฟรี เสน่ห์สำคัญๆของเกมนี้ก็คือตัวเกมเป็น วิดีโอเกมที่มีภาพเคลื่อนไหวที่น่าสนใจ มีแสงสีเสียง สวยสดงดงาม ภาพวิจิตรตระการตาตา และก็แบบธีมที่ต่างกันไม่เสมือนเกมอื่น เครดิตฟรี

  8. ma sói việt hóa

    Thank you for the good writeup. It in fact was a amusement account it.

    Look advanced to far added agreeable from you! By the way, how
    can we communicate?

  9. 21 Casino

    Good day! I know this is kinda off topic but I’d figured I’d ask.
    Would you be interested in trading links or maybe guest authoring a blog post
    or vice-versa? My blog addresses a lot of the
    same subjects as yours and I believe we could greatly
    benefit from each other. If you’re interested feel free to send me an email.
    I look forward to hearing from you! Awesome blog by the way!

  10. poker stars

    Hello! I know this is kinda off topic nevertheless I’d figured I’d ask.
    Would you be interested in trading links or maybe guest authoring a blog article or vice-versa?
    My site addresses a lot of the same topics as yours and I feel we
    could greatly benefit from each other. If you are interested feel free to shoot me
    an e-mail. I look forward to hearing from you! Excellent blog
    by the way!

  11. 카지노 사이트

    First off I would like to say fantastic blog! I had a quick question that I’d like to ask if you do not mind.
    I was curious to know how you center yourself and clear
    your thoughts before writing. I have had difficulty clearing my
    mind in getting my ideas out there. I do take pleasure in writing however it just seems like the first
    10 to 15 minutes are generally lost just trying to figure out how to begin. Any ideas or hints?
    Thank you!

  12. Williamhill

    That is very fascinating, You are an excessively professional blogger.
    I’ve joined your rss feed and stay up for looking for extra of your excellent post.

    Additionally, I have shared your website in my social networks

  13. handicapped beds

    I am commenting to let you know what a terrific experience my daughter enjoyed reading through your web page. She noticed a wide variety of pieces, with the inclusion of what it is like to have an awesome helping style to have the rest without hassle grasp some grueling matters.

  14. pronájám dodávky

    hello!,I like your writing so a lot! share we keep in touch
    extra about your article on AOL? I require an expert in this space to unravel my problem.
    May be that’s you! Having a look forward to see you.

  15. happyluke login

    WOW just what I was searching for. Came here by searching for cหา ตัง ใน เกม

  16. roulette online

    I have been exploring for a little bit for any high-quality articles or weblog posts on this sort of house .
    Exploring in Yahoo I at last stumbled upon this
    website. Studying this information So i’m satisfied to
    express that I’ve an incredibly excellent uncanny feeling I came upon exactly what I needed.
    I such a lot no doubt will make certain to do not overlook this website and give it a look regularly.

  17. high quality dog clothes

    I do not even understand how I finished up right here,
    however I thought this put up was great. I do not recognise who you’re but definitely you are going to a well-known blogger for
    those who are not already. Cheers!

  18. 온라인카지노

    Hey! This post could not be written any better!

    Reading through this post reminds me of my old room mate!
    He always kept chatting about this. I will forward this post
    to him. Fairly certain he will have a good read. Thanks for sharing!

    Review my web-site – 온라인카지노

  19. 카지노사이트

    I am really impressed with your writing skills as well
    as with the layout on your blog. Is this a paid theme or did you modify it
    yourself? Either way keep up the nice quality writing, it is rare to see a nice blog like this one nowadays.

    Feel free to surf to my homepage … 카지노사이트

  20. 퍼스트카지노

    Hello superb blog! Does running a blog such as this take a great deal of work?

    I have virtually no expertise in programming but I was hoping to start my own blog
    soon. Anyways, should you have any ideas or tips
    for new blog owners please share. I know this is off topic however I
    just had to ask. Many thanks!

  21. 메리트카지노

    Do you have a spam problem on this site; I also am a blogger, and I was wanting to know your
    situation; we have created some nice procedures and we are looking
    to exchange strategies with others, please shoot me an e-mail if interested.

  22. scommesse bet

    Yes! Finally someone writes about Bingo.

  23. Casino Trực Tuyến

    Hi there! This article could not be written much better!
    Reading through this article reminds me of my previous roommate!
    He continually kept talking about this. I’ll forward this information to
    him. Fairly certain he’s going to have a good read. Thank
    you for sharing!

  24. san antonio restaurants

    First off I want to say terrific blog! I had a
    quick question that I’d like to ask if you don’t mind.
    I was curious to know how you center yourself and clear your head prior to writing.
    I’ve had a difficult time clearing my thoughts in getting my thoughts out there.

    I do take pleasure in writing but it just seems like the first 10 to 15
    minutes are usually lost just trying to figure out how to begin. Any
    suggestions or hints? Thanks!

  25. オンラインカジノ

    Heya i’m for the primary time here. I came across this
    board and I in finding It truly helpful & it
    helped me out a lot. I hope to offer one thing again and aid
    others like you helped me.

  26. fb88

    An impressive share! I’ve just forwarded this onto a friend who was conducting a little homework
    on this. And he in fact ordered me dinner due to the fact that I discovered it for him…
    lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending some time to discuss this matter here on your web
    page.

  27. casino games

    I’ve read several good stuff here. Definitely worth bookmarking for revisiting.
    I wonder how a lot effort you put to make this kind of wonderful informative website.

  28. ベラジョンカジノ 出金

    Hmm it appears like your blog ate my first comment (it was
    extremely long) so I guess I’ll just sum it up what I had
    written and say, I’m thoroughly enjoying your blog.
    I too am an aspiring blog writer but I’m still new to everything.
    Do you have any suggestions for novice blog writers?
    I’d certainly appreciate it.

  29. 웹하드순위

    It’s going to be end of mine day, except before end I am reading
    this impressive post to improve my know-how.

  30. Cigars

    Wow, marvelous weblog format! How lengthy have you been running a blog for?
    you make running a blog glance easy. The overall glance of
    your website is excellent, let alone the content material!

Leave a Reply

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