<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**SEE ALSO:****Excel Formula Manager Add-in |Excel Number Manager Add-in |Excel Text Manager Add-in **

**Current Special!** Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,**30 Day Money Back Guarantee** & Free Excel Help for LIFE!

Got any Excel Questions? **Free ****Excel Help **

Let's take a detailed look at **Custom Number Formats**. Don't confuse the term "Number" as meaning custom formats only applying to numeric data as it can also apply to text.

It is very important to understand how cell formats are seen by Excel. Excel see a cells format as having four **Sections**. These are, from left to right ** Positive numbers, Negative Numbers, Zero Values and Text values.** Each of these

**Example of a Custom Number Format **

It is also very important to understand that the formatting of a cells value does not effect its underlying true value. To show this we can type any number into cell **A1**, then go to **Format>Cells-Number-Custom **and, using any format as a Starting point, type in **"Hello"**,** **with the quotation marks, now click **OK**. Now, while the cell displays the word **Hello**, it's true value can be seen by selecting the cell and looking in the **Formula bar**, or by pressing **F2**. If you were to reference this cell in a formula, e.g. **=A1+20** the result cell would also take on the custom format. If we were to reference cell **A1** and many other cells that have any standard Excel format, e.g. **=SUM(A1:A10)** our result cell **would still take on the custom format** of cell **A1**. This is an educated guess by Excel that you want the result cell formatted the same as the referenced cell(s). If the referenced cells contain more than one type of format, any custom format will take precedence.

So, you must always remember that Excel uses a cells true value and **not** it's displayed value. This can catch out the unwary if you are calculating cells that are formatted for no, or few, decimal places. For example, enter **1.4** in **A1** and **1.4** in **A2**, format both these cells to show zero decimal places and then place**=A1+A2** and the result is **3. **Excel does have an option called **Precision as displayed**, found under **Tools>Options-Calculation**, but you should be aware that this option will **permanently** change stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. In other words, once it's been checked and given the **OK**, **there is no turning back!.**

The default format for any cell is "**General**". If we enter a number into a cell, Excel will often ** guess **the number format that is most appropriate. For example, if you type in

Ok, getting back to the all important **Sections **that a formatted cell contains. Within these **Sections **we are able to use **Formatting Codes. **It is these codes that force Excel to make our data appear how we would like. Let's use a simple Example. Suppose you would like any negative number to appear inside parenthesis, and all numbers, positive, negative or zero, to show two decimal places. The Custom Format we could use is: **0.00_ ;(-0.00)**. If you also wanted negatives to be red, use: **0.00_ ;[Red](-0.00) **Note the use of the square brackets in the **Section** for negative numbers. This is the Formatting Code that tells Excel to make the number red.

There are many different **Formatting Codes** that can be used within **Sections** of a Custom Format. The tables below show these. **The Table is from Microsoft�**

Number Code | Description |

General | General number format. |

0 (zero) | Digit placeholder. This code pads the value with zeros to fill the format. |

# | Digit placeholder. This code does not display extra zeros. |

? | Digit placeholder. This code leaves a space for insignificant zeros but does not display them. |

. (period) | Decimal number. |

% | Percentage. Microsoft Excel multiplies by 100 and adds the % character. |

, (comma) | Thousands separator. A comma followed by a placeholder scales the number by a thousand. |

E+ E- e+ e- | Scientific notation. |

Text Code | Description |

$ - + / ( ) : space | These characters are displayed in the number. To display any other character, enclose the character in quotation marks or precede it with a backslash. |

\character | This code displays the character you specify. Note Typing !, ^, &, ', ~, {, }, =, <, or > automatically places a backslash in front of the character. |

"text" | This code displays text. |

* | This code repeats the next character in the format to fill the column width. Note Only one asterisk per section of a format is allowed. |

_ (underscore) | This code skips the width of the next character. This code is commonly used as "_)" (without the quotation marks) to leave space for a closing parenthesis in a positive number format when the negative number format includes parentheses. This allows the values to line up at the decimal point. |

@ | Text placeholder. |

Date Code | Description |

m | Month as a number without leading zeros (1-12) |

mm | Month as a number with leading zeros (01-12) |

mmm | Month as an abbreviation (Jan - Dec) |

mmmm | Unabbreviated Month (January - December) |

d | Day without leading zeros (1-31) |

dd | Day with leading zeros (01-31) |

ddd | Week day as an abbreviation (Sun - Sat) |

dddd | Unabbreviated week day (Sunday - Saturday) |

yy | Year as a two-digit number (for example, 96) |

yyyy | Year as a four-digit number (for example, 1996) |

Time Code | Description |

h | Hours as a number without leading zeros (0-23) |

hh | Hours as a number with leading zeros (00-23) |

m | Minutes as a number without leading zeros (0-59) |

mm | Minutes as a number with leading zeros (00-59) |

s | Seconds as a number without leading zeros (0-59) |

ss | Seconds as a number with leading zeros (00-59) |

AM/PM am/pm | Time based on the twelve-hour clock |

Miscellaneous Code | Description |

[BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n] | These codes display the characters in the specified colors. Note n is a value from 1 to 56 and refers to the nth color in the color palette. |

[Condition value] | Condition may be <, >, =, >=, <=, <> and value may be any number.Note A number format may contain up to two conditions. |

If you do a lot of Custom Formatting you might find it useful to print this table.

Lets look at the last Formatting Codes, the **Comparison Operators**. Assume we want our custom number format: **0.00_ ;[Red](-0.00)** to only display negative numbers as red font in brackets**IF** the number is less than **-100.** For this we could use: **0.00_ ;[Red][<-100](-0.00);0.00 **It is the **Formatting Codes**: **[Red][<-100](-0.00)** placed in the **Section** for negative numbers that make this possible.

One format that is often asked for is to display dollar values as words. For this we can use the Custom Format of: **0 "Dollars and" .00 "Cents"**. This format will force a number entered as 55.25 will be displayed as **55 Dollars and .25 Cents**. If you wish to actually convert numbers to dollars and cents, see these two Custom Functions from Microsoft.

- Microsoft's Convert a Numeric Value into English Words
- Microsoft's Convert a Currency or Value into English Words

Let's look at one more Custom Format where we wish to display the words **Low**, **Average** or **High** along with the number entered. For this we could use: **[<11]"Low"* 0;[>20]"High"* 0;"Average"* 0** Note the use of the Formatting Code *** ***This code repeats the next character in the format to fill the column width* Meaning all our **Low**, **Average** or **High** text will be forced to the right, while the number will be forced to the left.

**Microsoft�** have a helpful Workbook you can Download that shows many of the abilities of Custom Formats.

See Also:Custom Formats

**Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money**

**Special! Free Choice** of Complete Excel Training Course **OR **Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 **gets you BOTH! Purchases MUST be made via this site**. Send payment proof to [email protected] 31 days after purchase date.

**Instant Download and Money Back Guarantee on Most Software**

**Excel Trader Package**Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

**Some of our more popular products are below...**