Dennis demonstrates how to require unique entries, preventing duplicate entries.
- [Voiceover] Excel's data validation feature…does not have a direct way…of ensuring that entries within a given range are unique.…But you can use a function called countif…to get the job done.…Let's see how countif works…independently of data validation.…I'm interested, for example, in seeing how often…that entry in B2 appears in the list.…I think you can see pretty quickly…it appears there once and only once.…But how do we know?…Equal countif.…This function begins with the idea…that we're looking at a certain range.…Say we're looking in column B.…
Notice that's open ended because this list could grow.…How often does that entry that currently is in cell B2,…how often does this appear in column E?…Enter.…It appears once.…Now, if I were to type it here again,…in cell B14...…there's that same entry.…We now see that it's there twice.…So, we want to use this as a formula within data validation.…And when we use the formula,…it might strike you as a little bit strange at first,…we will first select column B…and we'll use B1 in the actual formula.…
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