How to reconcile Square sales with bank deposits
By the Analistable team · Updated · 5 min read
Square groups a day's (or period's) card payments into one deposit, net of processing fees and refunds. Export Square's transfer or deposit details listing each payment with its deposit, sum the net amounts per deposit, and match each deposit to your bank statement by amount and date. Card sales recorded in your till but missing from every deposit are the items to investigate.
Part of our guide: How to reconcile data in Excel
Build the deposit totals
- Export the payments included in each Square deposit for the month (Square's banking or transfers reports list them).
- Pivot: rows = deposit ID or deposit date, values = sum of gross, fees and net.
- Next to each deposit, find the bank line:
=XLOOKUP([@Net], Bank[Amount], Bank[Date], "Not found"), and check the date is within a few days. - For any deposit not found, look for two deposits that landed as one, or an amount changed by a refund or chargeback.
Worked example
| Deposit date | Card sales (gross) | Fees | Refunds | Net deposit | Bank |
|---|---|---|---|---|---|
| 2026-09-02 | 1240 | -21.08 | 0 | 1218.92 | Found 2026-09-02 |
| 2026-09-03 | 980.5 | -16.67 | -25 | 938.83 | Found 2026-09-03 |
| 2026-09-04 | 1560 | -26.52 | 0 | 1533.48 | Not found |
The third deposit wasn't on the statement. It covered a Friday's sales and arrived the following Monday as part of a weekend transfer — a timing difference, not a missing payment.
Till vs Square
Also compare the till's card total per day with Square's gross card sales. A difference usually means a card payment taken on another device, a manual entry, or a refund processed on a different day. Cash sales don't appear in Square deposits at all; reconcile them with cash deposits separately.
| Date | Till card total | Square gross | Difference |
|---|---|---|---|
| 2026-09-01 | 1240 | 1240 | 0 |
| 2026-09-02 | 1005.5 | 980.5 | 25 |
The £25 difference on 2 September matches the refund processed that day, which the till recorded against the original sale date.
Checklist
- Fees booked as an expense, not deducted from sales.
- Refunds and chargebacks matched to their deposit.
- Weekend and holiday deposits checked for grouping.
- Cash reconciled separately.
Payouts from delivery apps follow a similar pattern, with commission instead of card fees — see reconciling delivery-app payouts with till reports.
Chargebacks
A disputed card payment is deducted from a later deposit. List chargebacks separately, match each to its original sale, and keep them on the reconciling-items list until the dispute is resolved.
Why the date window matters
A lookup on amount alone can match the wrong deposit when two days happen to net to the same figure. Add a date condition so a deposit only matches a bank line within a few days after it was sent:
Found =COUNTIFS(Bank[Amount], [@Net], Bank[Date], ">="&[@[Deposit date]], Bank[Date], "<="&[@[Deposit date]]+4)
Bank date =IFERROR(INDEX(Bank[Date], MATCH(1, (Bank[Amount]=[@Net])*(Bank[Date]>=[@[Deposit date]]), 0)), "Not found")The second formula is an array formula: Excel 365 handles it natively, while Excel 2019 and earlier need Ctrl+Shift+Enter. In Google Sheets, wrap it in ARRAYFORMULA or use FILTER instead. A result of 2 or more in the Found column means two candidate bank lines — pick the earliest unused one and mark it so it can't be matched again.
Second example: a combined weekend deposit
When deposits are grouped, add the expected deposits together before looking for them. Here Friday, Saturday and Sunday arrive as one bank line on Monday.
| Sales day | Gross | Fees | Refunds | Net |
|---|---|---|---|---|
| Fri 5 Sep | 1240 | -21.7 | 0 | 1218.3 |
| Sat 6 Sep | 1685 | -29.49 | -40 | 1615.51 |
| Sun 7 Sep | 910 | -15.93 | 0 | 894.07 |
| Total | 3835 | -67.12 | -40 | 3727.88 |
The bank shows one credit of 3,727.88 on Monday 8 September. The three nets add up to it exactly (1,218.30 + 1,615.51 + 894.07), so all three days are reconciled. If the sum had been close but not exact, the next suspect is a chargeback or a refund processed on the Monday itself.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Deposit higher than the day's net | Two days grouped into one transfer | Sum consecutive days until the total matches |
| Deposit lower by a round amount | Chargeback or refund deducted | List disputes and refunds by deposit |
| Gross sales differ from the till | Sales taken on a second device or location | Filter Square's report by device or location |
| Deposit missing entirely | Paused transfers or a changed bank account | Check the transfer status in Square |
| Fees look too high | Manually keyed or online payments at a different rate | Group fees by payment method |
Several locations
If you run more than one site, deposits may be per location or combined depending on your settings. Add a Location column to the payments export and pivot by location and deposit, so a short deposit can be traced to the site whose till disagrees. To compare a whole month across sites, stack each site's till export first — the approach in combining data from multiple sources works well here.
Month-end cut-off
Sales on the last day or two of the month are usually deposited in the next month. List them as card receipts in transit: gross sales for the month minus fees and refunds, minus deposits received, should equal that in-transit figure. Next month, those deposits should be the first lines you match. Keep the list on the same sheet as your bank reconciliation so the in-transit amount is explained in one place.
Doing it in Google Sheets
Import the payments export and the bank CSV onto two tabs with File → Import → Insert new sheet(s). Build the deposit totals with a pivot table (Insert → Pivot table, rows = deposit date, values = SUM of net), or with one formula:
=QUERY(Payments!A:F, "select B, sum(F) where B is not null group by B label sum(F) 'Net'", 1)Here column B holds the deposit date and F the net amount; adjust the letters to your export. Then use COUNTIFS with the date window from the formula above to look for each total on the bank tab. QUERY treats a column with mixed text and numbers as text, so make sure the net column contains only numbers.
Common mistakes
- Comparing the till's total sales (cash and card) with Square deposits, which only contain electronic payments.
- Booking the deposit as the sales figure, so fees disappear from the accounts.
- Matching a refund to the deposit for the day of the original sale instead of the day it was processed.
- Marking a deposit as missing on the first of the month when it is simply still in transit.
Checking the result
For the month, gross card sales minus fees and refunds minus chargebacks should equal deposits received plus deposits in transit at the start and end of the month (opening in transit added, closing in transit subtracted). If that holds, every deposit is explained, even when a few landed on different days.
Frequently asked questions
- Why doesn't my Square deposit match my daily sales?
- Deposits are net of processing fees and refunds, and weekend sales may be grouped into one deposit.
- How do I see which payments are in a Square deposit?
- Square's banking or transfer reports list the payments that make up each deposit; export them as CSV.
- Do cash sales appear in Square deposits?
- No. Only card and other electronic payments are deposited by Square. Reconcile cash separately.
- How long do Square deposits take?
- Timing depends on your account settings and bank; standard transfers usually arrive within a couple of business days. Check your Square banking settings for your schedule.