اكسل

Troubleshooting formulas

Excel ignores data validation formulas that return errors. If a formula isn't working, and you can't figure out why, set up dummy formulas to make sure the formula is performing as you expect. Dummy formulas are simply data validation formulas entered directly on the worksheet so that you can see what they return easily. The screen below shows an example:

Once you get the dummy formula working like you want, simply copy and paste it into the data validation formula area.

If this dummy formula idea is confusing to you, watch this video, which shows how to use dummy formulas to perfect conditional formatting formulas. The concept is exactly the same.

Data validation formula examples

The possibilities for data validation custom formulas are virtually unlimited. Here are a few examples to give you some inspiration:

To allow only 5 character values that begin with "z" you could use:

=AND(LEFT(A1)="z",LEN(A1)=5)

This formula returns TRUE only when a code is 5 digits long and starts with "z". The two circled values return FALSE with this formula. 

To allow only a date within 30 days of today:

=AND(A1>TODAY(),A1<=(TODAY()+30))

To allow only unique values:

 =COUNTIF(range,A1)<2

To allow only an email address

=ISUMBER(FIND("@",A1)