Excel Invoice Reconciliation Practice for Finance Freshers
It’s your first week as a finance intern at a Chennai packaging company. A customer has transferred ₹18,000, but the invoice tracker shows ₹26,500. Your manager asks, “How much does the customer still owe?”
You have five minutes to check the numbers before the team sends a payment reminder.
Your Excel challenge
Here’s the invoice tracker for this fictional case. Every row belongs to the same customer.
| Invoice number | Invoice amount |
|---|---|
| INV-101 | ₹7,500 |
| INV-102 | ₹6,500 |
| INV-102 | ₹6,500 |
| INV-103 | ₹6,000 |
The customer’s payment email says the ₹18,000 covers INV-101 and INV-102 in full, plus ₹4,000 towards INV-103.
Copy the table into Excel and answer three questions. Which row needs checking? Which invoice is partly paid? What amount should go in the payment reminder?
Check your answer
The customer still owes ₹2,000.
INV-102 appears twice. In this case, checking the original invoice confirms it was entered twice, so it counts only once. The three valid invoices total ₹20,000. After applying the ₹18,000 payment, only INV-103 has a balance. Its invoice amount was ₹6,000 and the customer paid ₹4,000.
If you had subtracted the bank credit from the tracker total straight away, you’d have asked the customer for ₹8,500.
How to spot it in Excel
If invoice numbers are in column A, enter =IF(COUNTIF($A$2:A2,A2)>1,"Check duplicate","") in C2 and drag it down. Excel flags the second occurrence of INV-102 for review. Then add columns for Amount Paid and Balance Due so you can see which invoices are still open.
Try this in your next finance interview. Explain how you went from a tracker showing ₹26,500 to a payment reminder for ₹2,000. Mention the duplicate, the customer’s payment email, and the invoice that still has a balance.
Want to practise more Excel tasks like this? Explore AJ Academy’s Finance Analytics course in Chennai.