Dennis demonstrates how to force entries to be picked from a list which saves time, improves accuracy, and increases standardization.
- [Instructor] One of the most appealing features…of data validation is a feature popularly known as…a pick list, or drop down list.…Neither of those terms are actually found…as you use the feature.…But let's imagine that in Column A…we don't want anybody typing anything.…We want a drop down list here, a pick list…comprised of the entries, that for the moment,…are in Column E.…These entries.…And in Column B, the same general idea but…a much larger list, all of the states.…They are listed in order here by way of population…and we'll talk more about that idea too.…
And over in Column G, that would correspond…with the data that we will want in Column C.…We've got an alphabetized list of all…of the departments.…So, nobody will type anything in these examples here.…In Column A, we want to make sure that the only…entries that can be entered,…and they will not be typed,…they will be, as we see them over in Column E.…So, let's click on Column A,…go to Data Validation.…And the choice, as we see it,…on the Settings panel here under Allow,…
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