Excel, Custom Formats
In this article I will briefly explain the different concepts of custom formatting, and provide links to more detailed explanation of each.
You can download the file related to this article from the link below
Contents
Custom Date Formatting:
There are endless ways to display a date in Excel. Below are some examples:
I’ve covered this topic in detail in the article below:
Changing the font color:
Using custom formats you can change the font color of the text:
I have covered this topic in detail in the article below:
Conditions:
Using custom formatting you can check for specific conditions. For example you can check if a number is greater than 10, or smaller than 1000, ….
Based on these conditions you can apply different custom formats to the number. For example you can cause numbers larger than 1000 to display red and numbers smaller than 10 to be replaced by a text string.
Example:
In this example the following custom formatting is used:
[>1000][red]0;[<10]"Too Small";0
It checks if the value in the cell is larger than 1000. If this test returns true, then the cell font color will be changed to red. If this condition returns false, then it checks if it is smaller than 10. If this test returns true it replaces the number with the text “Too Small”. If both tests return false, the number is displayed as it is:
I have covered this topic in detail in the article below:
Semicolon Character, “;”:
There are 2 types of semicolons:
Type 1:
The first type splits the custom format into different sections. Each section applies a custom format to one of the following types of data:
- Positive numbers
- Negative numbers
- Zeros
- Text Values
For example the custom format below, colors positive numbers red, negative numbers blue, zeros green and text values cyan:
[Red]0;[Blue]0;[Green]0;[Cyan]@
I have covered this topic in detail in the article below:
Type 2:
When conditions are used in the custom format, the previous rule is overridden. Conditions have been introduced in the previous section.
@ Character:
The @ character defines where to position the text value of the cell in the custom format. For example lets say we want to add a “Dr. ” prefix before the text values input in cells. This custom formatting below should be used:
"Dr. "@
Result:
If we wanted to add a postfix we would have to use a custom formatting that looks something like this:
@" Jr"
Result:
I have covered this topic in detail in the article below:
Removing Digits, Comma Character:
For larger numbers you might want to prevent all the digits from displaying. This can be done using the comma character.
Example:
[>1000000]0.00,,"M";0.00,"K"
In this example numbers larger than 1,000,000 will be displayed as 1.12M and numbers smaller than 1,000,000 will be displayed as 23.5K.
Result:
I have covered this topic in detail in the article below:
Adding Characters and Text Strings:
You can create a custom format that will add a specific text or character to the value in the cell. Some examples have been shown in the previous sections:
"Dr. "@
Result:
I have covered this topic in detail in the article below:
Digit Placeholders 0, # and ?:
Digit placeholders can be used to determine many things:
- The number of digits to display after the decimal point
- To align numbers horizontally based on the position of the decimal point
- Whether or not to add zeros to the start or end of the number
Example:
000.000
Result:
Example:
#.###
Result:
Example:
?????.?????
Result:
I have covered this topic in detail in the article below:
You can download the file related to this article from the link below
See also:
- Excel, Custom Date Formatting
- Excel, Custom Formatting Colors
- Excel Custom Formatting Conditions
- Excel, Custom Formatting Semicolon Character
- Excel Custom Formatting, @ Character
- Excel Custom Formatting, Removing Digits
- Excel Custom Formatting Adding Characters and Text
- Excel Custom Formatting, 0, # and ? characters