The custom format feature in Excel allows the user to “paint” or mask a number, text, date or time value in a user defined custom format. Before we get into the details, it is important to remember that when you format a cell using this feature, it does not change the value present in the cell. The only change is of how the value appears to the user on the screen.

A custom format can be applied to a particular cell or a group of cell. Using this feature will retain the underlying data but will change its appearance on the screen. For example, suppose you have a figure, say 100, present in a particular cell of a column which stores the daily sales for a product. Now if you wanted to change the appearance of this cell and convert this to $’s (without actually changing the actual value), you can use the custom format feature so that the number is shown as $ 100 and not 100. The actual value in the cell remains 100. Similarly you can also change this cell to appear as “You scored 100” while the actual value remains 100. So as you can see, the custom format feature can help make cell values more descriptive and help the reader make better sense of the data. Let’s move on.

To apply custom format in Excel 2003 and earlier versions

Click on the menu “Format” -> “Cells…” or use the Excel shortcut Ctrl+1 from the keyboard. The following option box appears on the screen.

The custom format feature is the last one in the list of categories and provides a set in pre-defined commonly used custom formats including those for date, time and text. If you are just beginning to learn this feature, take a minute to familiarize yourself with it. Apply a set of formats to a cell and check how it appears. So, now with the basics out the way, let’s delve a bit deeper into the world of custom formatting.

Custom format – Upto 4 Expressions

The custom format option allows for upto 4 expressions to be specified for a given cell value. Here’s how it works:

You can specify either one or more expressions (upto a maximum of 4) as a custom format for a cell. Each of the expression is a combination of a conditional statement (which when evaluating to true triggers the format) followed by the actual format to use when the condition evaluates to true. In essence:

Expression = condition_when_to_use_format + format_to_use_when_true

Please remember that all the expressions are optional and you can specify any number of custom formats ranging from one to four. If only one expression is specified, it is assumed to be the expression 1. If two are specified, they are taken as expression 1 and 2 and so on and so forth. The four expressions are interpreted by Excel in the following order:

Expression 1 This specifies the format to use when the condition specified within the expression is met. The format to use in such a case is also specified alongwith. In the above example, the first expression [Red][>750]#; tells the application that when a number (the # sign) entered in the cell is greater than 750 (the [>750] part of the expression) then show the cell content in Red color (the [Red] part of the expression).

Expression 2
Like expression 1, the second expression specifies the format to use when the condition specified in the second expression is met alongwith the format to use. In the above example, the second expression [Blue][>500]#; tells the application that when a number (the # sign) entered in the cell is greater than 500 (the [>500] part of the expression) then show the cell content in Blue color (the [Blue] part of the expression).

Expression 3
The third expression is for all numerical values that don’t meet either the first or the second condition. Which means in the above example, if a value is not >750 and is not >500 then the value will be formatted using the third expression which specifies that the number should be color in Green ([Green]#). Please note that you will not be able to specify a condition for the third part – you can only specify the format to be used. The condition will automatically be formulated up as anything that does not meet either condition 1 and condition 2 specified in expression 1 and expression 2.

Expression 4
The fourth expression is reserved for the text. Again like expression 3, you can’t specify a condition for expression 4. The only thing you can specify is the format to use.

Custom Format Operators

The custom format operators help “sculpt” or structure the value in a cell so that the desired result is obtained. These operators are a key to understanding and working with the custom format feature in Excel.

The # operator

This is a placeholder for numbers. When you place this at a particular point in an expression, it will ensure that the number appears at the place. For example, say the value in a particular cell is 1000. If we were write a custom format as “The number “#” is greater than 100″, the outcome would be the cell value appearing as “The number 1000 is greater than 100” (without the quotes). However if you had the number 1000.55 rather than 1000, the outcome would be “The number 1001 is greater than 100”. If the format for decimal part is not specified, it would be rounded to the nearest whole number. In the above example, if you were to write the custom format as “The number “#.##” is greater than 100″, the outcome would be “The number 1000.55 is greater than 100”. (We will come to the decimal operator . a little later). Specifying a higher number of # than the length of the number (say #.#### even though the number is 1000.55), does not have any effect and any extra # are ignored.

The ? operator

Like the # operator, this also acts as a placeholder for numbers. The difference between # and ? in a custom format expression is that while the former ignores any extra placeholders, the ? operator creates a space (” ” without quotes) for any extra characters even if they are not present. Ok let’s look at an example. If you have a number like 1000.55, writing a custom format like “The number “#.?????” is greater than 100″ would give the result as “The number 1000.55 is greater than 100”. As you can see, extra spaces were inserted even though the the number had two decimal space only. If we replace the # operator with the ? operator, the result would have been “The number 1000.55 is greater than 100”. The later does not have any additional spaces. Those # operators that are extra are simply ignored.

The 0 operator

Like the # and ? operators, this again is a placeholder for numbers. The only difference is that any extra 0 operators specified in an expression would show on the screen. Continuing from the above example if you wrote an expression like ‘The number “0.00000” is greater than 100″, the result would be “The number 1000.55000 is greater than 100”. So in a way the 0 operator acts as the ? operator with the difference that any extra 0 specified as operators would show on the screen.

The @ operator

This is a placeholder for text and strings. You can use the @ operator only once in the entire custom format (only once in all 4 expressions). When used, the @ operator has to be present only in the last expression in the entire series. For example, if you use three expressions, the @ symbol can be used only in the third expression and if you use four expressions, the @ operator has to be present in the last expressions. So while #;@ is a valid custom format with two expressions, @;# is not a valid custom format since the @ operator has been wrongly placed in the first expression. When you try to use an invalid expression, Excel will throw the “Microsoft Excel cannot use the number format you typed” error. If this is used in the first expression (which also means that there can only be one expression in the entire custom format string), it works on both number and text. So if you specify [Red]@, any value in the cell would be colored in Red but if you specify [Red]#, only the numbers will be colored Red while the text values will remain unaffected. In this case, the numerical value is converted to text and then formatted according to the text format.

The * operator

Repeats next character to fill the entire width of the cell. So an expression like *=#.## in the above example (where the cell value is 1000.55) would generate the output as “======1000.55” on the screen with the character = repeating as many times as is sufficient to fill the entire cell. If you resize the column, additional fill characters will be appended automatically to fill up the available space.

The . operator

Decimal placeholder. When use with the #, ? or the 0 operator, specifies the position where the decimal should appear. For example, if you specify the custom format expression as 0000000.000000, (and when the cell value is 1000.55), the result would be 0001000.550000. The decimal in this case appears after 7 numerical places.

The % operator

The percentage sign. Formats the number as a percentage. In the above example if you specify the format as #%, the outcome would be 100055%. The % operator would internally multiple the number by 100 and then display the output followed by the % sign. You cannot specify the % operator with the text operator (@).

The \ operator

This serves as the escape character which means that any character specified after this character would appear on screen as such even though it may be reserved as an operator. So if you were to write an expression as #% the output is 100055%. But if you were to use the expression with the escape operator (\), and write something like #\%, the output would be 1001%. In the later case, the % is not the percentage sign but simply a literal or a symbol. Similarly writing #\0 would give you 10010. Bear in mind that this works only on one character which is on the immediately right of the escape character. The escape character itself does not appear on screen.

The [COLOR] operator

The [COLOR] operator serves to signify the color to be used. You have a list of eight readymade colors and you can choose between [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] and [Yellow]. The color only signifies the color of the text or number in the cell and does not affect the border, fill or other cell attributes. This is optional and if not specified is ignored and the default colors are used. So using [Red]General would color everything inside the cell in Red.

Using custom colors with the Custom Format Option

You are not limited to using just the eight colors mentioned above. By using the [Color n] option, you can paint the cells in any color that you want. So using [Color 25]General format, you can color the cell in the color that’s 25th in the Excel color Index.

The [condition] operator

The condition operator, typically specified within straight brackets [condition] is slightly different from any of the above operators in the sense that it does not format the cell value itself but actually governs when the custom formatting will be “triggered”. It specifies the condition under which a specific format will or will not be used. This is optional and if not specified is ignored. Say for example you wanted to color the cell value in Blue if the value is above 0 and color it Red if the value is below 0, you could write [Blue][>0]#;[Red][<0]#. Now if the cell value is a number and is greater than 0 it will appear in Blue and if less than 0 will appear in Red. Since we’ve not specified the third expression, Zero would remain unaffected and appear in the default color. Please note that the -ve sign before a number would disappear because we’ve only specified the format for the number and not the sign. To make the sign reappear, we can use the custom format as [Blue][>0]#;[Red][<0]-#.

Apart from these operators, you can use as alphanumerical and mathematical symbols which are typically interpreted as literals meaning they would appear on the screen as such without affecting the formatting.

You can download a sample worksheet with examples of custom format in Excel here or click on the button below:

This article dealt with the number and text custom format in Excel. Read more about custom format feature with date and time in Excel here.