Excel Tips: Show leading zeros on numbers

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.

Step 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.