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?
