Excel: Numbers that Start or Begin with a Zero

Often businesses identify products, projects, or people with numbers.  Sometimes those numbers have a zero at the beginning.  This is called “leading zero.”  Excel gets rid of the leading zero when you type it in, because Excel thinks numbers should look like real numbers.  Real numbers don’t begin with zeroes.

Fortunately, you can customize Excel to think like you do.  Let’s say you are making a project list in the year 2002.  You want your project numbers to begin with 02, so our first job would be 02001.  When you type this into a cell, Excel turns it into 2001.  We need to reformat the cell.

Click the cell.  There are two ways to get to the Format Number window.  Find the Number section in the in the Home tab of the ribbon, circled in pink below.

Hover your mouse over the small down arrow next to the word General, circled in green above.  This is your current number format.  “General” means that Excel will take its best guess:  a number, some text, a date, etc..  For leading zeroes, Excel guesses wrong.  We need a different format.

Click on the arrow and you will see a group of choices.

There is nothing about numbers with leading zeroes on this menu, so we would click “More Number Formats” at the bottom of the list.  This takes you to the Format Cells Window.

You may also click on the Expand arrow in the Number section of the Home tab of the ribbon, circled in blue.  Did you know Microsoft calls this the “Dialog Box Launcher?”  I just learned that, too.

This will also take you to the Format Cells Window. 

Click on the last choice, Custom, highlighted in yellow above.

In the Custom samples, almost every typical format is shown and you can scroll down to see lots of choices.  No leading zeroes, though.  Look at the second choice:  0.00.  This means no matter what number is entered, it will have a single digit followed by a period, followed by the tenths and hundredths place.  For example, enter 1 and you get 1.00.  Enter .99 and you will get 0.99.  Enter 1.2 and you will get 1.20.  Get it?  Think of each 0 in the format as a place holder.

Phone numbers can be formatted (000) 000-0000, right?  Social Security Numbers are 000-00-0000.  So back to our leading zeroes.

Go to Excel and type 0123456 in cell A1.  Yes, stop and do this right now.  As soon as you press enter, Excel will change this to 123456.  Go back cell A1, and use the steps above to go to Home>Number>Click the Dialog Box Launcher (Expand Arrow).  In the Format Cells Windows>Number tab, click Custom.

In the Sample, you will see 123456 since that is what Excel selected as the proper format.  In the “Type:” area, highlighted yellow, type in 0000000 (seven zeroes).

Notice that the sample now shows 0123456. Click “OK” and cell A1 will have a leading zero.  Try some other formats until you are comfortable.  Start with 0000000.000 and you get 0123456.000, right?  Try the phone number and Social Security Number formats.  What are the identification numbers or project numbers you use in you day to day work?