Fixed Decimal Places

Macro Tip for Fixed Decimal

I have this Fixed Decimal Place tip, which is below,  in my Must Know Excel Tips Tricks and Tools for the CPA.Ebook.  Eric Robinson read my tip and sent me his own. He shared a macro he uses to switch back and forth between no decimal place and  decimal places.  Switching back and forth can be tedious if you are, for example, entering check numbers (no decimal) and check amounts (2 decimal places). I have added it below. Thanks so much  Eric.

Sub ToggleAutoDecimal()

'

' ToggleAutoDecimal Macro

' Macro recorded by Eric Robinson to handle fixed decimal places

'

    Application.FixedDecimal = Not (Application.FixedDecimal)

   

End Sub

Fixed Decimal Place

I was teaching an Excel class a few years back and briefly mentioned the Fixed Decimal Place feature. My goodness, half the class was enthralled with it.  It seemed to be the biggest thing they had seen since sliced bread! (Hmm. I guess I need to come up with a more up to date expression particularly since I buy unsliced bread.)

If you are a numbers person then you too may appreciate this feature. If you need to enter columns of numbers, with a fixed number of decimals, into a worksheet then make your job a lot easier by having Excel enter the decimals for you.  For example,  I set the fixed decimal feature to 2 and then in Cell B2, I typed 1234 and Excel displays it as 12.34. It can be a bit of a time saver.

When you turn the feature on, the default is 2 decimal places but you can change it to whatever you want.

=======================================================================

If you are constantly dealing with large numbers and would like them to be truncated to the same decimal place then you can also use this feature.

In the example to the left, I typed original values in Column A, I then turned on the fixed decimal place feature to 2 decimal places and when I typed in the same numbers, Excel changed them.
So, when I typed 500000 into cell B2, Excel changed it to  5000. In Column C, I changed the fixed decimal place feature to 3 and when I typed in 500,000, it changed it to 500.
You get the idea.  So, this may really excite you and you may find it useful however please be aware that Excel now considers cell C2 to be 500. If  I add C2 + 0, the answer I get is 500 not 500,000 even if you have turned the fixed decimal place feature off.

If you don't want every number in your spreadsheet truncated then you need to type the decimal place in yourself.
For example, if the fixed decimal feature is on and set to 2 decimal places, if I type 200.00 in a cell, Excel will change it to 200 however if I type 200 into a cell, Excel will display it as 2.

Here are the steps in case you want to look at it
 Excel 2003

• Select the Tools menu.
• Select Options.
• Select the Edit tab.
• Select the Fixed decimal Places checkbox.
• Select the appropriate number of places.
• Click OK.

Other Versions:

• Click  File (Excel 2010-2016) or  Office Button (Excel 2007). 
• Select Options (at the very bottom of the dialog box).
• Click on Advanced.

• Put a checkmark in Automatically insert a decimal point and select the number of Places.
• Click OK..

Have a great day!