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.
Is there an operator that will bold the text?
September 4th, 2009 at 4:50 pmHi RW,
Welcome. Don’t tihnk there’s an operator for bolding the text. Depending on how you would want to use it, using conditional formatting may be of help.
Regards,
September 5th, 2009 at 12:22 amHow can I format currency in Indian Rs. stlye like this way (1,25,15,205.00)
October 23rd, 2010 at 11:27 amI am using Olap reports to pull in information on Excel spreadsheet. When I am using numbers such as 104000010, the information comes back as #N/A. If I manually put a ‘ in front of the number such as ‘104000010 the information will pull in from the Olap report. How do I format the numbers to come up with that character (‘) in front of all the numbers? I have 1700 items.
February 11th, 2011 at 9:56 amThank you so much for describing these operators in detail. I truly appreciate it!
October 19th, 2011 at 6:43 amA B C | A B C
August 30th, 2013 at 3:18 am1 Rate qty Amt | 1 Rate Amt
2 20 2 40 | 2 20 40
3 30 3 90 | 3 30 90
4 40 4 160 | 4 40 160 here i will type in b2 to b4 only qty as 2,3,.. so that automatically it must get multiplied & give Ans as 40, 90, 160,….
You explain how to do it for 2003 and earlier, isnt there supposed to be a part that say how to do it in excel 2007? Maybe i missunderstood something here. I can provide a guide for 2010 (http://www.excel-aid.com/formatting-numbers-excel-custom-numbers-format.html if you are interested), but not for 2007 or 2013. But that guide is not as indepth as Ajay`s, maybe its helpful as a starter guide. Anyway, good article Ajay, its a shame that its a little dated.
March 27th, 2014 at 5:45 amI am using excel 2013 and I setup a custom format of #### #### #### #### #### and when I entered the number excel changes last 5 digit to zeroes.
Anybody who have idea why?
June 29th, 2015 at 5:38 pmHi,
November 27th, 2017 at 2:11 amI am wanting to input a string of alphabetical letters and numerical digits into a cell, and want them to be displayed with a space between each – but the display does not change from the continuous string of letters and digits I entered into the cell.
I used #” “#” “# up to a maximum of 16 # for numerical digits (123456789123456789) and the display shows 123 4 5 6 7 8 9 1 2 3 4 5 6 0 0 0.
How can I get the Custom setting to show up to 20 letters and digits separated by a space between each ?
Have you heard that contact form messages like these can actually be a great way to get more sales for your site? How can we do it? Very simple, we craft an ad message like this one for your site and we submit it to millions contact forms on any website and in any business category or area you need. Do these types of ads work? Since you’ve ready my entire message then you’re proof that they do! The awesome thing is, this won’t cost you much more than $25 a week! Want to find out more? shoot us a quick email to: ErnestLilliana57358@gmail.com
January 10th, 2021 at 12:34 pm