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.