A free reconciliation spreadsheet template for Shopify and Stripe payouts (and how to actually use it)

A store doing real volume eventually asks the question one r/ecommerce poster put plainly: "For brands doing decent volume, is this mostly handled in Excel manually? Or are there tools people rely on?" The honest answer is that a spreadsheet handles it fine for a long time — if it is built right. The best community answer to that thread says exactly what to do and stops just short of how: most brands "create one clean master sheet that pulls Shopify payouts and gateway fees weekly and reconcile against bank deposits line by line. The no cost fix is discipline and a clear process owner." Correct. But nobody ever hands over the sheet. So here it is — and the one structural decision that separates a master sheet that ties out from one that quietly lies to you.

Is there a reconciliation spreadsheet template I can just use?

Yes — the one above — and for most stores a sheet is the right tool well past the point people assume they have outgrown it. The reason home-built versions fail is almost never the formulas. It is that they reconcile the wrong two things: total sales against the bank deposit. Those two numbers will never be equal, and no amount of XLOOKUP fixes that, because they are not supposed to be equal. The template is built around the number that sits between them — the payout — and that single change is what makes it tie.

Everything below explains how to use it: why sales and the deposit diverge, the structure that resolves it, the six steps to build it from your own exports, and how to read the only column that needs your attention.

Why your sales never equal the bank deposit

A bookkeeper in r/Bookkeeping described the exact trap the master sheet has to avoid: "I keep running into situations where the bank deposit from Stripe/Shopify/PayPal is lower than the sales showing in QuickBooks, and then I have to dig through fees, refunds, chargebacks, and timing differences to figure out what happened." The most useful reply named the cause without dressing it up: "the mismatch is usually because QBO records the gross sale amount but your bank gets the net deposit after processing fees. if you're recording both and trying to match them 1:1 they'll never line up."

That is the whole problem in two sentences. A single payout is not one sale — it is a batch. The processor takes a window of transactions, subtracts its processing fees, refunds you issued, and any chargebacks or dispute fees, and wires you the net. Refunds you process come out of the next available payout, not the one the sale was in, so the timing never lines up cleanly either. Stripe works the same way: a single deposit bundles many charges with fees already deducted, settling a day or two after the sale. So the deposit is smaller than the sales it represents, it covers a different set of orders than any single day, and it arrives late. Three reasons it cannot match — and all three are normal. We cover the mechanics of the gap in more depth in why Shopify deposits never match your sales; here we just build the sheet that absorbs it.

The structure that makes the master sheet work

Stop trying to match sales to the bank in one jump. Insert the payout statement as a middle ledger and reconcile in two hops: orders roll up to a payout on gross amounts, and the payout ties to the bank on the net. The payout is the pivot both sides agree on — every order belongs to exactly one payout, and every payout is exactly one bank deposit.

The two-hop model
-----------------
  Hop 1   Orders in the payout window  -->  Payout statement   (match on GROSS)
  Hop 2   Payout statement             -->  Bank deposit       (match on NET)

  Expected net  =  Gross sales  -  Refunds  -  Processing fees  -  Adjustments
  Difference    =  Bank deposit  -  Expected net

  Difference == 0   -> matched, ignore it
  Difference != 0   -> exception, the only rows you work

The master sheet is one row per payout. The columns are nothing more than the line items the processor already nets out, plus the two it computes for you:

How to build it from your own exports, in six steps

The procedure assumes a Shopify or Stripe payout export and your bank transactions. It is the same shape for any processor — only the column names change.

1. Export two files: the payout (settlement) report for the period, and your bank transactions. Most payout reports already break out gross, fees, refunds, and adjustments per payout — if so, hop 1 is done for you.
2. Clean the exports before anything else. A leading apostrophe, a region's comma-as-decimal, or a payout ID that Excel autoconverted to a number will silently break every match downstream — these are the CSV gotchas that quietly wreck a reconciliation. Import via Power Query and set column types explicitly rather than double-clicking the CSV.
3. Make one row per payout in the master sheet. Paste the payout report's gross, refunds, fees, and adjustments into their columns, keyed on Payout ID.
4. If your report does not pre-total gross sales, roll the orders up yourself with SUMIFS — sum each order's amount where its payout ID matches the row. One formula, filled down.
5. Add the two computed columns: Expected net = Gross − Refunds − Fees − Adjustments, and Difference = Bank deposit − Expected net. Pull the Bank deposit in from the bank file on Payout ID, or match it by amount and date if the bank line has no ID.
6. Filter Difference to everything that is not zero. That short list is your entire job for the period. Tag each one in Notes, fix the source, and re-pull. A clean run is every row at zero.

The two formulas are the only mechanics worth writing down. Gross roll-up and the difference, in Excel terms:

Hop 1 — roll orders up to the payout (only if the report does not):
  Gross sales for a payout:
  =SUMIFS(Orders[Amount], Orders[PayoutID], [@PayoutID])

The two computed columns:
  Expected net = [@[Gross sales]] - [@Refunds] - [@[Processing fees]] - [@Adjustments]
  Difference   = [@[Bank deposit]] - [@[Expected net]]

Pull the bank deposit in by Payout ID (if the bank line carries it):
  =XLOOKUP([@PayoutID], Bank[Reference], Bank[Amount], "NO BANK LINE")

SUMIFS does the gross roll-up; Power Query does the cleaning and, once you are comfortable, the whole import so next month is a refresh instead of a rebuild. The XLOOKUP that pulls the bank deposit is the same join used in any two-file reconciliation, and if a payout has no matching bank line at all, that is a set-difference you want surfaced, not hidden — which is why the formula returns "NO BANK LINE" instead of an error.

Reading the difference column: what each exception means

A non-zero difference is not a mistake in your sheet — it is the sheet doing its job. It means the deposit and what you expected disagree, and the gap itself usually tells you why. The common cases, and where to look:

The discipline is the one the original thread named — "discipline and a clear process owner" — made concrete: every period, you work the difference column to zero and keep the sheet. Worked the same way each month, it doubles as your month-end tie-out and the evidence an auditor asks for: a payout-by-payout trail from cash back to the orders, with every exception named and resolved rather than plugged.

When the spreadsheet starts to creak

Be honest about the ceiling. The master sheet holds up while you have one or two processors, a clean payout ID on every line, and one person who owns the weekly run. It starts to strain when partial payments and bundled deposits stop lining up to a single key — the point where XLOOKUP quietly gives up — when a third and fourth channel arrive each with their own export format, or when someone other than the owner has to trust the number without re-deriving it by hand. A workbook saves the answer; it does not save the reasoning, and once other people depend on that reasoning it has to live somewhere more repeatable.

None of that is a reason to skip the sheet. It is the right first tool, it teaches you exactly where your money goes, and the manual method underneath it is the same method any tool automates. Build the sheet, run it for a few months, and you will know precisely what a dedicated reconciliation system would have to do to earn its place — because you will have done it by hand first.

← All writing