One Excel cell cost JPMorgan $6.5 billion 🤦‍♂️

Not a typo. In 2012, an employee copied data between files, and when pasting, the formula took the sum instead of the average. The error went unnoticed, the model underestimated risk for weeks, and the bank lost $6.5 billion in a cascade.

One cell. No input validation. Six and a half billion 😡

Excel has a guard for this: Data Validation. It sits at the cell entrance, not letting garbage in. Tab Data → Data Validation (or Alt+D+L).

Custom formulas, the most powerful 😎

Here you write the rules yourself. Anti-duplicates: =COUNTIF($A$2:$A$100,A2)<=1. Only workdays: =WEEKDAY(C2,2)<6. Mandatory fill: =LEN(A2)>0. If I had known at the start how the bosses love to twist reports, I would have studied Data Validation first.

And here's the rake: Ctrl+V bypasses validation. Copy-paste can shove anything past the rules. Cure: add Sheet Protection. Two locks are more reliable than one.

Basic restrictions ✅

1️⃣ Dropdown lists (List). Column "Task Status", one writes "Done", another "done", a third "Petrov did it". You set List, specify options separated by commas or a named range. Creativity is over.

2️⃣ Numbers and dates (Whole number, Decimal, Date). In the "Discount" column, someone enters 500%. Or puts the year 2077 in a date. Set a range: discount 0–50, date from 01/01/2020 to today. Junk won't get through.

More rakes people step on 🤔

1️⃣ Error Alert in Warning mode. The user sees a warning, clicks "OK" and calmly writes whatever. Set mode to Stop. Then no options.

2️⃣ Input Message. When setting up a rule, there's an Input Message tab: you write a hint like "Enter only status from the list, don't add manually". Most people don't even open this tab. But in vain, it immediately reduces "creativity".

3️⃣ Circle Invalid Data. Data → Circle Invalid Data. Circles in red cells that violate rules. Great for auditing old files that were filled before you.

Open any working spreadsheet. Set at least one rule on a key column. In a week, click "Circle Invalid Data" and see how much garbage has accumulated.
Spoiler: usually a lot 🤷‍♀️

#excel

@data_dzen 🙂