Data Validation helps us to make our Excel sheet error free. With data validation, we can define which data type cell can accept. We can also provide a list of values, in which we can select. Entering wrong value will prompt with an error. In this tutorial, we’ll learn how to do data validation in Excel 2016.
Data Validation:-
We have a sheet with a table of data.
Need help with Excel Tutorials?
Get a free strategy session with our experts — no commitment required.
In the table above, These are just five entries. Suppose we have 1000 of entries like this in one table. And we are using that data for some calculations. A little mistake may give us wrong results. Suppose we have room numbers. We need it error free. We’ll add data validation on room numbers column.
For that, You need a pre-occupied list of data. Verify that list is perfect and error free, all the values are right. Select the range of the cell on which you want to apply validation.
And in the menu ribbon, Go to “DATA†and select “DATA VALIDATIONâ€. You can use shortcut key for that. Select the range of cells, and Press “Alt+A+V+Vâ€. These shortcuts are hard to remember but practice makes everything perfect.
You will see a dialogue box as below in the picture.
You’ll see Validation criteria dropdown list. *These are all those options you can apply for validation.†Select “List†among those options. And select the source data. Source data is the list of pre-occupied data that is verified as error free. Select the source range on the sheet.
In the upper portion of dialogue box, You will see two more tabs.
Input Message
You can enter information about the cell in this tab. When cell is selected, information pops out for user ease.
Error Alert
It facilitates with restriction about the wrong value. It will prompt if user will enter any wrong value and restricting to update the data in the cell.
After finishing, You will see that the cells in column of Room number is showing a dropdown arrow. Clicking on that one, you’ll see verified list values. You have successfully applied data validation on Room number Column.
