Permanently Turning On Set Precision As Displayed

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.

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