How to Display Leading Zeros in an Excel Spreadsheet

By Shawn McClain

Leading zeroes, or zeroes listed before a number, can be important if you need to create a list of numbers that are all the same number of digits, regardless of the actual number. When you include zeroes before a number in Microsoft Excel, it defaults the cell to the general number format and erases the zeroes. In order to include the zeroes, you have to either change the format of the cell, or tell Excel to record the number as text instead.

Changing the Cell Format

Step 1

Highlight the cells where you want the numbers to have leading zeroes. You can click and drag your mouse over the area, or click on the desired column or row. If you want the whole worksheet to display leading zeroes, click the button to the left of column "A" and above row "1."

Step 2

Click the "Home" tab at the top of the screen and then find the box labeled "Number." Click the small arrow right next to the word "Number" to open up the format cells window.

Step 3

Choose "Custom" form the list of formats on the left.

Step 4

Click in the text box underneath the word "Type" on the right side of the window. The word "General" should already be in the box. Press the backspace key to erase "General."

Step 5

Type zeroes in the box equal to the number of digits you want all the numbers to be. If you type "00000" in the box, any number less than five digits will have leading zeroes added to make it five digits long. If you would rather add the same number of leading zeroes to all numbers, regardless of length, type in the number of zeroes you want to add inside quote marks, followed by the pound sign. For example if you wanted to add three zeroes to every number, you would type: "000"#.

Step 6

Click "OK" and the new rules will be applied to the worksheet.

Enter Numbers as Text

Step 1

Click on the cell where you are going to type the number.

Step 2

Press the apostrophe key.

Step 3

Type the number, including the leading zeroes, after the apostrophe and press enter.