Phone 1300 793 951 or visit www.odysseytraining.com.au

Odyssey Training Logo
Microsoft Excel logo

Excel tutorial: Show leading zeros on numbers, but don't convert the numbers to text

If you want to keep a leading zero on a number, a workaround people often use is to format the cell as text or type an apostrophe before the number – e.g. '0822 will automatically store the leading zero, but keep the text as a number.

But what if you want to have numbers that display leading zeros, and keep the numbers as a numeric value?

Consider the following example:

The list below displays numbers. We want them to continue to be numeric values, but display with leading zeros, as shown in the second column.

 Display numbers with two leading zeros example

Format cells custom example four zerosStep 1. Select your list of numbers to format.

Step 2. Right-click > Format Cells.

Step 3. Choose Custom number formatting, and in the Type: box, enter 0000

Step 4. Click OK.

Your numbers will have as many leading zeros as you entered, and you can have as many as you need! (The Type 0000 will have a leading 0 on all numbers up to 999.)

Most importantly, your values are still numbers, so you can perform numeric calculations with them.

This tip was provided by Jane Pettigrew, one of our expert Microsoft trainers. Learn more by attending our beginner to advanced level Microsoft Excel training.

Our Training Courses

- +
Subtotal
Special offer