Data validation means to check the data before entry. It gives us the power to condition the values which can be entered and rejected into any cell.
The data validation is a very important aspect of any data filling job. Many a times we have fields which have limited type of entries and which can’t be given to the user for manual entry.
PROBLEMS WITH MANUAL ENTRY
If we have to work on the manual entries after the data has been filled , it can cause so much trouble for us if the data is not in the perfect format. If we keep the field just open for the entry, if there are a number of people filling up that data , there are bright chances of format problems.
Now imagine if we have a 1000 rows and there are slight differences in the format of the entry, no matter it was done by different people, we ‘ll never be able to work on that and we might end up in correcting all the data wasting our time and resources.
SO, FOR SUCH CASES WE ALWAYS PREFER CREATING A LIST FOR A FIRM FORMAT AND CONTROLLING THE ENTRY OF THE DATA.
You must have seen this a lot when we are filling up any form online. We are given either the options of lists to choose from.
So , the same option we have in the EXCEL too. Let us try to create a simple DROP DOWN LIST.
BUTTON LOCATION FOR DATA VALIDATION IN EXCEL
The list creation is done with the help of DATA VALIDATION option in the GOOGLE SHEETS . So we need to use the same button as for data validation as creating list is one of the sub option of data validation.
- Go to DATA MENU
- Choose DATA VALIDATION as shown in the picture below.
STEPS TO CREATE A DROP DOWN LIST IN GOOGLE SHEETS
EXAMPLE SET UP
We’ll create a small form with a name and country.
The country part will be selected from a drop down to understand the way we can set up a drop down list.
The scenario is clear from the picture given below.
SETTING UP THE DATA
SETTING UP THE DATA
The first requirement for creating a list is the data which should be kept in a list.
The data can be in the same sheet, where we are creating a list or we can keep it in another sheet
to avoid any mess in the same sheet. We can hide the sheet too or lock it with a password
if we need security too.
The following picture shows a list of countries which we will be using to create a drop down list.
ADDING THE DATA TO THE LIST
- Place the cursor or select the cell where we want to create the list.
- Go to DATA MENU >DATA VALIDATION .
- The following dialog box will open.
- REFER TO THE DIALOG BOX SHOWN ABOVE.
- CELL RANGE is the cell where we want to create the drop down list. It’ll be shown already filled as we had already selected the cell before opting for the data validation option.
- In CRITERIA choose LIST FROM A RANGE from the drop down and enter the cell range in the second field in the CRITERIA. For our example we have entered L4:L9. It contains our data.
- Choose SHOW WARNING in ON INVALID DATA option. We can also choose REJECT INPUT if we want to reject the input which is outside the list.
- CHECK SHOW VALIDATION HELP TEXT if you want to show a text when the cell is selected. This is a help text to give the input to the user about the value to be chosen in the cell.
- For our example, we have entered SELECT YOUR COUNTRY as the help text.
- Choose SAVE.
ADDING VALIDATION HELP TEXT FOR THE LIST
VALIDATION HELP TEXT [INPUT MESSAGE ]
THE VALIDATION HELP TEXT OR INPUT TEXT IS THE TEXT WHICH WOULD BE SHOWN WHEN WE SELECT THE CELL CONTAINING THE DROPDOWN LIST FOR THE HELP OF THE USER.
We can understand this as a help text for the user.
Suppose we have a drop down list of countries.
We can enter the help text as CHOOSE YOUR COUNTRY.
When the user selects the cell, GOOGLE SHEETS will prompt , CHOOSE YOUR COUNTRY.
STEPS TO ENTER VALIDATION HELP TEXT:
The option is present in the DATA VALIDATION DIALOG BOX as shown in the picture below.
The elaborated process is already discussed. Only the brief process and the step of adding the validation text is being discussed here.
- Select the cell in which you want to create a drop down list in google sheets.
- Go to DATA MENU and choose DATA VALIDATION.
- The picture shown below will appear.
- Select the checkbox against SHOW VALIDATION HELP TEXT. [If it is not checked ].
- As you select the option, a text field will appear.
- Enter the validation text in the field.For our example, we entered the text ” ENTER YOUR COUNTRY”.
- We ‘re done.
- When the user will click on the cell, it’ll prompt the message.
RUNNING THE EXAMPLE
Now our example is ready to be checked.Let us check the example.
We can see the example is having two entries one for the name and
other one is the list.
We can choose the country from the list.
We have created the list as we desired.