Permanently Turning On Set Precision As Displayed

by Allen Wyatt
(last updated May 30, 2020)

2

Tami much prefers to have "Set Precision As Displayed" enabled for all of her workbooks. She does payroll and works with dollars for all calculations. When this setting is not in force and she manually uses a calculator to double-check the math in the worksheet, it's often off by a few cents. The only solution is if the setting is turned on; then everything matches. It is a hassle to do this with every single workbook Tami creates, so she's hoping there is a way to have this setting turned on permanently.

There is no way to turn it on permanently within Excel itself, but you can do so with macros. For instance, you could add the following macro to a workbook:

Private Sub Workbook_Open()
    ThisWorkbook.PrecisionAsDisplayed = True
End Sub

The macro should be placed in the This Workbook module, so it will run every time the workbook is opened. You could, if you want, also add this to a new, blank workbook and then save that workbook as a template named Book.xltm in the XLStart folder. Doing so specifies, for Excel, the default template you want to use when creating a new workbook. Thus, all new workbooks would include this simple one-line macro to set the precision.

Now, that being said, many people strongly advise against even turning on the Set Precision As Displayed option. Why? Because it permanently affects the data in your workbook. For instance, try this little exercise:

  1. Set the display of a cell in a worksheet to 5 decimal places. (Let's say you are doing this using cell B3.)
  2. Make sure Set Precision As Displayed is turned on.
  3. Enter the value 1.23456 into cell B3.
  4. Now change the display of cell B3 to 2 decimal places. It should now show 1.23.
  5. Again, change the display of cell B3 back to 5 decimal places. It should now show 1.23000.

Those last digits (456) are now gone and lost forever; they cannot be recovered. It may be better to adjust your formulas to include the ROUND function so that rounding to 2 decimal places is done only in those instances where you need it done, instead of everywhere.

If you do decide you want to use Set Precision As Displayed, you might be better served to set up an easy way to change the setting on a workbook-by-workbook basis. This could be done with a single macro added to your Personal workbook:

Sub TogglePrecision()
    Dim sTemp As String

    sTemp = "Precision as Displayed has been "
    With ActiveWorkbook
        If .PrecisionAsDisplayed Then
            .PrecisionAsDisplayed = False
            sTemp = sTemp & "DISABLED"
        Else
            .PrecisionAsDisplayed = True
            sTemp = sTemp & "ENABLED"
        End If
    End With
    MsgBox sTemp
End Sub

Now you can add the macro to your Quick Access Toolbar and click it to turn the setting on and off. The macro just toggles the setting from what it is currently set to, and then displays a message indicating the newly changed state of the setting.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13765) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding DATE Field Formatting

One of the most commonly used fields is the DATE field. You can specify how the DATE field displays the current date by ...

Discover More

Changing from Pirated to Permitted Software

When you install Microsoft Office, you are required to enter a product key that unlocks the software for your use. This ...

Discover More

Word Styles and Templates

Styles are at the heart of Word's formatting power. Understanding how to use styles can greatly increase your ability to ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Choosing Direction after Enter On a Workbook Basis

Excel lets you specify how it should behave when you press Enter. If you change this behavior, Excel assumes you want it ...

Discover More

Tab Key Won't Move from Cell to Cell in Locked Worksheet

Normally the Tab key can be used to move from one cell to another in Excel. If this cell movement doesn't work for you, ...

Discover More

Cell Movement After Enter

What happens when you press Enter in a cell depends on how you have Excel configured. Here's the way you can control the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 4?

2020-06-01 15:40:54

Roy

Actually, some things, like payroll, NEED to be PAD, universally.

Bear in mind when I say that that I have fought overrounding since I was in school in the seventies. It was standard since the calculation devices were slide rules and paper and pen. Slide rules cannot have more than some depth, and got darned expensive (I just missed that era, by a year or two, but its practices still ruled the day), and if you think today's kids are feckless and lazy, I offer you the 1972 student when told he could use interim rounding rather than use longer interim values with pen and paper. Sadly, the teachers used it too and I had to argue a lot of answers when their WRONG answers varied from mine.

That said, the moment a result exists in payroll, it exists as such forever. There is absolutely no carrying through longer decimals because they might add a penny later. Or subtract one. The moment a calculation is done, it is fixed permanently in dollars and cents, two decimal places, no more, ever.

I still use ROUND in its several guises, for every step along the way. I used to use the GET.CELL function (option 53) from the Excel 4 macros, but when VBA came about and those function became "the old Excel 4 macros" I converted. Didn't know they'd last. It was made into a chore anyway, so I would've bitten that bullet anyway.

But once a payroll value is computed, it is no longer "an interim result": it is set in stone, and under NO circumstance I've ever seen (35 years) would you want to carry the extra digits forward.

Most anything else though, that I have worked with, not a problem. Earnings for an article from many discrete transactions, well, that'd be one like payroll, but USUALLY, that data comes permanently rounded to begin with. Usually. However, when it comes with discounting of various sorts, especially if one applies across several items for an exact money amount, applied, it can depend a lot on the situation, but usually needs permanently rounded after application.

Some items, like sales tax calculations, are required by some (all? definitely mine, rounding up from the fourth decimal place) states to be rounded up, no matter what. Then have to be kept that way because you pay the higher of "collected" or "calculated from applicable sales" and the former is always higher. Wouldn't match up if kept rounded as it'd just equal the latter. Given how Ohio stresses this, it seems likely they'd be chary about explanations that mentioned "you don't want to permanently round things" preferring to think you were trying to beat them out of some pennies. Then you'd be a nasty thief, stealing from, say, the WIC program, and that'd color your whole audit. So many things involving money seem to fit the "permanently round any defined result no matter how "interim" it will eventually be" criteria.


2020-05-30 11:35:37

J. Woolley

I agree that universally enabling "Set Precision As Displayed" is not a good idea. If it must be done, the Tip provides a good way as a template for NEW workbooks or on demand via a macro. But if you really want to do it for each workbook (including an OLD workbook) as it is opened, here is a procedure:

1. Open a new workbook, then press Alt+F11 to open Visual Basic Editor.
2. Select ThisWorkbook and press F7 to View Code.
3. Enter the following code (see http://www.cpearson.com/Excel/AppEvent.aspx):
Private WithEvents MyAppEvents As Application
Private Sub Workbook_Open()
Set MyAppEvents = Application
End Sub
Private Sub MyAppEvents_WorkbookOpen(ByVal Wb As Workbook)
If Not (Wb Is ThisWorkbook) Then Wb.PrecisionAsDisplayed = True
End Sub
4. Select VBAProject and press F4, then change the Name property from VBAProject to MyAppEvents.
5. Close the Visual Basic Editor.
6. Press F12 and pick Save as type: Excel Add-in (*.xlam), then File name: MyAppEvents. Before clicking Save, copy the folder location (default is like C:\Users\[Name]\AppData\Roaming\Microsoft\AddIns).
7. Close Excel, then reopen Excel.
8. Pick Options > Trust Center > Trust Center Settings… > Trusted Locations > Add new location…, then add the folder location copied from step #6.
9. Pick Options > Add-ins > Manage: Excel Add-ins > Go… > Browse…, then select MyAppEvents.xlam.

As long as the MyAppEvents add-in is enabled, every workbook that is opened will have PrecisionAsDisplayed set True. This will persist after the workbook is saved, even if the MyAppEvents add-in is subsequently disabled.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.