The invoice tracking spreadsheet that works (until it doesn't)

Create a sheet called "Invoices" with these columns: Invoice #, Client, Date Sent, Amount, Payment Method (Stripe, PayPal, ACH, check), Date Paid, Deposit Amount, Difference, and Status (Open, Paid, Overdue).

The Difference column is key: set it to =Amount - Deposit Amount. For exact-match payments (checks, ACH), this should be zero. For card payments through Stripe or PayPal, it shows the fee amount. If the difference doesn't match the expected fee, something's off.

Add a Days Outstanding column: =IF(Date Paid="", TODAY()-Date Sent, Date Paid-Date Sent). This tells you how long each invoice has been waiting for payment — or how long it took to get paid.

For the Status column, automate it: =IF(Deposit Amount>0, "Paid", IF(TODAY()-Date Sent>30, "Overdue", "Open")). Adjust the 30 to match your payment terms. Use conditional formatting to highlight overdue invoices in red and paid invoices in green — it makes scanning the sheet much faster.

At the top, add summary cells: =SUMIF(Status, "Open", Amount) for total outstanding, =COUNTIF(Status, "Overdue") for overdue count. These give you a snapshot without scrolling.

Create a second sheet called "Bank Deposits." Each month, paste in your bank's CSV export — just the deposits, not the debits. Columns: Date, Description, Amount, Matched Invoice #.

The matching process is manual: sort both sheets by amount, find deposits that match invoice amounts (or invoice amounts minus expected fees), and enter the invoice number in the "Matched Invoice #" column. When you fill in the match, go back to the Invoices sheet and fill in the Date Paid and Deposit Amount.

Color-coding makes the matching process faster. Highlight the Difference column: green if zero (exact match), yellow if between $0 and $50 (likely a processing fee), red if over $50 (needs investigation). Highlight rows where Status is "Overdue" with a red background. Highlight the Matched Invoice # column in the Bank Deposits sheet: leave unmatched rows white so they stand out.

These visual cues turn a wall of numbers into something scannable. You can spot unmatched deposits or suspicious fee amounts at a glance.

This spreadsheet approach has a ceiling, and you'll hit it faster than you expect. The problems start around 50 invoices per month:

Fee calculations become error-prone. When you're manually checking whether $1,165.20 is the right deposit for a $1,200 Stripe invoice, mistakes happen. One wrong match throws off everything downstream. There's no built-in validation — the spreadsheet trusts whatever you type.

Combined payments break the model. When a client pays three invoices in one transaction, your neat one-row-per-invoice structure falls apart. You need to split the deposit across multiple rows, track which invoices it covers, and make sure the fees still add up.

Multiple payment processors multiply the work. If you accept Stripe, PayPal, and ACH, each processor has different fees and different payout timing. Your simple Difference column needs to account for all of them, and the formula gets unwieldy fast.

There's no error detection. If you accidentally mark the wrong invoice as paid, the spreadsheet won't flag it. You might not notice until the client gets a follow-up invoice for something they already paid — which is a great way to damage a business relationship.

The spreadsheet approach is honest work, and it's worth doing until you feel the pain of its limitations. But when matching invoices starts eating hours every week, and you're second-guessing your own formulas, it's time for something purpose-built.

InvoiceTally does what the spreadsheet can't: it automatically matches invoices to bank deposits, accounts for processor fees across Stripe, PayPal, and ACH, catches errors, and handles combined payments. Upload your files and get matched results in seconds instead of hours.