Data Validation
What is it? And why do we use it?
Spreadsheets are already organized in a grid of columns and rows. A spreadsheet is a digital representation of a ledger sheet. Because spreadsheets are organized in such a way, they are very popularly used to organize all manner of data.
What is Data Validation?
Simply put, data validation allows the creator/author of the spreadsheet to establish “rules” for the entry of data into columns/rows/cells to limit data entry errors and enforce the “type” of data permitted in a particular area of the spreadsheet. Phew! Sounds complicated, doesn’t it? It doesn’t necessarily need to be.
Why do we use Data Validation?
Excel data validation is a useful tool when sharing workbooks with multiple users; especially if the users are varied in their Excel experience. Essentially, data validation permits you to enforce data integrity and limit data entry errors therefore making your spreadsheets foolproof! There are eight (8) validation options for users to set for any cell:
- Any Value: If any value is allowed, This is the default option for all cells on a spreadsheet.
- Whole Number: A whole number is simply any positive number that does not include a fractional or decimal.
- Decimal: Like whole numbers, except it converts numbers to decimal values.
- List/Drop-Down: Adds a drop-down list in the selected cells using a list that you added yourself in the settings area of the List dialog box. You can manually type the list in using a comma (,) as a separator between all options in the list. Or, you can have the list somewhere on the spreadsheet and use cell references for the list options.
- Dates: You can provide a specific date range to be used.
- Time: You can provide a specified time range to be used.
- Text length: Sets a specified character length – including spaces to restrict the amount of data that can be entered into the specified cells or range.
- Custom: Permits you to use a custom formula (format) to validate data For example, you can format the cells to permit only uppercase characters, or even show an alert if a user inputs a date beyond 30 days from the current date.
Data Validation is offered as part of our Excel Level 3 class. If you would like to learn more - contact sales@westernts.ca or call or text to get more information on this exciting data tool that can make your life so much easier!
