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.

Tags: , ,

Trackback from your site.

Comments (17)

  • Avatar

    Joel Ungar

    |

    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!

    Reply

  • Avatar

    Dustin Wheeler

    |

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

    Reply

  • Avatar

    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

    Reply

  • Avatar

    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.

    Reply

  • Avatar

    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…..

    Reply

  • Avatar

    Dustin Wheeler, CPA

    |

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

    Reply

  • Avatar

    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.

    Reply

  • Avatar

    Dustin Wheeler, CPA

    |

    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.

    Reply

  • Avatar

    Geraldine

    |

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

    Reply

  • Avatar

    Kael Montas

    |

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

    Reply

  • Avatar

    Kael Montas

    |

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

    Reply

  • Avatar

    Paul D. CPA

    |

    I convert PDF files to Excel and Word, but you need the full version of Adobe, not just the reader. Even with that program, I have had varying degrees of success. Sometime I get nice files, other times, I get output with mixed width columns, numbers as text with leading spaces and assorted data parsing that can be frustrating to recombine. With mixes of =Left, =Mid and =Right along with the =Trim command I have been successful a large percent of the time. But not always. If you know somebody who has he full version of Adobe ask them to try the conversion, particularly if it is a one-off cevent. That aside, the full version is very useful for a lot of reasons. I add and remove pages, combine PDFs to one file, add Text to a PDF that isn’t a fill-in document. Good luck.

    Reply

  • Avatar

    Ms. Hill

    |

    I am not seeing set precision as displayed in 2013.

    Reply

  • Avatar

    Thanuka

    |

    Thanks buddy

    Reply

  • Avatar

    Blair LittleJohn

    |

    I have an Excel worksheet with many cells spread throughout that are formatted for percent – 2 decimal places and the percent is calculated by a formula. My friend has the same worksheet, with exactly the same data. Sometime, but not always, when totaling a column of percentages I get an answer that is different from his by +/-.01. I’m wondering if his Excel percent format is set to round up and mine to round down. I’m unable to find a way to set the Excel rounding default, if there is one. Can anyone help me?

    Reply

  • Avatar

    Unajet

    |

    10/20/2020
    So after trying to figure out why my cents were adding wrong, I saw this and decided to chance the fix.
    It worked.
    So, File, Options, Advanced, When Calculating This Workbook, check Set Precision As Displayed
    Now, my .02 + .02 = .03 is now gone. Yeah.
    Also, decide which way your cells are going to be – number, currency, or accounting. Keep them the same.

    Reply

Leave a comment