The big difference between what Excel shows and what Excel knows

Office-Watch.comLast updated: 8 December 2020

Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Office 2007,Office 2010,Office 2013,Office 2016,Office 2019,Office 365

There’s a big difference between Excel’s stored value for a cell and the displayed value happens all the time and it is important to understand.

Back in the day we detailed a bug in Excel 2007 where the wrong number appears on the screen yet Excel ‘knows’ the right value for other calculations. This difference between Excel’s stored value for a cell and the displayed value happens all the time and it is important to understand.

Excel always calculates formulas to a high precision – 15 significant digits, probably more than you need so the displayed number is only part of what Excel uses to perform maths.

Many Excel worksheets involve money, displaying dollars and cents (ie two decimal places) but Excel is calculating based on fractions of a cent – and those fractions can add up.

Here’s the simple example Microsoft uses:

You have worked out the cost price of a product to $20.005 – Excel will display that as $20.01 because of rounding in Currency Format.

Add the cost price of two products each $20.005 and Excel will show a total of $40.01 – not $40.02 (ie the displayed values $20.01 plus $20.01).

A similar thing happens when adding up percentages – often the total won’t be 100% because of rounding.

The most extreme example of this is Excel’s display of dates. As we’ve noted previously in Office for Mere Mortals – all Excel dates are actually numbers (integers are days and fractions hours/minutes). It is only the display formatting of the cell that makes a date appear.

See what Excel sees

There’s no direct way to expose the ‘real’ numbers Excel uses in cell because all cells have some formatting to transform the number for human eyes.

If you’re trying to uncover a worksheet anomaly you can change the cell formatting to the ‘General’ format that will adapt to show as much of the cells value as possible.

Calculate what you see

Excel has an option to change the precision from the stored value to displayed value.

In Excel 2007 and later you’ll find it under Excel options | Advanced | Set Precision as displayed

In Excel 2003 the same choice is as Tools | Options | Calculation:

But be aware that changing to displayed precision may have unwanted consequences too – for example fractional values are dropped which can have an unexpected affect on calculations with large values. For example a price of $10.004 displays as $10.00 – if you work out the cost of 100,000 units the result will be different by $500 depending on which precision you use.

‘Precision as displayed’ applies to an entire worksheet and is usually avoided because much mathematical accuracy is lost. Someone looking at the worksheet and innocently changing a cell style could affect the results of the worksheet.

Most Excel users prefer to control precision in their formulas as required.

Changing precision in a formula

Aside from changing the precision for an entire worksheet there are some functions you can use to force the precision of numbers in a formula.

Some functions to workaround Excel display vs stored value:

Round() this will round the number to precision you want.

Int() will convert a number to an integer by rounding

Trunc() will convert a number to an integer by truncating the fractional part.

What about the bug?

The bug was in Excel 2007 only – some formulas that have specific number results around the 65,535 mark will show ” 100,000 ” instead of the true value.

Microsoft has released a patch to deal with this bug.