Leading zeros in Excel: How to stop zeros from dropping off your numbers


Jason Morrell

Reading Time:
  minutes remaining

Have you ever typed a number with one or more leading zeros, like 00123 and watched as Excel drops the leading zeros as soon as you press Enter.

Frustrating, isn’t it!

So how do you fix it?

There are 2 methods. One is better than the other for a quick set up and easy maintenance. There is also a third method which combines elements from each of the first two.

Method 1: Format the cells as text

1.       Before you enter any data, select the cells where the data is to be entered.

2.      Press CTRL 1. This is a keyboard shortcut to display the Format Cells dialog.

3.      On the Number tab, choose the TEXT category.

4.      Close the dialog.

Now, when you enter your data with leading zeros, the leading zeros remain and do not disappear when you press Enter.

A couple of things to watch out for:

1.       Text is left-aligned by default so you may need to re-align the data.

2.      If your data has already been entered (or downloaded), switching to a text format will not fix the data - there is a partial fix for this which is outlined in Method 3.

Method 2: Use custom formatting

1.       Select the cell range where the data will be entered (or has already been entered).

2.      Press CTRL 1 to display the Format Cells dialog.

3.      In the Number tab, choose the CUSTOM category.

4.     Custom formatting works with a system of place holders. For numbers, the primary place holders are:

  • '#' which represents an optional digit 0-9,
  • '0' (zero) which represents a mandatory digit 0-9 (this is what we need for leading zeros)
  • Any punctuation you add is displayed as-is.

5.       If you have a 5 digit product code, enter 00000 into the TYPE box. This will convert 123 to 00123, 1 to 00001 etc.

6.      If you have a phone number with a 4-digit area code followed by 2 sets of 4 digits, enter (0000) 000-000 into the TYPE box. This will convert 0123456789 to (0123) 456-789.

7.      Close the dialog.

Create a custom format

Figure 01: Create a custom format

The new custom format will now be applied.

Phone numbers will now have the punctuation and spacing. For example, enter 0123456789 into a custom-formatted phone number cell (see above) and press Enter. It will automatically reformat to (0123) 456–789.

Numbers that previously dropped the leading zeros will now display the leading zeros. For example, enter 123 into a custom-formatted number cell (see above) and press Enter. The leading zero will be added automatically to display 00123.

Method 3: Expert Tip - Combine methods 1 and 2

If you import data into Excel that was exported from another program or website, the data is often formatted entirely as text.

Numbers stored as text

Figure 02: Numbers stored as text

You can click the smart icon (see above), then choose Convert To Number but you still don’t have the leading zeroes!

An easy way to convert a lot of ‘text’ numbers to ‘numeric’ numbers (if that makes sense) and add leading zeroes is to follow this process (assume the ‘text’ numbers are in column A):

Numbers start in text format

Figure 03: Numbers start in text format

1.       Insert a blank column, if necessary.

2.      In cell B1 type =A1*1 and press Enter. The result is the same number but in numeric format (notice that it is right-aligned).

Formula that converts 'text' number to 'numeric' number

Figure 04: Formula that converts 'text' number to 'numeric' number

3.       Copy the formula down.

4.      Select column B and assign the custom format to add the leading zeros as described above.

Select the entire column and set a custom format

Figure 05: Select the entire column and set a custom format

5.       With column B still selected, COPY the data then PASTE SPECIAL as VALUES (using the right-click menu) to column B to overwrite the values, or PASTE SPECIAL directly over the original ‘text’ numbers in column A if your prefer.

Select the column of data, COPY, then PASTE SPECIAL the VALUES over the same cells

Figure 06: Select the column of data, COPY, then PASTE SPECIAL the VALUES over the same cells

6.       Delete column B as it is now redundant.

I hope that gets you out of a fix. 

What do you think?

Watch the video (over the shoulder demo)

To see this technique demonstrated with additional examples, check out this Leading Zeroes video.

Click to watch video

What next?

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!


Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}