Custom Formatting
Custom formatting is one of those skills that doesn’t take much effort to master, but can really add to the professional appearance of your spreadsheet. With it you can manipulate the format of text and numbers to suit, whether it is the way a date is presented or what colour text will be when a cell contains a negative number. Custom formatting is much the same in any spreadsheet application, whether that is Excel, LibreOffice Calc, or Gnumeric (to name but a few, though for the purposes of this post we’ll be focusing on Excel), and works by using a series of symbols to represent elements like hours, decimal places, and text. By learning these symbols you can create many different formatting variations.
To bring up the custom formatting prompt, highlight the cell you wish to format and press CTRL+1. Select the Custom category and enter your format code into the field below ‘Type:’.
The custom formatting prompt
Below are some of the symbols used and examples of things that can be done with custom formatting.
Date and Time
Symbols:
| h | m | s | d | m | y |
| hour | minute | second | day | month | year |
Examples:
| d-m-y | 8-11-12 |
| dd/mm/yy | 08-11-12 |
| ddd mmm yyyy | Thu Nov 2012 |
| dddd mmmm yyyy | Thursday November 2012 |
| dd mmmmm yy | 08 N 12 |
| h m s | 11 45 12 |
| hh:mm:ss | 11:45:12 |
| m/ss | 45/12 |
Notice something in the tables above? The same letter, ‘m’, is used for both month and minute, so how is the spreadsheet able to differentiate between the two? By default, the letter m is treated as month, but preceding it with ‘h’ or following it with ‘s’ tells the spreadsheet to treat the value as a time and adjust the figure for m accordingly.
Number
Symbols:
| #,## | inserts a thousands symbol when the value is greater than 999 |
| 0 | acts as a placeholder for any digits entered |
| 0.0 | Similar to the above, but dictates the number of decimal points |
| ? | A placeholder like 0, but adds a space around insignificant digits so that decimals points line up between values |
| E+0 | Used to show the value in exponent form. |
Examples:
| 000-000-0000 | 031-865-9034 |
| 0.0 | 24.7 |
| #,##0.00 | 3,294.67 |
| 0.0? | 2.9 and 2.94 will line up when stacked vertically |
| 0.00E+0 | 2.45E+5 |
Text
Symbols:
| “” | Used to fix text in a cell |
| @ | Section for text value |
| * | Used to repeat a symbol across the width of a cell. Placed before the symbol to be repeated |
| _ | Used to create a space the width of one character |
Examples:
| 0 ” km” | 74 km |
| @_*> | To the batmobile! >>>>> |
Colour
Symbols:
| [Blue], [Black], [Red], etc | Uses the generic colour selected (limited colours) |
| [Color10], [Color15], [Color17], etc | Uses the corresponding colour from the built-in colour palette |
Examples:
| [Blue] | I went to the store today |
| [Color53]#,##0.00 | 4283.97 |
You can find an excellent breakdown of the colour palette here: http://dmcritchie.mvps.org/excel/colors.htm
Positive and Negative numbers
Another custom formatting feature is to change how the contents of a cell are displayed depending on what value the cell contains. Semi-colons divide this type of formatting into four divisions:
| if value positive; if value negative; if value zero; if value text | Determines how a cell is formatted, depending on the value it contains |
You do not necessarily need to specify anything for the zero and text divisions.
Example:
| $#,##0.00 ” Profit”;$#,##0.00 ” Loss”;”Break Even” | ‘$1,357.23 Profit’ or ‘$3,257.10 Loss’ or ‘Break Even’ |
Conditions
Like the above, you can also specify the exact conditions under which cell formatting will change:
| [condition] action; [condition] action; | Performs an action depending on the condition given.Works like an if statement |
Notice that the conditions must be typed in using square brackets, so as not to be confused with the positive and negative number formatting shown above. You should end up with something like this:
| [<2000][Red]; [>2000][Color10]; | 1500 or 2500 or 2000 |
That covers many of the options, but if you would like to find out more about custom formatting in Excel you can visit the following help page on the Microsoft website: http://office.microsoft.com/en-gb/excel-help/create-or-delete-a-custom-number-format-HP005199500.aspx.
With a little practice you will be a formatting master in no time!
David Richardson
April 15, 2014 @ 12:04 pm
When using time formats, I find one of the most important custom format symbols is the “[” used around h, m, or s (usually h) to prevent overflow when performing calculations. With the format “h:mm:ss” if you add 14 hours and 12 hours, you get 2 hours. with “[h]:mm:ss” you get the “correct” 26 hours.
Roger Smith
April 15, 2014 @ 1:54 pm
Thanks Dave! That’s an excellent tip.