Custom format strings in Excel are used to change the visual appearance of the data in cells or to add attributes such as text color. Operations can be applied to a single cell or to a group of cells. Remember that using custom formatting operators doesn't change the content inside a cell, it simply changes the look of cell contents.
When using custom formatting, you can create up to four different expressions for one cell. Each of these expressions can combine a conditional statement and an actual format. The conditional describes the criteria that the cell needs to meet in order to use certain formatting.
Imagine a table of test scores. If a professor enters the correct expressions, Excel will automatically convert all scores over 90 to blue, all scores between 80 and 89 to purple and all other scores to red. The professor also wants the letter grade that the score reflects to appear in black in the same cell.
You can think of an expression as a math equation such as the one below.
An Expression = The Condition that Must be Met + What Formatting is Applied if the Condition is Met
Take a look at how the professor would write these expression in Excel. Remember that expressions are applied according to the order in which they appear, which means that the first expression always takes precedence.
Expression One: [Blue][>89]#;
This expression tells Excel to color all scores of 90 and above blue.
Expression Two: [Purple][>79]#;
Excel reads expressions in the order that they appear. That means that the first expression will automatically apply to all scores over 89. The second condition, which colors scores between 80 and 89 purple, can simply be written to reflect that any number greater than 79 should be colored purple. Excel will still format all values about 90 in blue so long as that's the first expression used.
Expression Three: [Red]#;
The third and fourth expressions can't have conditionals. This means that Excel will simply take any number that falls outside of the ranges that you've already specified and color them red. Any number between 0 and 79 will automatically be reformatted.
Expression Four: [Black]@
The fourth expression is reserved entirely for text formatting. While you can't describe a condition with this expression, you can still apply a set format to all text within the cell.
Here's how the expression looks when it's entered into a cell:
[Blue][>89]#;[Purple][>79]#;[Red]#;[Black]@
The four different expressions can also be used without conditionals to define positive numbers, negative numbers, zero values and text. If you use all four expressions, they'll be used for formatting in that order. Here's an example of how this works. The following would render positive numbers in blue, negative numbers in green, zero values in red and text in black.
[Blue]#;[Green]#;[Red]#;[Black]@
If you only use two expressions, the first will be used for positive and zero values while the second will be used for negative values. The following would render positive and zero values in red and negative values in yellow. Text will be rendered in blue.
[Red]#;[Yellow]#;[Blue]@
If you only use one expression, it will be applied to all values. Remember that only expressions followed by the @ symbol apply to text, and that only the last expression in a given string can be used for text.
Using the correct color formatting codes and condition codes will ensure that the desired formatting is applied to the all numbers that meet the conditions of that particular expression.
Colors can be described either by name or by their color code value. Color code values range from 1 to 56 and refer to individual colors on the legacy color palette. For example, the code [Color3] refers to the color red. Of course, the following names can also be used to specify colors.
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
When you combine a formatting code with a condition, you can ensure that the format will be applied only to values that meet your conditions. Condition codes always consist of a comparison operator and a corresponding value. If you wanted to render all numbers above 300 in magenta and all numbers below 300 in white, you'd write the following conditional code.
[Magenta][<=300];[White][>300]
Conditionals can also be used to change the formatting of only one value in a set of numbers. For example, a researcher might want to highlight everyone who reported all six major symptoms of a new flu strain in yellow. Here's the conditional code that would be used.
[Yellow][=6]
Operators are at the heart of custom formatting. Each operator alters the cell's appearance in a unique way. These operators are used in expressions to handle almost all aspects of formatting except for the physical attributes of the cell itself such as its border color.
0 (Zero)
If you use a zero in your number formatting code, it represents what's called a forced digit. It's a way to ensure that numbers conform to a certain length. A good example to use is how currency is represented. In most countries, something like $0.00 stands for price. Even if something is two-and-a-half dollars, the output is rendered as $2.50 instead of $2.5.
Here's what happens if you put the number 45.67945 in a cell and apply different codes using zero.
| Code | Output |
|---|---|
| 0.00 | 45.68 |
| 0.000 | 45.679 |
| 0.0000 | 45.6795 |
#
The pound sign is also a digit placeholder and works much like the zero symbol with one important exception. When you use pound, no extra digits are added at the end of a number even if extra digits are called for in the code. If you enter the number 10.5 into a cell that has a custom formatting code of #.##, the output will be 10.5 instead of 10.50.
?
The question mark is also a digit placeholder. However, it can be used to create an alignment digit so that all of the decimal points in all numbers are aligned in a column. This attribute is generally used to improve readability. If you used the code 0.0?, you could ensure that numbers with zero, one or two digits after the decimal are all aligned in the column. If you used the code 0.00?, you would expand alignment to include numbers with three digits after the decimal.
. (Period)
The period represents the decimal point. It's used as a placeholder that describes how many digits should appear both before and after the decimal point. Here's how the number 25.35 would look if different custom formatting codes with the period are used.
| Code | Output |
|---|---|
| 00.000 | 25.350 |
| 0000.000 | 0025.350 |
| 00000.00000 | 00025.35000 |
%
This operator is used to show percentages. If you write the expression #%, Excel multiplies the number in the cell by one hundred and places the percentage sign at the end. So, if you put 0.5 in the cell, the output would be 50%. If you put 1 in the cell, the output would be 100%. Note that the % operator can't be used in combination with the text operator (@).
, (Comma)
The comma is used as a thousands separator. It helps you describe how numbers will be displayed in relation to the thousands or millions value. Here's what it looks like when you format differ numbers with the comma code $$$,$$$.00.
| Input | Output |
|---|---|
| 123456 | 123,456.00 |
| 9876 | 9,876.00 |
| 570 | 570.00 |
E- E+ e- e+
Formatting codes using E-, E+, e- and e+ render numbers in scientific notation. This makes numbers that are preceded or followed by long string of zeroes easier to read. You need to use a digit placeholder to help define the values to which E is applied. Here's what it would look like if you used code 0.0E+00.
| Input | Output |
|---|---|
| 24000 | 2.4E+4 |
| 9800 | 9.8E+2 |
| 0.005 | 5.0E-3 |
*
The asterisk is used to repeat the character that follows it for a set number of time until it fills the width of the column. The actual number of time that the character will be repeated depends upon column width.
If you wanted a dashed line to appear to the left of every number in a given set of cells until the cell was filled, you'd enter the code *-0.00. Of course, you can adjust the period operator to change how many decimal places are displayed in each number.
You could also create a custom formatting code of 0.00*- to ensure that the column space to the right of the number would be filled with the dash character.
_ (Underscore)
The underscore is used to insert a space that is the width of the next character in the cell. It is often used to align positive and negative numbers in a column when the negative numbers are enclosed by parentheses. To ensure that the numbers 12.4, 8.9 and -13.5 were aligned properly, you would write the code _(00.0_);(00.0). Use the number of zeroes to the left and right of the decimal point that are needed to accommodate the largest number in your data set.
@
The @ symbol is used to explain how text in the cell will be handled. If you used the expression [Yellow]@, all of the text in the cell would be yellow. Remember that the @ operator can only be used one time in your entire expression string, and it must always appear in the last expression in the string.
Special date and time codes allow you to define how different input will be displayed. Refer to the following table to determine the various time and date formats you can create with custom formatting codes.
| Time Period | Desired Display | Code |
|---|---|---|
| Months | 1-12 | m |
| Months | 01-12 | mm |
| Months | Jan-Dec | mmm |
| Months | January-December | mmmm |
| Months | J-D | mmmmm |
| Days | 1-31 | d |
| Days | 01-31 | dd |
| Days | Sun-Sat | ddd |
| Days | Sunday-Saturday | dddd |
| Years | 00-99 | yy |
| Years | 1900-1999 | yyyy |
| Hours | 0-23 | h |
| Hours | 00-23 | hh |
| Minutes | 0-59 | m |
| Minutes | 00-59 | mm |
| Seconds | 0-59 | s |
| Seconds | 00-59 | ss |
| Time | 6 PM | h AM/PM |
| Time | 6:30 PM | h:mm AM/PM |
| Time | 6:30:45 P | h:mm:ss A/P |
| Time | 6:30:45.10 | h:mm:ss.00 |
| Elapsed Time in Hours and Minutes | 2:05 | [h]:mm |
| Elapsed Time in Minutes and Seconds | 25:19 | [mm]:ss |
| Elapsed Time in Seconds and Hundredths | 50.35 | [ss].00 |