Formatting Cells in Excel

by Linda Johnson

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.

1. 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.

2. 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.
    
3. 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 see.
    
4. 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.
    
5. 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.
    
6. 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.
    
7. What if you had parts numbers that always began with the letter A and had four numbers following the A? Try A000#.
    
8. 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.
    
9. 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) in blue.

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:
http://www.personal-computer-tutor.com/services.htm

ArrayFormulas
ConditionalFormat
Data Filters
ExcelShortcuts
Easter Eggs
FormatCells
NamedRanges
PasteSpecial
Personal.xls
Page Breaks
Car Loans
IF Statement
Intro to IFs
IFs Pt. II
Subtotals
Vlookup