In this post we will see how we could limit the number of decimal places for all the numerical values in the entire excel workbook.
Before we start, I would like to tell you that there is no global parameter that we can set in excel that will restrict/limit the number of decimal places in excel, however we can use a small code of 4 lines in VBA to compel excel to restrict the decimal places to a predetermined number.
Let’s see how we do it.
Step 1
Suppose we have 2 excel sheet with numerical values and these values are having different number formatting to show the number of decimal places as shown in the picture below.
Sheet 1
Sheet 2
As you can see, the numbers have different formatting to show the decimal places.
Step 2
We will create a macro in excel to restrict the number of decimal places
Open VB editor from Developer tab in excel (Alt+F11).
Step 3
Double Click on ‘ThisWorkbok’ in which we will write our VBA code to restrict the number of decimal places for all the worksheet.
Step 4
Copy and paste below line of codes in ‘ThisWorkbook’.
Sub RestrictNumerFormat()
For Each ws In Worksheets
ws.Activate
Cells.NumberFormat = “0.00”
Next ws
End Sub
Let’s understand what the code is doing here
For Each ws In Worksheets
Next ws
With the help of For each loop in VBA, we are going through each of the sheet in excel.
ws.Activate
Cells.NumberFormat = “0.00”
With the help of above two lines of code, we are activating the sheet and defining the number format to show only two decimal places. You can change the “0.00” to “0.000” if you want to show three decimal places for all the numeric values in your excel workbook.
Step 5
Run the program by pressing F5 or by clicking play button just below Debug tab in VB editor.
Step 6
You can see now that all the numerical values are showing two decimal places.
Step 7
If you want it to be a global setting so that excel changes the number formatting as soon as someone opens the excel workbook, write the program in workbook open event which will run the macro whenever the excel workbook is opened now.
You can see in the below code that we have used Private Sub Workbook_Open() event which will run it and change the excel number formatting in the entire workbook whenever it is opened.
Private Sub Workbook_Open()
For Each ws In Worksheets
ws.Activate
Cells.NumberFormat = “0.000”
Next ws
End Sub
Hope this helps.
