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.

46 comments

  1. skyscanner flights

    Does your blog have a contact page? I’m having problems locating it but, I’d like to send you an e-mail.

    I’ve got some creative ideas for your blog you might
    be interested in hearing. Either way, great blog and I look forward
    to seeing it improve over time.

  2. software

    I every time spent my half an hour to read this weblog’s content daily along
    with a mug of coffee.

  3. maria b new collection

    Aking questions are truly pleasant thіng if you are not understandiing anytһing fuⅼly, except
    this piece of writing preѕents fastidious understandiong yet.

    My webpage :: maria b new collection

  4. generic levitra

    Au premier coup de fusil des tirailleurs de Galbaud, la porte s’ouvrit; la cavalerie se porta en avant et l’infanterie de la
    garnison et la garde nationale se jeterent de chaque cote par Jardin-Fontaine et
    Thierville. generic levitra Comme elle ne se souciait pas de mettre ses gens
    dans la confidence de cette faiblesse, et que le domicile de la pythonisse n’etait pas tres-eloigne de son hotel, puisque pour s’y rendre il ne fallait que traverser les Champs-Elysees, elle y allait a pied et tres-simplement vetue.

  5. web hosting service

    Exceptional post however , I was wanting to know if you
    could write a litte more on this subject? I’d be very grateful if you could elaborate
    a little bit further. Many thanks!

  6. web hosting service

    I enjoy what you guys tend to be up too. Such clever work and coverage!
    Keep up the wonderful works guys I’ve incorporated
    you guys to our blogroll.

  7. web hosting reviews

    Hi would you mind sharing which blog platform you’re
    working with? I’m looking to start my own blog soon but
    I’m having a difficult time choosing between BlogEngine/Wordpress/B2evolution and Drupal.

    The reason I ask is because your layout seems different then most blogs and
    I’m looking for something completely unique.

    P.S Sorry for getting off-topic but I had to ask!

  8. web hosting providers

    excellent issues altogether, you just received a
    logo new reader. What could you suggest about your put up that you made some days ago?
    Any sure?

  9. web hosting providers

    It’s an amazing post in favor of all the web people; they will take advantage from it I am sure.

  10. best web hosting 2020

    Thank you for any other informative site. Where else may I get that type of
    info written in such an ideal manner? I’ve a venture that I’m simply now operating on,
    and I have been on the look out for such info.

  11. adreamoftrains webhosting

    Heya just wanted to give you a brief heads up and let you
    know a few of the pictures 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.

    adreamoftrains web hosting

  12. website hosting companies

    Heya fantastic website! Does running a blog like this require a
    lot of work? I have absolutely no knowledge of programming but I was hoping to
    start my own blog soon. Anyway, should you have
    any ideas or techniques for new blog owners please
    share. I understand this is off subject however
    I simply had to ask. Many thanks!

  13. best website hosting

    I’m impressed, I have to admit. Rarely do I come across a blog that’s
    equally educative and engaging, and without a doubt, you have hit the
    nail on the head. The issue is an issue that not enough
    people are speaking intelligently about. I am very happy I stumbled across this
    during my hunt for something regarding this.

  14. web hosting sites

    Hi there, You’ve done a great job. I will certainly digg it and personally recommend to my
    friends. I am confident they will be benefited from
    this web site.

  15. best web hosting sites

    Good information. Lucky me I discovered your site by accident (stumbleupon).
    I’ve saved as a favorite for later!

Leave a Reply

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