Dustin Wheeler

Dustin Wheeler
Dustin is a technology-driven CPA in Orem, Utah.

Social Media

Follow me on Twitter

Excel's set precision as displayed option

When I was in first grade, I learned that 2 + 2 = 4.  Excel, who I have come to know and trust, thinks the answer is five.  How is this possible?

Two plus two is ... five?The truth is told after I increase the decimal places shown for each of these cells.  The twos on the spreadsheet really aren’t twos after all.  They’re 2.43 and 2.35, which both round down to two.  The sum, 4.78, rounds up to five.

2.43 + 2.35 = 4.78To fix this kind of rounding difference in Excel 2010, click on the File tab and go to Options.  On the left side of the window that appears, click on Advanced.   Scroll down and check the box next to “set precision as displayed.”

Set Precision As DisplayedA window will appear, warning that “data will permanently lose accuracy.”  For example, when I decrease the decimal so that all of the numbers are shown as whole numbers, they actually become whole numbers (increase the decimal again, and both twos will be shown as 2.00).  Don’t select this option if you need complete data accuracy in your spreadsheet.  I can’t be held responsible for a few lost pennies.

2 + 2 = 4This option is especially helpful for an accountant whose report must always foot.

Thanks to Val Steed of K2 Enterprises for showing this in a session at the 2011 AICPA TECH+ Information Technology Conference.

12 comments to Excel’s set precision as displayed option

  • Good tip Dustin. This option is also available in Excel 2007.

    I encountered this problem a long time ago. My solution, especially when drafting a financial statement that is linked to a trial balance database, has always been to use the @round formula. I also learned to keep my trial balances in whole dollars!

  • Thanks, Joel. Like you, I have become quite experienced with the round formula.

  • Nick

    Hi,

    Your answer is precisely the solution for which I have searched high and low across the web. However, because I use Apple’s ‘Numbers’ and also Microsoft Excel for Macs (2008) the problem remain. The File tab on the Mac version does not have ‘Options’ and I can’t find a ‘Set Precision As Displayed’ box to check.

    It would help tremendously of you could extend your answer to cover Macs.

    Many thanks.

    Regards,

    Nick

  • j.e. mcgimpsey

    Your example demonstrates a common fundamental misunderstanding of Excel – changing the displayed number of digits doesn’t do anything to the stored value which is used in calculations.

    Setting Precision as Displayed is usually a REALLY bad idea – since XL will actually change the values you enter, unless you set up your workbook very carefully, you’ll never know where the precision was lost. You’re usually much better off using the ROUND() function, e.g.: =ROUND(A1,2) + ROUND(A2, 2), which at least hints at where potential inaccuracies may creep in. Your hypothetical accountant will never be able to find out why he’s one off if the base data is permanently lost.

    To Nick’s question: Preferences/Calculation, check the Precision as Displayed checkbox.

  • John

    How can I set this in Excel 2007 ?

    In 2007, I go to options – advanced, but then there is no check box for set precision.

    Please help… my accountant is losing his patience with me. So annoying that the world’s most famous calculating spreadsheet does not work…..

  • John, I’m not sure why you’re not seeing it, but it is there in Excel 2007. Look under “When calculating this workbook.”

  • Jeffrey A. Dalton, MBA

    I usually use the =Trunc(Round(a2,2),2) That way I know exactly which fields I want to be “footed” with precision as displayed on screen without compromising data somewhere else in the table or on other tabs. This was a huge issue on Government Costing contracts where 100′s of pages had to match and a lot of % calculations are used. This way made every calculation on the reports match while retaining the precision up until the items on each report was calculated. I guess I am old school I have had too many issues with options applied globally, I prefer the control.

  • Jeffrey, I also often use the round function like you, but I’ve found the “set precision as displayed” option useful in certain situations. It is far easier to select the option than enter formulas when rounding numbers throughout an entire workbook.

  • Andrea

    Instructions to set precision as displayed for mac can be found at
    http://support.microsoft.com/kb/181918

  • Geraldine

    Can you set this for an individual sheet rather than work book, I don’t want to use Trunc or Round

  • Kael Montas

    You made my day. Just the solution I’m looking for.

  • Kael Montas

    How can I convert PDF files to excel, word, etc

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>