62. Excel Tips - Small Numbers in Excel

Nick's picture


Small numbers look odd in Excel.

A Very small number will look something like this: 3.38E-09

- This is scientific notation meaning 3.48*10^-9 (3.48 times 10 to the power of -9, or 3.48*0.000000001) The reason Excel does this (I think) is to be able to display a small number in a small space.

- If the default were 0.00000000348023, the column width would have to be increased to be able to display the numbers.

There are several ways to treat small numbers..

These are my preferred:

  1. If all numbers that you are looking at are of the same order, just scale them up and rename the output. i.e. Multiply by 10^9, and change the heading.
  2. Use a custom number format: "0.00000000000000" to display the whole number.
  3. Round the number - if you only care abt things that are >1 for example, just use the ROUND formula.

Here's how it looks in Excel:

small-numbers-excel 

Download sheet to practise dealing with small numbers in excel

 

 

Training Video on Dealing with small numbers in Excel:

 

AttachmentSize
small-numbers-excel.xls19 KB