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.

This entry was posted in Quick tips and tagged . Bookmark the permalink.