Have you ever dealt with large data sources and there’s a rogue input in row 88 of your spreadsheet? It’s REALLY annoying, isn’t it? Why can’t people just follow instructions and insert either A or B!?
Well, there’s a way to stop these types of issues. Enter Data Validation!
What Is Data Validation In Excel?
Data validation in Excel is utilized to ensure that users can only enter certain values into a cell. It is commonly used to create drop-down lists where users can only select certain values.
Another useful purpose of data validation is to restrict user inputs and create rules within a cell. This is particularly useful for limiting inputs for number ranges or date inputs into a cell.
Why Use Data Validation In Excel?
This feature in Excel helps you to control the data that is input into a worksheet and ultimately reduces the risk of errors.
Take the example below. As you can see, cell D5 is looking quite odd with a “HELLO” in the cell. Clearly, when the user was inputting data, they didn’t follow the header guideline of “Yes” or “No”. You can prevent these types of issues with data validation.
How To Apply Data Validation In Excel
The data validation button can be found under the Data ribbon as shown below. It can also be accessed through the keyboard shortcut: Alt + D + L.
You will then be presented with the following:
By following the prompts within the data validation menu, various rules and restrictions can be applied to a cell. These are explained below:
- Whole number / Decimal – the cell will only permit whole or decimal numbers within the specified range or conditions.
- List – the cell will only permit items in the restricted list
- Date / Time – the cell only permits dates or times within the specified range/conditions
- Text length – the cell will only permit text inputs of a specified number of characters (including spaces)
- Custom – This option allows you to type in your own formula as a data validation set. For example, TRUE or FALSE formulae can be used to limit cell inputs – e.g. you can type in the formula =IF(A1 = B1, TRUE, FALSE) to ensure that cells A1 and B1 are equivalent. The formula used can also prevent duplicates, spaces in cells, maximum/minimum, etc
Related article – How To Manipulate Text Data In Excel
Data Validation Worked Example
Following on from our example above, we can apply a “List” to ensure that users can only enter Yes or No instead of a “HELLO”. To do this, you select “List” under the Allow. As you can see, to separate each item in the list, you use the “,” (comma).
Now if you click on the cell, you will notice a dropdown list icon and only the correct data types will be in the list.
Hope this helps and improved your productivity that extra bit today!
You may also like to read How To Remove Blank Spaces In Excel or 6 Helpful Excel Productivity Tips.