# Set Cell Values to 2 Decimal Points

#### Trevor G

##### Well-known Member
I have a sheet and columns A to V have values that have more than 2 decimal points how can I strip away the excessive decimal points, there are positive and negative values via VBA over 30000 rows.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### obiron

##### Active Member
It depends on what you mean by 'Set'

You can format the cells to numerical 2dp and they will only display (and export) with 2dp but the precision is still stored in the cell.

You can ROUND() the values which will trim the precision using standard rounding rules [ round up if the first significant digit to be trimmed >= 5 or down if 1st s.d. is <=4]

You can TRUNC() which will simply chop off the digits after the precision you specify (i.e. 2.999999999999 will become 2.99, not 3.00)

If you install the analysis toolpak add-in you can also use MROUND() which will apply rounding rules but will round to the nearest multiple (e.g. 0.25) rather than the nearest signigicant digit.

#### Joe4

Another way is to format it to show the number of decimals you want, and then selecting the "Set precision as displayed" option (found under the Advanced Excel option in Excel 2007, under the "When calculating this workbook section).

#### Trevor G

##### Well-known Member
Thank you Joe.

I looked at this method and was able to automate it so I can proceed.

Obiron grateful for your help as well.

Replies
4
Views
93
Replies
1
Views
95
Replies
1
Views
363
Replies
1
Views
151
Replies
1
Views
249

1,195,592
Messages
6,010,621
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back