Dennis demonstrates how to use error-checking settings.
- [Instructor] Excel has some built in error checking rules…but you might not be aware of them,…and some of them might be turned on or off.…There are quite a few of them.…Let's see how we might encounter them,…and then we'll show you how you can make changes to them.…On this worksheet called Functions,…in cell N3, I'm about to write a formula…that multiplies M3 times J3.…So, equal, and I'll just click on the cells…M3, asterisk, J3, Enter.…And suddenly I see a little green triangle there.…
Let me zoom in on this, and you might have seen…green triangles before.…Whenever you see a green triangle,…you'll see an exclamation point to its left,…and there's a drop arrow.…This says Unprotected Formula.…Maybe I'm not sure what that means.…Below we see Error Checking Options.…Let's go here.…Now we could've gotten to this dialog box differently,…but in the bottom section here we see…Error checking rules, and as you look over…some of these, some of them are not truly errors.…
And the one we just encountered was…Unlocked cells containing formulas.…
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