Mark 100+ Invoices Paid From a Spreadsheet in QuickBooks Online: The Bulk-Apply Options

One deposit lands in the bank. The email that explains it carries a spreadsheet with a hundred and forty rows, one per invoice, and every one of those invoices is sitting open in QuickBooks Online waiting to be marked paid. There's no bulk or batch button that takes the spreadsheet and marks them all paid for you.

That's the situation this article is about: a customer who pays one lump sum for many invoices and sends the breakdown as a file, and a bookkeeper on QuickBooks Online who is applying it by hand. There are three ways to stop, and they suit different volumes. Here is what QuickBooks can do on its own, what a spreadsheet importer adds, and what remittance-matching software does that neither of the others can.

Bulk mark as paid in QuickBooks Online: what it does natively, and what it doesn't

QuickBooks Online will apply one payment across many invoices, as long as they belong to one customer. Receive Payment lists every open invoice for the customer you pick, you tick the ones the payment covers, and it marks them paid in a single save. The bank feed does the same thing from the other direction: select the downloaded deposit, choose Find match, and tick the open invoices that make it up.

What it won't do is read a file. There's no native way to upload a spreadsheet of invoice numbers and amounts and have the payments applied from it, and no bulk "mark as paid" action on the invoice list. QuickBooks Online Advanced has batch tools, but they cover creating and editing invoices, bills, checks and expenses, the outgoing side. Receiving payments isn't one of them.

So the ticking is the whole job, and at a hundred and forty lines it's a long one. The three options below get it done by something other than a person.

Option 1: tick them by hand in Receive Payment

For a payment covering two or three invoices this is a minute of work. Open Receive Payment, pick the customer, enter the amount, tick the invoices, set the deposit account, save. The full walkthrough, including checks and part-paid lines, is in how to apply a customer payment to multiple invoices in QuickBooks Online.

At volume the same screen works against you in two ways. The first is time: each line means finding the invoice in the list, checking the amount against the spreadsheet, and ticking it, and a large payer's list can run past the screen several times over. The second is the default. Enter the total without ticking specific invoices and QuickBooks applies it oldest first, which is a guess, and a guess that drifts further from the payer's own record with every cycle.

The usual escape is to record the payment now and allocate it later, which is how unapplied cash is born. The receivables aging then shows invoices as open that the customer has already paid, and the clean-up is the same matching, done colder. That failure mode is covered in how to apply an unapplied payment in QuickBooks Online.

Option 2: import the spreadsheet with a payments importer

Third-party importers such as SaasAnt Transactions and Rightworks Transaction Pro load transactions into QuickBooks Online from Excel or CSV, payments included. You map your columns to their fields, typically customer, invoice number, amount, payment date, reference and deposit account, and the import creates a Receive Payment against each invoice named in the file.

This works well when the payer's file is already the shape the importer wants: one row per invoice, an invoice number that exactly matches yours, an amount that equals the open balance. Where it costs time is everything before the upload. The payer's spreadsheet has their column headings, their invoice reference format and often a few rows that are not invoices at all, so someone reshapes it into the template every cycle. A row whose invoice number doesn't match is rejected. A row whose amount differs goes through as a part payment nobody has looked at, unless the importer's amount check is on, in which case it is rejected too.

An importer also only understands spreadsheets. The same payer who sends a file this month may send a PDF next month, and a smaller customer sends the breakdown in the body of an email. Those get keyed by hand or retyped into the template first.

Option 3: software that reads the file as the payer sent it

Remittance-matching software takes the document in whatever form it arrives: a spreadsheet, a CSV, a PDF, a scanned check stub or the text of an ACH email. It reads the invoice numbers and amounts off it and matches each line to the open invoices in your QuickBooks Online company. The payment is then posted fully applied, with every invoice the file names ticked and the file attached in QuickBooks.

RemitClear is remittance-matching software for QuickBooks Online: forward the payer's spreadsheet, CSV, PDF or ACH email as it arrived, and it matches every row to your open invoices and posts one payment per customer, fully applied. There's no template to reshape, because the reading is done from the payer's own layout. Matching handles exact, partial and prefix-based invoice numbers, so a payer who writes your number their own way still lands on the right invoice, and credit memos the payer has netted off are applied in the same posting. A document where every line matches and the total agrees can be set to post without anyone touching it. Anything short-paid, over-paid or unrecognised waits on a review screen with the mismatch called out, so the person's time goes on the exceptions rather than the hundred lines that were fine.

The wider category, enterprise cash application, is worth knowing about to rule it out. Suites such as HighRadius and Billtrust do this at scale for ERP-based finance teams and are priced and implemented accordingly. Collections tools such as Chaser connect to QuickBooks Online but sit on the chasing side of receivables, not the applying side. For a QuickBooks Online company the practical choice is between an importer and a matching tool.

One customer or many

The question usually arrives in one of two shapes, and they behave differently in QuickBooks Online.

• One customer, many invoices. A distributor or retailer pays on a cycle and sends one remittance listing everything it covers. Receive Payment handles this natively for one customer, and it is the shape both the importer and the matching route were built for.
• Many customers, one deposit each. A property manager, a franchise group or a parent company paying for several accounts at once. A QuickBooks Online payment belongs to exactly one customer, so this always becomes one payment per customer, however it is done. By hand that is one Receive Payment per customer; an importer needs a customer column on every row; matching software groups the lines by customer and posts one payment for each.

In both shapes the bank sees one deposit. The payments should go to Undeposited Funds, shown in newer layouts as Payments to deposit, and be grouped onto one Bank Deposit that the bank feed can match to the statement line. Recording them straight to the bank account instead leaves the feed with a deposit it cannot match, covered in how to clear undeposited funds in QuickBooks Online.

What breaks the spreadsheet, whichever route you take

The exceptions decide whether a bulk route saves time, and they show up in every payer's file eventually.

• Short payments. A row for less than the invoice balance, with or without a reason. Someone decides whether it is a partial payment, a deduction to write off, or a query back to the payer, per invoice.
• Credits netted off. A negative row for a credit memo the payer has deducted. The credit has to be applied to the invoices the file names, not left to QuickBooks to apply to the oldest balance on its own.
• The payer's own numbering. Your invoice number with their prefix, a purchase order in the invoice column, or a reference field that is really two numbers joined.
• Repeated and duplicated rows. The same invoice twice on one file, or the same file sent twice a week apart.
• A total that doesn't equal the deposit. Bank fees, a currency conversion, or a payment the file simply does not mention.

By hand, each of these is a pause and a judgement. Through an importer, each is a rejected row or a silent misapplication, because the importer trusts the file. Through a matching tool, each is a flagged line on the review screen. The file still has the same problems, but the person is only asked about those rows.

A worked example

1. By hand. Receive Payment for the customer, 140 ticks against a list that scrolls several screens, two credits applied in the same payment, one Payment column edited for the short pay. Around two hours every month, and the short pay is easy to miss when the running total is the only check.
2. Importer. Twenty minutes reshaping the chain's file into the template, then an upload. The 140 clean rows and the short-paid row apply, with nothing to say the short pay was short. The two credit rows have no place in the template and are done by hand, and the reshaping repeats every cycle.
3. Matching software. The email is forwarded as it arrived. The 140 clean lines match, the two credits are applied in the posting, and the short-paid invoice is the one line waiting for a decision. A few minutes on the review screen.

Which one fits

• A handful of invoices per payment, a few payers: Receive Payment, ticked against the remittance every time.
• Large payments from one or two payers whose files are consistent and clean: an importer, with a saved mapping and someone owning the reshape.
• Large payments, several payers, mixed formats, or files that carry credits and short pays: remittance-matching software, so the review is of exceptions only.

Whichever route, the test is the same. The customer's file and your ledger should agree line for line after the payment posts, without a person having read every line to get there. That is what marking a hundred invoices paid from a spreadsheet means, and the ticking was only ever the slow way of getting it.