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


Custom Date Formatting:

There are endless ways  to display a date in Excel. Below are some examples:

Custom Formatting, Dates
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:

Custom Formatting, Colors

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:

Custom Formatting, Conditions

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]@

Custom Formatting, Semicolon

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:

Custom Formatting, @png
If we wanted to add a postfix we would have to use a custom formatting that looks something like this:

@" Jr"

Result:

Custom Formatting, @2

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:

Custom Formatting, Remove Digits


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:

Custom Formatting, @png

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:

Custom Formatting, 0
Example:

#.###

Result:

Custom Formatting, #
Example:

?????.?????

Result:

Custom Formatting, Question Mark

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:

 

Leave a Reply

Your email address will not be published. Required fields are marked *