Hello all and welcome back to the Excel Tip of the Week. This week, we have a Creator post in which we are revisiting the topic of how to make custom data validation rules with functions.
As creators we should be designing our spreadsheets so they can be understood and used by others. #6 of the Twenty Principles for Good Spreadsheet Practice reminds us that data validation can be used to help avoid input errors, especially those that have a knock-on effect elsewhere. If you need a refresher on the basics of data validation, they were covered in TOTW #296.
Writing a function for data validation
The data validation menu has many options, but the one we're interested in here is 'custom':
This allows us to write a formula that will act as a custom data validation rule. The function must be a logical formula that uses operators such as > < and = and returns a TRUE/FALSE value; an IF function is not suitable.
The formula should also include cell references. These should be written as if the formula were entered in whatever cell was active when data validation was opened: so, if for example you have cell B5 active, a reference to B5 in the formula will be treated as 'this cell' when the data validation is expanded to other cells. You can use dollar signs as normal to mix between absolute and relative references.
Most commonly, the formula will inspect the cell that has the data validation rule for some quality. Here are some example formulas, using A1 as the reference cell:
| Rule | Formula | Potential application |
|---|---|---|
| First character is an 'a' (not case sensitive): | =LEFT(A1,1)="A" | Entering product codes, especially alpha-numeric ones that start with a particular character |
| First character is an uppercase 'A': | =EXACT(LEFT(A1,1),"A") | As above but case sensitive |
| Cell contains no spaces: | =LEN(A1)=LEN(SUBSTITUTE(A1," ","")) | If lookups used to retrieve data do not have spaces, inputs shouldn’t have them either! |
| Number is a multiple of 0.25: | =MOD(A1,0.25)=0 | Accounting for timesheet entries where hours should be recorded to the nearest 15 minutes according to a policy |
| Value is any number | =ISNUMBER(A1) | The standard validation options for “Whole Number” or “Decimal” require values or ranges to be specified, so this is a great alternative if you just need it to be a number of any value. |
| Value is greater than the value in the cell to the right: | =A1>B1 | Validating an ‘expenses paid’ cell that cannot be greater than the sum of individual expenses. |
| Value is greater than the value in B1 (always B1 for all cells with this rule): | =A1>$B$1 | Setting targets which may vary for different departments/areas but all need to be above a certain level. |
Remember however that standard validation options around date/time ranges, that simple rules like "decimal greater than a threshold" or "date before X" are pre-set rules and don't need a function!
More Data Validation Tips
You can create almost any rule you wish with this method. This includes using AND or OR to combine rules, providing the overall outcome is TRUE or FALSE – for example if you need to compare values against two different cells. Given custom rules may leave users confused as to why their input has been rejected, adding an input message or a custom error message is also advisable.
Something worth noting is that when using custom rules to compare cell values (e.g. =A1>$B$1), this will not inherently validate the data type. So you can end up in a situation where this isn’t allowed:
But this is:
Therefore, if you need to make a numeric comparison, you should also use a function like ISNUMBER.
One last trick to bear in mind - the formula interface in the data validation box doesn't show the tooltip or colour in cell references or any of the other helpful bits the regular formula bar does. It's also very hard to test or inspect formulas written into the data validation box. If you're having trouble getting your custom validation to work, try writing it in a regular cell first and testing it there, before copying the formula text into the data validation menu later on.
Remember: use operators > < =, IF is not suitable, and the formula should return TRUE or FALSE.
- Excel Tips and Tricks #496 – ‘Check Performance’ in Excel
- Excel Tips and Tricks #495 - Excel “Tick”ery!
- Excel Tips & Tricks #494 - How to add a custom ribbon to your workbook part 3
- Excel Tips & Tricks #493 - How to add a custom ribbon to your workbook part 2
- Excel Tips & Tricks #492 - How to add a custom ribbon to your workbook
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
