Contextures News 20200107

Check for Cheaters in Excel

January 7, 2020

Are people cheating in your Excel files, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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.

Check for Cheaters

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":

  • copy a cell somewhere else, and paste it into the cell with the rule
  • on the Home tab, click Clear, then click Clear All, to get rid of the rule

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.

  • =SUMPRODUCT((tblIDs[ID]<>"") / COUNTIF(tblIDs[ID],tblIDs[ID]&"") - (COUNTIF(tblIDs[ID],tblIDs[ID]&"")=1))

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

count duplicate items in column

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:

  • =IF(DupIDs>0,"Before continuing, remove duplicate IDs.","")

show message for duplicated IDs

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.

On Contextures

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.

calorie counter

Excel Articles

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?

Also see: My Excel Products || Previous Issues

An Excel-lent Wine

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!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: January 4, 2020 3:44 PM