Dennis demonstrates how to restrict data by value limits, text length, date, or time constraints.
- [Instructor] You can reduce data entry and cut down…on lots of data entry errors, by using a feature called…Data Validation.…It's a powerful tool, and on this worksheet here,…in column A, we're about to put in ID numbers.…But they must be exactly six characters long.…That's easily achieved.…Many times when you apply data validation rules,…you use them for an entire column.…Let's click column A, here.…Data Validation found on the Data tab.…Allow, not just any value here, but in this case a…whole number, but we're more concerned with here,…really though, is text length.…
Now a text could be all numbers, it could be all letters.…All we care about is it's six characters.…Text length.…We could have a range of them, but in this case…how about, equal to six.…Click OK.…So we'll try and enter here.…Not bad.…Bad.…And here's what happens.…Cancel, eliminates the entry completely.…Retry, puts us in edit mode.…Maybe there's one extra character on the end.…Get rid of that, we're all set.…
We can also provide one or two message types,…
Dennis begins with the basics—how to display data so that errors can be easily spotted. Next, he offers handy tips to ensure data is entered correctly the first time, using the AutoFill feature and using AutoCorrect shortcut codes for frequently used entries. Dennis provides easy ways to validate your data, which is particularly helpful when multiple team members are contributing to the same spreadsheet. This includes restricting the data that can appear in a spreadsheet by setting value limits, pre-populating data with drop-down lists, and other methods. He also shows how to avoid mistakes in formulas, how to hide data that doesn't need to be seen, and how to use workbook protection to prevent errors, and more.
- Streamlining data entry steps
- Using Excel shortcuts and automation features
- Protecting worksheets and workbooks
- Validating data
- Basing entries on formulas
- Detecting errors in formulas
- Using Range Names
- Finding mistakes in large, complex spreadsheets