Categorized | excel tips

Custom Format in Excel – How to Format Numbers and Text




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.
custom-format-in-excel-2003

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:

custom-format-number-and-text-in-excel

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:


custom format in excel

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.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. RW wrote:

    Is there an operator that will bold the text?

  2. Ajay wrote:

    Hi 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,

  3. Akash wrote:

    How can I format currency in Indian Rs. stlye like this way (1,25,15,205.00)

  4. Carla wrote:

    I 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.

  5. anon wrote:

    Thank you so much for describing these operators in detail. I truly appreciate it!

  6. hylo wrote:

    A B C | A B C
    1 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,….

  7. Mary wrote:

    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.

  1. Custom Format in Excel – How to Format Numbers and Text | da TaB is On | RARWarez Blog wrote:

    [...] the original here: Custom Format in Excel – How to Format Numbers and Text | da TaB is On Tags: a-user-defined, allows-the, allows-the-user, and-made, and-unique, are-then, dictation-, [...]

  2. Custom Format to Left Align Text Symbol and Right Align Numbers | da TaB is On wrote:

    [...] Custom Format Numbers and Text we discussed using the custom format feature on numbers and text. Here is a neat little trick using [...]

  3. Conditional Formatting + Custom Format = More than Three Colors | da TaB is On wrote:

    [...] formatting complimented by custom format can help us make use of more than three colors while formatting numbers. Conditional formatting can [...]

  4. Chart Label Trick - Converting Chart Axis Labels to Table | da TaB is On wrote:

    [...] so that they appear when the sales are up or down respectively. The TEXT formula helps us shape or format numbers are desired. It has two parts – the first is the expression/string/number that needs to be [...]

  5. Custom Format to Color Chart Axis and Chart Labels | da TaB is On wrote:

    [...] Custom Format can be used to format numbers, text, dates and time. The technique can be extended to charts as well where the custom format feature can be used to color the chart axis and chart labels. [...]

  6. Custom Format in Excel - How to Format Date and Time | Excel & VBA - da Tab Is On wrote:

    [...] you haven’t had a chance to read through the first part where we talked about basics of the custom format feature, it is highly recommended that you do so before coming back and finishing this one [...]

  7. Volatility Chart in Excel - Lessons in Chart Design From NY Times | Excel & VBA - da Tab Is On wrote:

    [...] Excel you can add custom format the chart’s axis so that they become easier to grasp for the reader. Here’s how we [...]

  8. Multiple Scale Chart - Adding Customized Scale to Each Category in a Chart | Excel & VBA - da Tab Is On wrote:

    [...] and ‘max’ strings rather than values from from 0 to 10. How. Simple. You can use custom format to change the original scale on the X-axis of the chart to show any text /string that you want [...]

  9. Anonymous wrote:

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel