Custom Formatting
You can create your own unique number style to show in Excel cells by using the Custom Number Formatting feature in Format Cells. For example, you could need to designate the negative amount as Red or Blue when working on a spreadsheet that contains client details. You might also need to construct a special number format for amounts. You can automatically format a series of digits in a cell as a unique amount number by applying this custom format to them. This may be necessary for your model or report so that the data is visible and easily understood.
Here are ten key takeaways for custom formatting in MS Excel:
1.Accessing Custom Formats:
-
- In Excel Select the cell — >>> Home Tab — >>> Alignment Group — >>> Alignment Settings — >>> Number Tab — >>> Custom.
- Or
- Press CTRL+1 — >>> Number Tab — >>> Custom
- Or
- Right Click the Cells — >>> from the list choose Format Cells.
2. Basic Structure:
-
- Custom format consists of up to four parts separated by “ ; “ a semicolon: TEXT, ZERO, POSITIVE NUMBERS, and NEGATIVE NUMBERS. Example: #,##0.00_;[Red](#,##0.00);0.00;@.
3. Number Formatting Codes:
-
- We use (zero) 0 if a Number formatting cell has example 00000, and you type 7 it shall display digits like 00007. We use # in custom format example #,## if someone type 10000 the format will display 10,000.
4. Decimal Places:
-
- We use decimal places with #,##0.00 for thousands separators and fixed decimals. If some one type 10000 the format will display 10,000.00.
5. Thousands Separator:
-
- We use #,## for thousand separator a comma in between #.
6. Text and Special Characters:
-
- We use text within quotation marks in custom format cells to force a cell to display the formatting: Example “Amount:” #,##0.00, with this format if someone types 1200, then the cell will show “Amount 1,200.00.”
8. Conditional Formatting:
-
- We use custom in format cells to apply conditions for colors by using brackets.
- Example [Blue]#,##0.00_);[Red](###0.00)
9. Dates and Times:
-
- We use specific style for dates and times, for instance some one wants to show week day and date in a cell we go to custom cell and copy the following code. DDDD DD/MM/YYYY H:MM:SS AM or PM).
10. Colors:
-
- We can use COLOR in custom format cells to apply colors for fonts. For instance: [RED], [BLUE] [CYAN].
11. Combining Multiple Formats:
- We can use multiple formats for a single cell in custom format cells. For instance: [Blue]#,##0.00_);[Red](###0.00);[Green]@
Custom Number Formatting Design (Format cells — >>> Custom)
In format Cells a custom number format a cell can have up to four combined sections, and we can separate them by semicolons (;). These sections apply to:
Positive numbers
Negative numbers
Zero values
Text values
For example: #,##0;[Red](#,##0);0;”Text: “@
Components of Custom Number Formatting in Excel:
# (Number Placeholder):
“#” It represents a digit placeholder. We use it to display significant digits without showing extra zeros.
If we type more digits than placeholders, Excel will display all digits.
- Format: ###
- Example: We format the custom # and type some numbers example 987, it displays as 987. If the number is 7, it displays as 7.
? (Digit Space Holder):
“?” It is the same as the #, only difference for numbers, it will increase a space at the end of numbers to align if some cell has no decimals or zero.
Hences, it is useful for aligning numbers by the decimals or zeros.
- Format: ?.?? or .??
- For instance: If the number is 555, it displays as “555 “ with two spaces after 555.
0 (Zero Placeholder):
We use (zero) 0 it ensures that if a Number formatting cell has trailing or leading 0s as example 00000, and you type 7 it shall display digits like 00007.
- Format: 00000
- Example: If the number is 7, it displays as 00007.
$ (Currency Symbol):
$ sign will place a currency symbol and display number with leading currency symbol in the formatted number.
- Format: $#,##
- Example: If the format for number is 10001, it displays as $10,001.
% (Percentage):
The percentage format multiplies the cell value by a hundred and displays the result in a percentage format.
Example: (=16/120) 0.1333 formatted as a percentage is displayed as 13.3333% or 13.33%.
- Format: 0.00%
- Example: If the number is 01333, it displays as 13.33%.
_ (Underscore):
_ Underscore increase a space equal to the width of the next character.
This is used to align numbers properly by increasing the spaces.
- Format: #,##0.00_);(#,##0.00)
- Example: If the number is 10001, it displays as 10,000.00 increasing a space for alignment.
Color:
Color is used for changing the color of the number or content in a cell, color condition formats are typed in brackets. For instance: [Blue] [Green], [Red], etc…
- Format: [Red](0);[Blue]0
- Example: As the above syntax explains the negative number format is Red, and the Positive number format is Blue.
Comma (,) (Thousands Separator):
We use comma to add a thousand separator to big numbers. This option also can be used to scale numbers down by factors of a thousand when multiple commas are used (e.g., #,).
- Format: #,##0.00
- Example: If the number is 9500000000, it displays as 9,500,000,000.00.