All the formatting in excel is divided into four parts:
- <POSITIVE> – This is where the formatting goes for positive numbers
- <NEGATIVE> – This is where the formatting goes for negative numbers
- <ZERO> – This is where the formatting goes for
- <TEXT> – This is where the formatting goes for text
Note that all parts are divided by semi-colons (;). Between each of the semicolons is where you define the formatting for each of the conditions specified. Please note that in some special cases, these conditions are not relevant (ie. date and time formats) and that you don’t necessarily have to specify the condition for zeroes or text values.
Anything entered into a cell in excel would fall into these four categories, making it possible to have a custom format for anything.
How the construct works
You can specify a format for any or all of these four parts. For example, if you write General;General;General;General then everything is in a general format. But if you write 0.00;-0.00;0.00;General, positive numbers are displayed with 2 decimals, negative with a negative sign and 2 decimals, zero as 0.00 and text as normal text.
Similarly, you can specify the format for any of the four parts.
If you mention only:
- One format: It is applied to all the four sections
- For example, if you just write General, it will be applied for all the four sections
- Two formats: First one is applied to positive numbers and zeros, and second is applied to negative numbers. Text format by default becomes General
- Three Formats: First one is applied to positive numbers, second is applied to negative numbers, third is applied to zero, and text disappears as nothing is specified for text.
The following are the custom formatting adjustments that go into creating the Accounting format. This is a good good place to start learning custom formatting.
|_(||Adds a left indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore|
|$||Special character that will show up in your output without the need to add quotation marks within the syntax|
|*||(Please also note that the code indicated here is both an asterisk and a blank space) The asterisk tells Excel to repeat the subsequent character until the width of the cell is filled; this is why the accounting format has the dollar sign aligned to the left, the number aligned to the right, with as many spaces as necessary in between|
|#,##0||Displays commas in between every three digits, for all values exceeding three digits|
|0||Digit placeholder that displays insignificant digits (ie. digits that are zero)|
|0||The zeros trailing after the first zero indicate how many decimals places you want to show; even if the final digit is zero, it will be displayed in this format|
|_)||Adds a right indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore|
|“-“||Displays a text value; anything between the quotation marks will be shown|
|?||Digit placeholder similar to 0, but does not display insignificant digits; if the value is zero, it will just be a blank space; in accounting format, because it is in the Zero section of the syntax, it will always appear as a blank space|
|@||Placeholder for text|
If you want to learn more about custom number formatting, I would highly recommend Office Help section.