Excel Skills for Fraud Analysis
What this domain tests
This domain tests practical spreadsheet competence for fraud work: exact-match lookups, multi-criteria aggregation, duplicate detection, and repeatable cleaning. Items test whether you know why a formula returns the wrong answer.
Core rules
- Use exact-match lookups: XLOOKUP defaults to exact; VLOOKUP needs FALSE as the fourth argument.
- INDEX with MATCH looks left as well as right and survives column insertion.
- SUMIFS and COUNTIFS handle multiple criteria and are safer than SUMIF with concatenated keys.
- Detect duplicates with COUNTIFS over a composite key (policy number, loss date, rounded amount) greater than 1.
- Clean text with TRIM, CLEAN, PROPER, and SUBSTITUTE before matching names.
- Pivot tables summarize quickly, but distinct counts require the data model.
- Power Query makes cleaning repeatable and documented; manual edits do not.
- Protect source sheets; do calculations on copies.
Worked example
Task: find duplicate claim payments. Reasoning: names and claim numbers vary in case and spacing, so build a key from policy number, loss date, and amount rounded to the dollar; then COUNTIFS the key against itself and flag anything above 1. Rounding is deliberate, because cents differences often come from partial payments that are legitimate, so the flagged set needs review rather than automatic recovery. Use XLOOKUP to pull the claim and payee for each flagged row, then confirm whether two payments represent one loss or two.
Traps that cost marks
- Leaving VLOOKUP on approximate match and getting plausible but wrong values.
- Using Remove Duplicates on the source and destroying the audit trail.
- Copying filtered ranges that include hidden rows.
- Assuming date format is consistent across imported files.