Limit Number Of Decimal Places In Entire Excel Workbook

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.

Share The Knowledge

Random Posts

Leave a Reply