Check for Cheaters in Excel
January 7, 2020
Happy New Year! I hope you had a relaxing holiday season, and you're ready for another year of learning new Excel tricks, and remembering a few old ones!
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
In the last newsletter, you saw how to block duplicate entries, with a custom data validation rule. Unfortunately, data validation isn't foolproof - at best, it's "fool resistant". Here are 2 ways that people can put invalid data into a cell, with a bit of "cheating":
Check for Duplicates
If your co-workers might cheat, and enter duplicates, set up a hidden formula in your workbook, to alert you when that happens. In the worksheet shown below, each ID number should be unique, but 2 is entered twice, and 3 is entered twice
This formula, in cell A4 (named DupIDs), shows that there are 2 duplicated numbers.
To show how the 3 parts of the formula work, I've added formulas in columns E:I, with a total in cell I13.
TIP: Move the formula from cell A4 to a different sheet, where people won't see it
In other parts of your workbook, you can refer to cell A4 (DupIds), to create warning messages, or show a zero, instead of the expected results. For example, show a message with a formula like this:
See more details about this formula, and other counting formulas on my Contextures site. This example is in the first download file on that page, on the "DupItems" sheet.
Here are recent items from my Contextures site and blog.
New Year: If you're setting goals for 2020, get these three free Excel templates to help you "Excel" at your New Year resolutions. There's a weight loss tracker, a calorie counter, and a weekly meal planner. The formulas and techniques could be used in other types of Excel workbooks too.
Here are a few Excel-related articles that you might find useful or interesting.
Excel Skills: If you're looking for a new job this year, here are a few tips on how to include your Excel skills in your résumé. (Level-All)
Excel Features: Our favourite spreadsheet program is constantly changing. Chris Newman looks forward to trying these 5 new Excel features this year. (Level-All)
Excel Humour: See what people said about Excel recently. Is it painful to watch your boss work Excel?
We enjoyed this Formula wine with our New Year's Day dinner -- an excel-lent choice, right? We also had prime rib - the best one we've ever cooked. We used the Reverse Sear method, and it was medium rare inside, and crispy outside. Now we're back to healthier eating, for the rest of the year!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200107ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: January 4, 2020 3:44 PM