How to avoid misleading numbers

Excel 365 2013 2010 2007 2003
Applying a number format to a cell doesn't change the value, but only how the value appears in the worksheet. Formatting can play a joke with you, e.g., the sum of values seems incorrect because Excel displays a limited number of decimal places, and their sum is not equal to the real sum.

For example, if a cell contains .874543, you might format it to appear as 87%. If that cell is used in a formula, the formula uses the full value (.874543), not the displayed value (.87).

In some situations, formatting may cause Excel to display calculation results that appear incorrect, such as when totaling numbers with decimal places. For example, if values are formatted to display two decimal places, you may not see the actual numbers that are used in calculations. But because Excel uses the full precision of the values in its formula, the sum of two values may appear to be incorrect.

Several solutions to this problem are available:

  • You can format the cells to display more decimal places.
  • You can use the ROUND function on individual numbers and specify the number of decimal places Excel should round to.
  • Or you can instruct Excel to change the worksheet values to match their displayed format.

To do this, do the following:

   1.   On the File tab, click the Options button:

Excel 2016 Options

   2.   On the Advanced tab, in the When calculating this workbook group, check the Set precision as displayed checkbox:

Advanced options in Excel 2016

See also this tip in French: Comment éviter les nombres trompeurs.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.