<$BlogRSDURL$>

April 25, 2004

More Custom Formatting

I could probably write a book on Custom Formatting and still not cover everything :)

I see that the options offered on my Excel 2003 are becoming more diverse but here are some simple formats that you should know - adapt them to what best suits you. Right click the cell(s) you want to change and select the Format Cells dialog box, then choose Number, Custom.

Numbers & Currency
0 a general number format
0.00 a number with 2 decimal places (add or delete zeros for more or less decimal places)
$0.00 dollar currency with 2 decimal places
#,##0 a number with commas added for every thousand, million, billion...
$#,##0.00 dollar currency with 2 decimal places and commas added for every thousand, million, billion...
#,##0, will round your number to thousands
#,##0,, will round your number to millions

Fractions
# ?/? simple fraction rounded up to 1 denominator
# ?/?? simple fraction rounded up to 2 denominators
# ?/??? simple fraction rounded up to 3 denominators
# ?/???? simple fraction rounded up to 4 denominators (This appears to be the limit for fractions, I don't know the limit for decimals but I can tell you it's a lot!)
# #/4 fraction divided into quarters
# #/16 fraction divided into 16ths
# #/100 fraction divided into 100ths

Dates and Time
Dates (see my previous post)
h:mm:ss hours, minutes and seconds
h:mm AM/PM hours and minutes with AM or PM

Percentage
% Just add a percentage mark to a number format (decimal numbers or commas are okay)

Text
Very useful. You can use this to enter almost anything and what you enter will be what is displayed. Note: You'll have to change to another format in many cases such as entering formulas otherwise they will not work.

@ You can use this to add whatever word you enter to text that is already entered into your format. For example, "This is a "@ will give you 'This is a boat', if you enter 'boat' into the cell. You can enter text before and after the @ mark. (Use double quotation marks for formatting, single quotation marks are just to show my examples)

Colors and Equations
You can do simple conditional formatting by entering colors or equations into square brackets. For example, [Red][=<100] will turn the font red for any number less than or equal to 100, but might cause the value to disappear if over 100. I suggest using a semicolon and using a formula such as [Red][=<100];[Black]. Combinations are also possible, such as [Red][=<100];[Blue][=>200] which means red font for any number equal or less than 100 and blue font for any number equal to or more than 200. (You might find that Excel adds it's General format as an automatic anti-error feature, this usually works to your advantage)

Okay, that's it for now. There is another simple way to make custom formats, I'll cover this sometime in the future.

This page is powered by Blogger. Isn't yours?