What you see isn't what Excel knows


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

Late last week 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 you’ll find it under Excel options | Advanced

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 new bug is 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.

subs profile e1563205311409 - What you see isn't what Excel knows
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address