Excel Payment Log: How to Track Payments and Balances

How-to basics · Updated November 18, 2026

To make an Excel payment log, create an invoice register for amounts due and a separate transactions sheet for each payment; use a formula to total cleared transactions against each reference and calculate the remaining balance. This setup handles partial payments without erasing their history, and works in Excel for Microsoft 365 on Windows.

What an Excel payment log is for

An Excel payment log is a structured record of money paid, received, or still outstanding. A basic log can help a household track bills or help a small organization monitor customer invoices and supplier payments. It is not a bank feed: unless connected to a suitable service, the workbook does not verify that money actually moved. Someone must enter or import transactions and reconcile them against statements or receipts.

The phrase can mean two different things. A transaction log records each payment event, while an invoice register records the total due for each bill or invoice and how much has been paid toward it. Combining both concepts in one row becomes awkward when an invoice is paid in installments: replacing the original amount with a new total loses the individual payment dates. A two-sheet design keeps both the running balance and the payment history.

Choose a layout before entering data

Start with two sheets, named Invoices and Transactions. On Invoices, use one row per bill or invoice, with these columns:

  • Reference: a unique invoice number or bill identifier.
  • Due date: the contractual due date recorded on the bill.
  • Counterparty: the customer or supplier.
  • Type: Receivable for money expected in, or Payable for money owed.
  • Amount due: the original amount, entered as a positive number.
  • Paid to date, Balance, and Status: calculated fields.
  • Last updated: an optional date or note for review purposes.

On Transactions, use one row per payment event: Date, Reference, Counterparty, Type, Method, Amount, and Status. Record partial payments as separate rows, each with the same invoice reference. For example, a $500 invoice paid in two installments of $200 and $100 has two transaction rows; the register can then show $300 paid and $200 remaining. Use positive amounts consistently and distinguish money in from money out with the Type field, rather than mixing positive and negative conventions.

Choose references that identify the relevant invoice unambiguously. If a supplier and a customer happen to use the same number, the Type field can help distinguish the records; where necessary, use a combined unique reference such as a prefix plus invoice number. Do not use a person’s name alone as the matching key, because one person or organization may have many invoices.

Build the workbook and enter records

Enter the column headings in row 1 and the records underneath. Select each data range and choose Insert > Table, then confirm that the table has headers. Tables make it easier to filter, sort, and keep formatting consistent as rows are added. Give each table a useful name in the Table Design tab, such as Invoices and Transactions. Excel for Mac also has the Table command; ribbon layout can vary by window size and version.

Format date columns as dates and money columns as currency or number with two decimal places, as appropriate. Set a consistent currency for the workbook. A number formatted as currency is still a number, so it can be added; text such as “$200 paid” is not a reliable substitute for a numeric amount. Add payment methods such as bank transfer, card, or cheque only if those categories are useful to the people maintaining the file.

Use a clear status vocabulary. For example, Transactions can use Cleared, Pending, or Void. Include Cleared entries in balance calculations; a pending transfer is not necessarily a completed payment, and a void item should not reduce an amount due. Keep a note or supporting reference for unusual adjustments. Excel’s Data Validation tools can provide a drop-down list, helping prevent inconsistent entries such as “cleared,” “Clear,” and “Cleared.”

Calculate amounts paid and balances

Assume the Invoices sheet has Reference in column A, Due date in B, Type in D, Amount due in E, Paid to date in F, Balance in G, and Status in H. Assume Transactions has Reference in column B, Type in D, Amount in F, and Status in G. Enter the formula below in Invoices F2 and fill it down. It totals only transactions with the same reference and type whose status is Cleared.

With those Invoices and Transactions columns, and an invoice in row 2 whose reference is INV-104 and type is Receivable, put this in F2: =SUMIFS(Transactions!$F$2:$F$1000,Transactions!$B$2:$B$1000,A2,Transactions!$D$2:$D$1000,D2,Transactions!$G$2:$G$1000,"Cleared"). If the transaction rows for INV-104 include cleared payments of $200 and $100, the result is $300. A pending $50 transaction is excluded.

With the same Invoices layout, put this in G2 to calculate the balance, never allowing it to fall below zero: =MAX(0,E2-F2). If E2 is $500 and F2 is $300, the result is $200. A negative balance would require investigation, such as a duplicate payment or an overpayment; MAX prevents the displayed balance from going negative but does not resolve the underlying issue.

With Due date in B, Paid to date in F, Balance in G, and Status in H, put this in H2: =IF(G2=0,"Paid",IF(B2<TODAY(),"Overdue",IF(F2>0,"Part-paid","Open"))). If the balance is $200, the invoice has a $300 payment, and its due date is in the future, the result is Part-paid. If its due date is earlier than today, the result is Overdue until the balance reaches zero. TODAY updates with the current date, so the status can change when the workbook recalculates. This is a tracking label, not a decision about late fees or contractual terms.

Track payments and reconcile regularly

When a payment is made or received, add a new transaction row rather than overwriting an earlier installment. Enter its date, reference, type, method, amount, and status. Update a pending payment to Cleared only when a reliable record confirms settlement under the process being used. If a payment is reversed, retain an audit trail in notes or record the reversal clearly; do not quietly delete a previously recorded event if others rely on the file’s history.

Reconcile the log on a regular schedule that suits the volume of activity. Compare transactions with bank or payment-provider statements, receipts, and the underlying invoices. Check that amounts, dates, and references agree, and investigate unmatched items. A log can be internally consistent yet still be wrong if a payment was omitted, entered twice, assigned to the wrong invoice, or marked cleared too early.

For a simple summary of cleared receipts, assume Transactions has Type in D, Amount in F, and Status in G, with a summary formula placed in another sheet. Use: =SUMIFS(Transactions!$F$2:$F$1000,Transactions!$D$2:$D$1000,"Receivable",Transactions!$G$2:$G$1000,"Cleared"). If cleared receivable transactions total $300 and other rows are payable, pending, or void, the formula returns $300. Change the criterion to Payable to total cleared outgoing payments. Expand the ranges as the log grows, or use Excel Table references to make formulas adapt as table rows are added.

Make the log easier to read and check

Filters help answer practical questions: which invoices are overdue, which payments are pending, or how much is owed to a particular supplier? Click a table’s filter arrow to select a value or date range. Sort by due date to bring the nearest deadlines into view, but avoid sorting only one column, which can separate values from their related records. Sorting the entire table preserves each row as a record.

Conditional formatting can make exceptions visible. In Excel for Windows, select the relevant cells, then choose Home > Conditional Formatting and select a suitable rule. For example, highlight “Overdue” status text or pending transactions. Color should supplement, not replace, the written status: color alone may be inaccessible to some readers and can be lost in printing or copying.

Review formulas after inserting or moving columns, and protect formula cells if multiple people edit the workbook. Sheet protection can prevent accidental changes, but it is not a substitute for secure file storage or a strong access policy. Keep a dated backup and restrict access to people who need the information.

Common errors and limits

  • Using inconsistent references: INV-104 and INV104 will not match in a formula. Adopt one format and check pasted data.
  • Counting pending items as paid: filter calculations to cleared transactions when the goal is confirmed payment totals.
  • Entering amounts as text: imported symbols, spaces, or text labels can stop Excel from summing values correctly. Check whether totals include every intended row.
  • Overwriting installment history: keep each payment on its own transaction row and let the register calculate the cumulative amount.
  • Assuming a spreadsheet is an accounting system: a workbook does not automatically provide bank reconciliation, approval workflows, reliable audit controls, or jurisdiction-specific accounting treatment.

Excel is well suited to a modest log with a clear owner and a repeatable review process. As records, users, currencies, approvals, or reporting requirements grow, a spreadsheet may become harder to control. Protect sensitive details, avoid storing full card numbers or banking credentials, and follow the organization’s retention and privacy requirements. For formal bookkeeping, financial reporting, tax, or legal questions, check the relevant records and consult a qualified professional; this log is an organizational tool, not professional advice.

When to use a different tool

A spreadsheet is a reasonable choice when the log is small, needs flexible columns, and can be checked manually. An accounting or invoicing application may be more suitable when several people need permission-based access, automatic invoice matching, payment reminders, audit history, recurring bills, or direct bank feeds. Bank and payment-provider exports can also help with reconciliation, but imported data should be checked for duplicates, date formats, and transaction descriptions before it is treated as complete.

There is no single best option for every business or household. Consider the number of transactions, who will update the records, whether an approval trail is needed, and how sensitive the data is. A tool that saves entry time but cannot produce the reports or controls a user needs may not solve the underlying problem. Whichever tool is chosen, retain source documents and verify totals against independent records.

Frequently Asked Questions

How do I make a simple payment log in Excel?
Create a table with date, reference, payer or payee, direction, method, amount, and status. If you need to track invoices and partial payments, keep a separate invoice register and link transaction rows to each invoice by reference.

Can Excel calculate a remaining payment balance?
Yes. Store the amount due and calculate paid-to-date from matching cleared transactions, then subtract paid-to-date from the amount due. Check for duplicate entries and overpayments rather than relying on a formula alone.

How should I record a partial payment?
Add a new transaction row for each installment, using the same invoice reference each time. The invoice register can sum cleared installments while preserving each payment’s date and amount.

Can Excel tell me which payments are overdue?
Yes, if the workbook includes due dates and a balance. A status formula can compare the due date with TODAY and label an unpaid balance overdue. Confirm the actual terms on the invoice; the formula does not establish whether a payment is contractually late.

Is an Excel payment log safe for bank details?
Do not store passwords, full payment-card numbers, or online banking credentials in a workbook. Limit file access, use appropriate organizational security controls, and keep only information needed for tracking and reconciliation.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.