From the course: Excel 2016: Tips and Tricks
Unlock the full course today
Join today to access over 22,400 courses taught by industry experts or purchase this course individually.
Ensure unique entries with data validation rules
From the course: Excel 2016: Tips and Tricks
Ensure unique entries with data validation rules
- [Voiceover] In this worksheet called Data Validation, I'll be putting in some names, I already have some ID numbers, and I want to be sure to keep out duplicate ID numbers. But as you can see from this list already there are some duplicates. Now, one approach is simply to highlight the duplicates. That's not going to help us keep out duplicates. As a reminder, if you click Column B I'm on my Home tab, go to Conditional Formatting, Highlight Cell Rules, Duplicate Values, and we probably would have seen that anyway with our eyes since the list is so short but we see the duplicate values highlighted. We don't really need this feature here, so I'll simply press escape. We might actually want to prevent duplicate entries from going into Column B. Let's get rid of the data. Now, there is a function called COUNTIF. You might be familiar with it. COUNTIF says, I'm looking at a certain location, let's imagine it's going to be Column B, comma, and I want to know how often a certain value…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
-
-
-
-
-
-
-
Sort data with single buttons4m 34s
-
Clean up spaces quickly with the TRIM function4m 37s
-
Identify and eliminate duplicates6m 21s
-
Split column data using Text to Columns and Flash Fill5m 26s
-
Join data with the TEXTJOIN function and Flash Fill4m 46s
-
Ensure unique entries with data validation rules5m 19s
-
Force typed dates to be weekdays only with data validation rules4m 31s
-
Display unique items from large lists5m 17s
-
-
-
-