Useful custom formats

Some neat Excel custom formats

A few nice custom Excel formats for you to use.

<;;;> Stores a value, but doesn’t display it
<00000> Always stores five digits. So 453 becomes 00453. Useful for US zip codes
<[Black] General> To hide error messages (e.g. #DIV/0!). If you make your standard font colour the same as the background (usually white), then this will kick in to give non-error cells a black font
<0;-0;;@> Not sure why this works, but it hides zero values
[#.???] This will line up your decimal places in a column, and display three decimal places, but only if they’re significant. "3.2" will display as "3.2", not "3.200". The only slight issue is that "3" will display as "3."
< @> Put a bunch of spaces before the @, and these will appear at the beginning of the cell, resulting in a padding
<#,###"km"> Type in 1000, and it will appear as 1,000km, but you can still use the value to do calculations
<@*.> This will pad your cell out with dots to the width of the cell. So "Excel" will appear as "Excel…………". Might be useful for tables of contents
<#,###,, "M"> Again, not sure how this works, but it divides the value by a million and displays a trailing "M"
<[<=2]"Low"* 0;[>=4]"High"* 0;"Average"* 0> This will display the word at the left of the cell, and the value at the right

To use them, go to Format | Cells and click Custom. Then type the bit between the < and the > above to get the desired result.

The beauty about the above is that only change the display format. They don’t affect the value stored.