When you apply data validation rules, no special actions are taken to the cells within the definition range that violate the rules. To highlight those cells (by encircling them in red), select the option appearing when you click the drop arrow on the Data Validation button and select Circle Invalid Data.
- [Narrator] We're looking at the worksheet called…Data Validation Violations.…When you a data validation to a set of cells,…existing data is not affected whatsoever.…Now sometimes you discover this…in kind of a backhanded way.…In Column G we've got some entries for…compensation salary amounts here…and I'm about to change Larry Weber's salary,…he's gotten a big increase here,…he's now making $96,000.…So I type in 96,000, I press enter…and that's not acceptable.…I don't know what the rule is because the error alert…was not customized, so I'll close this…and simply go to Data Validation on the Data tab…and see that the rules says that…these must be less than 95,000.…
That's sort of strikes me as strange because…right down below here I can see that Leslie Phillips here…has a salary of $102,000, so what's going on here?…Some of the data in Column G was there…before the rule was established.…This might be happening in other locations too.…So let's check out throughout the entire worksheet…if there are any validation rules that have been violated.…
Author
Dennis Taylor
Released
9/19/2016- Controlling numeric input
- Using the input message box
- Setting up pick lists for quick, error-free input
- Creating multitiered pick lists
- Setting date and time limitations
- Limiting text length
- Requiring entries to be unique
- Locating cells with data validation rules
Skill Level Advanced
Duration
Views
-
Introduction
-
Welcome37s
-
-
1. Controlling the Limits of Numeric Data
-
Using the Error Alert tab2m 40s
-
2. Setting Up Drop-Down Lists
-
Creating drop-down lists5m 15s
-
List location and order9m 30s
-
Multitiered lists8m 11s
-
-
3. Date Controls
-
4. Time Controls
-
5. Text Length Controls
-
6. Specialized Custom Formula Controls
-
Data validation shortcomings5m 20s
-
Conclusion
-
Goodbye27s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Identifying cells that violate data validation rules