### Dustin Wheeler

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

# 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?

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.

To 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.”

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

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

### 16 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.

• 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

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

• Ms. Hill

I am not seeing set precision as displayed in 2013.

• Thanuka

Thanks buddy

• 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?