Lesson 18: Data validation in excel!

How to do Data Validation and Create Drop Down List in Excel?

This video is a part of free Excel Training Singapore, where I show you how to use Data Validation in Excel from the basics and also cover a lot of advanced Features.

Data validation in excel simply allows users to make sure that any data that is to be entered in excel is initially validated, checked and then inputted in the cell. It happens when the user decides upon a criterion that needs to be met before entering the data in the cell. Data validation helps to input the data in the most accurate form and as per the requirement of the worksheet as data validation doesn’t allows storage of irrelevant data.

Features of data validation in excel –

• Input a personalized message – input a personalized message feature enables users to input a message explaining the criteria applied on a cell when any cell refuses to accept the input being inputted.
• Customize error messages – the error messages displayed by default in excel aren’t very elaborate in nature but excel gives it’s users the opportunity to customize that error message as well. The messages can be displayed in the form of a warning, an information and stop.
• Circle invalid data – In excel, circle invalid data cells allows users to identify the cell in advance that are not in accordance with the criteria mentioned. This feature circles the invade data with red to demarcate it.

How to validate data in excel?

Data validation helps users to restrict random and useless entry of data in a cell or a range of cells. This feature enables to put a criteria on the entry of data in a cell. To learn how to restrict data entry in a cell, follow the following mentioned steps –

• Start by clicking on data option mentioned in the tab on the upmost part of the screen. Click on data validation option from the data tools group. Three options are displayed which are data validation, circle invalid data and clear validation data
• Click on data validation, a pop-up menu box appears. Under the data validation criteria, fill in the required inputs. Input the criteria which the user wishes to restrict for data entry and hit OK.
• The criteria so described will be applied to the selected cells. If the user tries to input data that does not fulfill the requirement of asked criteria, an error message is displayed

How to input message?

When data validation is applied in excel, it becomes very ambiguous for any user to understand in any particular situation the reason why any cell simply refuses to accept data. To resolve this ambiguity, excel provides the benefit to users to input a message for it’s users to understand the kind of criteria applied . To learn how to do this, follow the following mentioned steps –

• Click on data tab that appears on the topmost region of the work space. Click on data validation option that appears in the tab just below it
• Three options will appear, click on data validation. A pop up menu will appear, called as the data validation box. From the box, click on input message option
• Another pop up box appears on the screen. Enter the heading of the message in the title box, followed by content in input message box which ideally should be the explanation of criteria applied to the cells
• Make sure to select the option that says show the input message when the cell is selected. This option will display the inputted message when any data that doesn’t fit in the criteria of the cell is being inputted
• Hit OK to save the message.

How to customize error messages?

After having applied data validation in excel, whenever any data is inputted in any cell that does not matches the criteria of that cell, an alert is displayed. This alert is not very descriptive in nature. To learn how to input a more descriptive and elaborate error message, follow the following mentioned steps –

• Click on data tab that appears on the topmost part of the screen. Click on data validation option from the tab below
• Three options will appear, click on data validation option. A pop up box will appear, click on error alert open
• By default, the option saying display error alert when invalid data is entered. Excel provides three styles to display the error alert which are stop, warning and information
• Specify the title in the title box, displayed on the right side of the pop up box and enter the text in the text box just below it
• After filling all the details, hit OK. Next time when the value inputted doesn’t matches the criteria, the detailed error message will be displayed.

Learn Microsoft Excel course in Singapore!

Microsoft excel has become of the most important and powerful tool in almost all fields of work and development today. Make sure you aren’t the one lagging behind by taking up Master Excel Course in Singapore today!

In this Tutorial, we will cover all you need to know about Drop Down List and Data Validation.

Data validation allows you to make sure any data that is entered in a cell meets specified criteria (i.e, it’s first validated and then allowed).

For example, if you only want to make a cell accept date values, you can set that in data validation. Or if you only want to accept numbers up to 10 digits, you can specify that in Data Validation.

Drop Down Lists are a part of data validation, as it gives the user an option to select from a pre-populated list. This can be really useful when you have a workbook where you want people to do data entry or when you’re creating an Excel dashboard.

The video covers all the basic data validation topics and then also covers some advanced data validation topic – such as dependent drop-down lists, input messages, dynamic drop-down lists. etc.

• It covers the following topics:
• Data Validation Criteria
• Input Message
• Customizing Error Messages
• Circle Invalid Entries
• Advanced Data Validation Tricks (such as Create Dependent Validation, Make Sub Headings in Validation, Disguise Numbers as Text, Creating Dynamic Drop Down List)