To turn a remittance advice PDF to Excel, make one row per invoice the payment covers, with the invoice number, the invoiced amount, the amount paid and any deduction. Copy the payment reference and date onto every row. Then check that the paid column adds up to the deposit that actually reached your bank account.
What does a remittance advice PDF list?
A remittance advice PDF is the note a customer sends with a payment to say what it pays for. It lists a payment header, with the payer, payment date, method, reference and total, and then lines for each invoice or credit the payment covers. The lines are what you need in Excel.
Remittance advice comes from the payer's system, not yours, so its layout depends on the customer. A large company's accounts payable software prints one style. A small customer may type a list into an email. The fields, though, are close to the same everywhere.
Header fields
Read these once per remittance:
- Payer name, the customer as their system names them, which may differ from your customer record.
- Payment date, the date they sent the money.
- Payment method, such as ACH, wire or check.
- Payment reference, a check number or a transfer reference.
- Payment total, the amount sent.
Line fields
Each line usually holds:
- Invoice number, as your invoice printed it, or sometimes their own purchase order number.
- Invoice date.
- Invoice amount, what you billed.
- Amount paid on that invoice.
- Discount or deduction, with a reason code or short note.
When the payer quotes their purchase order number instead of your invoice number, you need a second lookup later. Note on the sheet which number each customer tends to quote.
One row per invoice from the remittance advice
A remittance advice sheet works best with one row per invoice line, and the payment header copied onto every row. That way each row stands on its own: you can sort, filter or paste it into a larger table and still know which payment it came from. Never leave the header only in the first row.
Column layout
| Column | Example content |
|---|---|
| Payer | Example Retail Group |
| Payment date | 2026-09-18 |
| Payment ref | ACH 7731042 |
| Invoice no. | INV-2207 |
| Invoice amount | 2,250.00 |
| Paid | 2,205.00 |
| Adjustment | -45.00 |
| Reason | Early payment discount |
Store invoice numbers as text. A number such as 002207 loses its leading zeros if Excel treats it as a number, and then it no longer matches your invoice list. Format the column as text before you paste, or prefix the entries with an apostrophe.
Getting the lines out of the PDF
If the remittance is a text PDF from the payer's system, the table usually copies or converts cleanly, because it is a simple grid. Check whether your PDF reader can export it directly before reaching for anything else; the guide to saving a PDF as Excel from the program that opened it covers which readers offer that. For a single short remittance, typing five lines is often the fastest route.
Short payments, discounts and deductions on a remittance advice
A short payment on a remittance advice is an invoice paid for less than its full amount. The difference is usually an early-payment discount, a deduction the customer took for a dispute, or a credit memo applied against the invoice. Record the difference in its own adjustment column, with the reason, instead of changing the invoice amount.
Keeping the invoice amount as billed matters. If you overwrite it with the paid amount, the sheet loses the fact that the customer took 140.00 off, and nobody follows it up. The adjustment column turns every short payment into a visible line that can be approved, disputed or written off.
Three kinds of difference
- Discount: the customer paid early and took the terms you offered, for example 2% off within 10 days. Check that the payment date falls inside the discount window.
- Deduction: the customer held back money for a reason such as damaged goods, a pricing dispute or a missing delivery. These need someone to look at them.
- Credit memo: a credit you issued earlier, now used up against this payment. It appears as a negative line.
Unexplained differences
Sometimes the paid amount is lower and no reason is given. Mark the reason as Unexplained rather than leaving it blank. A filter on that word gives the list of questions for the customer, and an empty cell gives nothing.
Does the remittance advice total match the bank deposit?
The remittance advice total should match the deposit on your bank statement for the same payment. Add the paid column, compare it with the payment total printed on the remittance, and then compare both with the bank line. All three must agree. When they do not, the remittance is incomplete, or the bank deducted a fee.
A worked example
The remittance below is an example with invented customers and amounts.
| Invoice no. | Invoice amount | Paid | Adjustment | Reason |
|---|---|---|---|---|
| INV-2201 | 1,500.00 | 1,500.00 | 0.00 | |
| INV-2207 | 2,250.00 | 2,205.00 | -45.00 | Early payment discount, 2% |
| INV-2214 | 1,320.00 | 1,180.00 | -140.00 | Deduction: damaged goods |
| CM-0098 | -72.50 | -72.50 | 0.00 | Credit memo applied |
| Total | 4,997.50 | 4,812.50 | -185.00 |
The invoices and the credit memo come to 4,997.50. The two adjustments take off 185.00. The paid column sums to 4,812.50, which in this example is also the payment total printed on the remittance and the ACH deposit on the bank statement. The check =SUM(Invoice)+SUM(Adjustment)-SUM(Paid) returns zero, so every line is accounted for.
When the deposit is lower
A wire can arrive a little short because a bank along the way took a fee. The remittance says 4,812.50, but the deposit says 4,787.50. That 25.00 is not on the remittance at all. Record it as a bank fee against the payment, not as a deduction against an invoice.
Getting the deposit lines
To compare remittances with deposits in bulk, you need the deposits as rows too. FastBankConvert is being built to read a digital bank statement into date, description and amount columns, and to tell you before download if the balances do not tie. It is not open yet; the get-started page takes sign-ups for when it is.
Matching remittance lines to open invoices with XLOOKUP
Matching remittance lines to open invoices means looking up each invoice number from the remittance in your list of unpaid invoices. XLOOKUP does this in one formula and returns the open balance. If the paid amount plus the adjustment equals the open balance, that invoice is closed; anything else stays open with a new balance.
The formula
With open invoices on a sheet called Open, invoice numbers in column A and open balances in column C:
=XLOOKUP(D2,Open!A:A,Open!C:C,"Not found")
Put that in column I next to each remittance line, using the column layout above. Then a second column, =I2-F2+G2 where I is the lookup result, F is paid and G is the adjustment, shows what is left. The adjustment is stored as a negative number, so adding it takes the discount or deduction off the open balance. Zero means closed, and a non-zero result is the new open balance.
Excel versions without XLOOKUP can use INDEX and MATCH in the same way. The important part is the "Not found" result. A line that cannot be found usually means the payer quoted their own purchase order number, typed the invoice number with a different prefix, or paid an invoice you already marked as paid.
Duplicates and overpayments
Look for invoice numbers that appear on two remittances. Sometimes a customer pays the same invoice twice, and the second payment becomes a credit on their account. The lookup returns zero open balance on the second line, which is your signal.
Customers sometimes send a statement of their own showing what they think they owe. The guide to a vendor statement PDF turned into Excel covers that document, which is useful for spotting invoices that the customer never received.
Remittance advice layouts that resist a clean table
Some remittance advice does not arrive as a tidy PDF table. It can be the body of an email, a scanned paper stub from a check, a file that covers several payments at once, or a long list that wraps descriptions over two lines. Each needs a small extra step before it becomes rows.
Email bodies
Remittance text pasted into an email usually loses its columns. Copy it into a single column in Excel and use Text to Columns with a space or tab delimiter, or type the lines if there are only a few. Check that amounts with commas did not split into two cells.
Check stubs
A remittance on the stub of a paper check is a scan, so the text needs OCR first. Stubs are small and printed by the payer's system, which helps, but confirm every amount against the check total before you trust it.
Several payments in one file
Large payers sometimes send one file listing several payments, each with its own reference and a block of invoice lines. Copy the header for each block onto that block's rows, so the Payment ref column separates them. Then tie each payment total to its own deposit, not the file total to one deposit.
When the document is not a remittance
Expense claims from staff look similar, with a list of items and a total paid out, but the money goes the other way. Those belong in a separate sheet, and the guide to turning an expense report PDF into Excel rows covers that layout. Keep customer payments and staff reimbursements apart so neither total is distorted.