How to Retain Leading Zeros in Excel

This video shows two methods for retaining the leading zero when entering data into Microsoft Excel. They both rely on making the spreadsheet see the data as text and not a number. In both cases, the output with the leading zero becomes left-aligned whereas the version that lost the leading zero was right-aligned. By default Excel places text on the left of a cell, and numbers on the right of a cell.

Normally the fact you're looking to retain the leading zero means you don't want to treat it as a number. Although you can refer to the cell in simple additions and multiplications where it appears on its own, the cell will be ignored by any range functions such as SUM and AVERAGE. A further restriction is that you cannot use number filters on cells containing text so if you wanted to divide up a list by the value of a zip code or phone number then you may have problems.

There is an alternative method that ensures Excel will treat the data as a number as long as the entries with the leading zeros follows a standard format (like 01234 for a basic zip code). Instead of selecting Text from the Format Cells menu, you can select Custom and type as many zeros as you require digits in the output. A five digit zip would use the format '00000'. If any data appeared with fewer than 5 digits, Excel would automatically add further digits to the front.