A “copy-and-paste” formula to restrict decimal places with Data Validation

By Neil Shorney, Director, Naturally Sales Ltd.

Sometimes you need a formula in Excel for a specific task, which you don’t really need to understand, you just need to use it. Someone asked me recently for just such a formula.

This person wanted to use Data Validation to restrict the number of decimal places which a user can enter into a cell.

We can do this as follows:

1. On the “Data” tab, click “Data Validation”
2. In the “Allow” drop-down, choose “Custom”
3. Enter the following formula in the “Formula” box: =OR(IF(ISERROR(FIND(".",$A1)),LEN($A1)>0,LEN(MID($A1,FIND(".",$A1)+1,25))<=X))
4. Change the “X” before the final closing brackets to the number of decimals you want to allow
5. Change any reference to "A1" to the cell you're working with
6. Press “OK”

You can then copy this down a column to a whole range if necessary.

It doesn’t matter how this works (I can explain if anyone desperately needs to know) but it does the job and can be used in many Data Validation situations.