Microsoft Excel Training Videos

Custom Validation in Excel

Our video today shows how to use a custom validation in Excel which relates to budgeting. We wish that the sum of 'advertising', 'phone' and 'rent' expenses should not exceed 5000 and as soon as that happens the data entry operator or user should be warned and take necessary action.
In the earlier videos we had demonstrated how to validate names and designations of employees using lists. We had also shown how to validate entries involving salaries of employees to reduce data entry errors.

How to implement custom validation in Excel:

• Select the Excel cells where you wish to enter the relevant data
• Click on 'Data' in the menu bar
• Select Validation...
• A Data Validation dialog box opens
• Under 'Allow' where you will see the default value as 'Any value', click on the drop-down arrow and select 'custom'
• On selecting 'custom' a formula text box appears
• Inside the formula text box you type your criteria as shown in the Excel training video
• We have shown examples of a 'sum' Excel function and the 'trim' Excel function

The sum function prevents the budget Excel range from exceeding a certain total and the trim text function doesn't allow the user to add names with leading or trailing spaces.

More about validation and custom validation in Microsoft Excel Further reading on custom validation in Excel