When you enter data into a cell in Excel, it is handled differently depending
on what type of formatting you have assigned to the cell. For example, if you
choose Currency formatting, Excel will automatically convert 3 into $3.00;
if you choose Date formatting, Excel would convert 3/1 to March 1, 2002;
and if you choose Percent formatting, Excel would change 0.3 to 30%.
All of these choices are available when you highlight/select the cell(s) you
want to format, then go to the Format menu and choose Cells, then
click on the Number tab in the Format Cells dialog box. There
is also a choice named Special and when you choose that, you can
select formats like Social Security Number, Phone Number, etc.
But, what if you want a format that is not included in Excel's pre-made formats?
Well, you can make your own custom format, following the directions below.
- Type the number 123456789 in an empty cell and hit Enter. Now click
back onto that cell and go to the Format Menu and select Cells.
Click on the Number tab and select Custom from the list.
- In the box on the right, see all the different custom formats Excel offers.
There are formats in here for most of the styles you would get if you chose
one of the options for any of the choices in the list on the left. For example,
in the list on the right, there is a custom format that is simply an @ symbol.
If you choose this, it simply formats your cell as text. I recommend
you look through the list and try some of them and see how they affect the
number 123456789 that you have typed in your cell. Since there are so many,
I can't possibly go through all of them with you. But, what I do want to do
is teach you how to make some of your own.
- Let's say you have parts numbers and some of them begin with zero. You
will see that if you type the number 0123 in a cell, by default, Excel will
remove the leading zero and your cell will just show 123. Try it and you will
- Now click on the cell that has 123 in it and go to Format/Cells.
Choose the Custom format again and, in the box at the top right that
says General, delete the word General and type four zeroes (0000)
and click OK and you will see your number is converted to 0123. This
is because you have told Excel that at least four digits will always
appear in this cell so, if there are less than four, add a leading zero or
zeroes to it to make it four digits long.
- Click back into the cell and type 12 and you will see it is changed
to 0012. Type 1 and it is changed to 0001. But you will see that if you type
12345, no leading zeros are added since you only told Excel to fill to four
digits. This format only works when you want all numbers to be filled with
leading zeroes and be the same length, which is usually the case with something
like parts numbers.
- Let's try another one. Click back on the cell that now has 12345 in it
and make the custom format 00###. Now change the number in the cell
to 123 and you will see Excel changes it to 00123. Now change it to
12 and you will see that Excel changes it to 0012, and if you change it to
1, Excel makes it 001, since this format says always add two zeroes before
any number that is three digits or less.
- What if you had parts numbers that always began with the letter A and had
four numbers following the A? Try A000#.
- Now type 1 in the cell and hit Enter. You will see that Excel
formats it as A0001. Type 25 and Excel changes it to A0025 because it knows
the number should always be five characters long, begin with an A and
filled with zeroes.
- Look through the list of Custom formats and try making some of your
own. See that you can add parentheses and colors. Try this: type [Blue](000)
and click OK. Now type 5 and you will see it is changed to (005)
This should give you a good starting point for understanding Custom formats.
Remember that you can apply these formats to cells that already have data in
them and Excel will convert the existing data to the new format. Or, you can
highlight a range of cells and apply the format to them before you add the data,
then Excel will convert what you type into the new format.
If you want to learn more about how Excel handles formatting, etc., check out
my online classes and downloadable ebooks here: