How To Add Leading Zero In Excel

Most frustrating thing in excel is saving Account numbers, Phone numbers and ZIP codes, we end up losing the leading zeroes.

Today Let us see how to avoid it

What is leading zero?
Where excel leading zeroes are used?
How to use it with an example

What is leading zero?

A leading zero is any 0 digit that comes before a non-zero digit in a value.

Where excel leading zeroes are used?

In various situations of our life, we may need it .i.e Zip codes, Phone numbers, Bank accounts and many more…

How to use it with an example

It can be done in many ways, let’s look at a few of them…

3 Ways To Add Leading Zero To Excel And Maintain The Structure

1.Convert a number to text
2.Using REPT Function
3. Apply Custom formatting


1.Converting A Number To Text

The solution is to convert the cell format to “Text”

Step 1: Select the cell or the range of your choice and Click the number format icon

Leading zeroes 1

Step 2: Select the Text tab from the left display and click “Ok”

Leading zeroes -2

Step 3: Now you can just start off with the data entry.

@Checkout the example below
Leading Zeroes Excel- 1
NOTE: As we are converting it to text, few calculations may not work.


2.Using REPT Function

REPT function in combination with LEN can help us achieve it

Copy paste the below formula where ever you need it by just changing the reference

Formula : =REPT(0,6-LEN(E1))&E1

The above formula makes sure that the cell always contains 6 digits in it, by adding the extra zeroes to it.

@Checkout the example below
Leading zeroes -5

Explained :

  • REPT(0, No.of times), We have to provide the parameters like what to repeat and how many times to repeat it
  • LEN(E1), Finds the length of E1 which is the cell we are performing the action on.
  • &E1, It just adds the original text to the above calculation
NOTE: As we are converting it to text, few calculations may not work.


3. Apply Custom Formatting

The advantage with this way of adding leading zeroes is that you can perform the calculation on the data.

Step 1: Select the cell or the range of your choice and Click the number format icon

Leading zeroes 1

Step 2: Select the Custom tab from the left display

Leading zeroes 3

Step 3: Enter “000000” into the type field and click “Ok”

Leading zeroes -4

Step 4: Now you can just start off with the data entry.

@Checkout the example below
Leading Zeroes Excel- 2

Note: Here only the formatting is changed but not the value, you can see the real value in the formula bar.

What are you waiting for, Give it a try buddy!!!

Hope the article has helped you…

Leave a Reply

%d bloggers like this: