Excel Data Validation

Copper Contributor

Hi. I want to restrict someone putting an amount more than 2 decimals into a cells.

Thanks guys.

5 Replies

@FanieB 

Using data validation:

Select the cells you want to apply the rule to. In the following, I will assume that A1 is the active cell in the selection.

On the Data tab of the ribbon, click Data Validation.

Select Custom from the Allow dropdown.

Enter the formula

 

=MOD(100*A1,1)=0

 

where A1 is the active cell.

Specify messages in the Input Message and Error Alert tabs, if you wish.

Click OK.

 

Alternatively, you could use the Worksheet_Change event procedure to round the input to 2 decimal places, but that would require users to allow macros.

Thanks Hans. I tried that, but I have plenty of cells. I want to change the properties of those Cells, so that you can't put in an amount more than 2 decimals into those Cells. So, if you want to put in say 23.245 if must give you an error.

@FanieB 

What was the problem with the Data Validation rule that I proposed?

 

S0663.png

Hans. These are the cells and the amounts. And like in Cell D7, it must not allowed me to put in an amount like 125.248 with 3 decimals. Than it must show an error.

@Hans Vogelaar 

@FanieB 

See the attached sample workbook. Try entering more than 2 decimals in D7, D8 or D9.