Find Duplicate Payments Fast with Excel Copilot

For Internal Auditors, few tasks are as tedious yet critical as substantive testing for duplicate payments. Traditional methods involving VLOOKUPs, conditional formatting, or manual "ticking and tying" are prone to human error—especially when analyzing ledgers with thousands of transactions. Excel Copilot transforms this workflow, allowing you to instantly identify potential cash leakage and shell company risks using natural language prompts.

Why This Workflow Matters

Duplicate payments account for 0.1% to 0.5% of total AP spend in many organizations. By automating the detection process, you move from random sampling to testing 100% of the population. This workflow saves hours of data manipulation, providing immediate assurance and freeing you to investigate the root causes of control failures rather than hunting for them.

Prerequisites

• Microsoft 365 License: Specifically Business or Enterprise with the Copilot add-on enabled.
• AP Ledger Data: A raw export containing at least: Vendor Name, Invoice Number, Invoice Amount, and Payment Date.
• Table Format: Your data must be formatted as an Excel Table (Ctrl + T) for Copilot to interact with it.

Step-by-Step Guide

Step 1: Sanitize and Standardize Data

Before hunting for duplicates, ensure your data is clean. Inconsistencies like extra spaces or mixed capitalization can hide duplicates from standard logic. Use Copilot to standardize the text columns instantly.

Step 2: Detect Exact Matches (Low Hanging Fruit)

The most common error is processing the exact same invoice twice. Ask Copilot to flag these immediately. This prompt creates a visual filter for quick review.

Step 3: Identify Fuzzy Duplicates (The "Slightly Different" Invoice)

Fraudsters or tired AP clerks often alter an invoice number slightly (e.g., "INV-100" vs "INV100") to bypass system controls. As an auditor, you need to find payments for the same amount to the same vendor on different dates.

Step 4: Generate an Audit Evidence Summary

Once you have flagged the potential errors, you need a summary for your workpapers. Instead of manually building a pivot table, ask Copilot to summarize the findings for your report.

Pro Tips

• Leverage Python in Excel: If you have the Python enablement, you can ask Copilot to "Use Python to calculate the Levenshtein distance between invoice numbers to find typos." This is advanced fuzzy matching.
• Check for Split Invoices: Modify Step 3 to look for transactions on the same day that sum up to a round number (e.g., $4,900 + $100 = $5,000), which may indicate structuring to bypass approval limits.
• Benford's Law: You can ask Copilot to "Create a chart analyzing the 'Invoice Amount' distribution against Benford's Law" for a high-level fraud risk assessment.

Common Mistakes to Avoid

• Not transforming data into a Table: Copilot is grayed out or functions poorly if the data requires unstructured cell references. Always press Ctrl + T first.
• Ignoring Date Ranges: Duplicate payments might happen months apart. Ensure your dataset covers a wide enough date range (e.g., rolling 12 months) to catch historical duplicates.
• Over-reliance on exact matches: Failing to prompt for "Same Amount, Different Invoice Number" results in missing the most common type of duplicate payment error (data entry mistakes).

Frequently Asked Questions