Accounts Payable Tracking Spreadsheet: Formulas and Layout

Quick Answer: An accounts payable tracking spreadsheet is a table with one row per vendor bill: vendor, invoice number, invoice date, terms, a due date formula, amount, amount paid, paid date and payment method. Add a balance column, a days-past-due column, and a summary that uses SUMIFS to total open balances in 0–30, 31–60, 61–90 and 90+ day buckets. A COUNTIFS flag catches duplicate invoices before you pay one twice.

An accounts payable tracker answers two questions every morning: what do we owe, and what has to go out this week? In Excel that takes one table of bills, a handful of formulas for due dates and aging, and a habit of marking bills paid only after the payment shows up on the bank statement. The layout below works in Excel and Google Sheets.

What columns does an accounts payable tracking spreadsheet need?

Put the bills on a sheet called Bills, format the range as an Excel Table named Bills, and keep one header row with no merged cells. Every formula below uses structured references, so new rows pick them up as you type.

Put the reporting date in its own cell on a Summary sheet and name it AsOf. You could use TODAY() everywhere instead, but a fixed as-of date lets you reprint last month's aging and get the same numbers, which an auditor or your accountant will want.

Two data-entry rules save trouble later. Pick vendor names from a data validation dropdown that points at a vendor list so "Ridge Electric" and "Ridge Electric Co." don't split into two vendors. And store amounts as numbers, never as text with a currency symbol typed in, or SUMIFS will skip them silently.

How do you calculate the due date from payment terms?

For day-count terms the due date is the invoice date plus the terms:

E2: =[@InvoiceDate]+[@TermsDays]

Format the column as a date. Excel stores dates as serial numbers, so adding 30 adds 30 calendar days.

Some vendors bill "Net 30 EOM" or "due end of next month." For terms counted from the end of the invoice month, wrap the invoice date in EOMONTH first:

=EOMONTH([@InvoiceDate],0)+[@TermsDays]

EOMONTH(date,0) returns the last day of the invoice's own month, so an invoice dated 14 October on Net 30 EOM comes due 30 November. If you have a mix of term types, add a TermsType column ("Days" or "EOM") and choose the formula with IF.

Early-payment discounts such as 2/10 Net 30 deserve a column of their own: =[@InvoiceDate]+10 for the discount deadline and =[@Amount]*0.02 for what you save. Sorting by that column shows which bills are worth paying early.

Balance and days past due

J2: =[@Amount]-[@PaidAmount]
K2: =IF([@Balance]=0,"",AsOf-[@DueDate])

DaysPastDue is negative while a bill is not yet due, zero on the due date, and positive once it's late. Paid bills show blank, which keeps them out of every bucket. Partial payments are handled by PaidAmount: a $2,050 bill with $1,000 paid shows a $1,050 balance and keeps aging until the rest is paid.

How do you build an AP aging summary with SUMIFS?

On the Summary sheet, list the buckets in column A and put one SUMIFS per row in column B. SUMIFS takes the range to add first, then pairs of criteria range and criterion, and adds only the rows that meet every pair.

The five bucket cells should add up to Total open. Put a check cell under them, =ROUND(SUM(B2:B6)-B7,2)=0, and if it ever reads FALSE you have a row with a blank due date or a text amount.

For aging by vendor, add vendor names down column D and use the same formulas with one more criteria pair, Bills[Vendor],$D2. That grid is what you look at before calling a supplier who says you're behind.

Worked example

Here is a small book of bills as of Saturday 31 October 2026:

The Summary sheet then reads:

• Not yet due: 829.99 (Northside 640.00 + Lakeview 189.99)
• 0–30 days: 1,280.00 (Ridge Electric 88213)
• 31–60 days: 415.50 (Cobalt Freight)
• 61–90 days: 1,050.00 (Harbor Supply's remaining balance)
• Over 90 days: 300.00 (Delta Print)
• Total open: 3,875.49, and the check cell is TRUE

Ridge Electric's paid invoice 88190 drops out of every bucket because its DaysPastDue is blank. Delta Print at 122 days is the one to look at first: either it's genuinely unpaid, or it was paid and nobody marked it, which is the most common reason an AP aging looks worse than reality.

How do you see which bills are due this week?

In Excel for Microsoft 365, Excel 2021 or 2024, FILTER returns the matching rows as a live list:

=FILTER(Bills,(Bills[Balance]>0)*(Bills[DueDate]>=AsOf)*(Bills[DueDate]<=AsOf+7),"Nothing due")

Multiplying the conditions is how FILTER does AND. With the example above it returns Lakeview Internet, due 4 November. Add a second FILTER for overdue bills, (Bills[Balance]>0)*(Bills[DueDate]<AsOf), and the payment run for the week is the two lists together.

On older Excel versions without FILTER, use a helper column =AND([@Balance]>0,[@DueDate]>=AsOf,[@DueDate]<=AsOf+7) and filter the table on TRUE, or use conditional formatting to shade those rows amber.

Catching duplicate invoices with COUNTIFS

Paying the same invoice twice usually happens when a vendor emails a bill and then mails a paper copy, and both get entered. Flag it at entry time:

L2: =IF(COUNTIFS(Bills[Vendor],[@Vendor],Bills[InvoiceNo],[@InvoiceNo])>1,"DUPLICATE","")

If someone typed INV-4471 for Northside Paper a second time, both rows would show DUPLICATE. Two looser checks are worth adding, because invoice numbers get mistyped:

• Same vendor, same amount, invoice dates within a week. =COUNTIFS(Bills[Vendor],[@Vendor],Bills[Amount],[@Amount],Bills[InvoiceDate],">="&[@InvoiceDate]-7,Bills[InvoiceDate],"<="&[@InvoiceDate]+7)>1
• Invoice number with spaces or dashes removed. A helper column =SUBSTITUTE(SUBSTITUTE(UPPER([@InvoiceNo])," ",""),"-","") makes "INV 4471" and "INV-4471" compare equal.

Treat the loose checks as prompts to look, not as proof. Monthly service bills from the same vendor for the same amount are normal.

Marking bills paid from the bank statement

A bill should move to paid when money leaves the bank, not when someone approves it or mails a check. That keeps the tracker honest: a check that hasn't cleared is still a liability you need to watch, and a payment that bounced gets noticed.

Each week, put the bank's transactions on a sheet called Bank as a table with Date, Description and Amount (money out negative). Most banks let you download a CSV for recent activity. If you only have PDF statements, convert the statement to Excel rather than retyping it; QuickBankConvert turns each transaction into a row with the date, description and signed amount, which is the shape this sheet needs.

Then add a helper column on Bills that looks for a candidate payment:

=IF([@Balance]=0,"",IF(COUNTIFS(Bank[Amount],-[@Balance],Bank[Date],">="&[@InvoiceDate])>0,"Check bank",""))

It flags any open bill whose exact balance appears as a withdrawal on or after the invoice date. Open the bank row, confirm the payee in the description, and only then fill PaidAmount, PaidDate and Method. Check numbers make this cleaner: if the description carries the check number, put it in Method and match on that instead of the amount.

Once a month, compare the tracker's total open balance with the accounts payable balance in your books. The accounts payable reconciliation template walks through that period-end tie-out; this sheet is the day-to-day record that feeds it.

When a spreadsheet stops being enough

A tracker like this handles a few dozen bills a month comfortably. Signs you have outgrown it:

1. More than one person enters bills and you can't tell who changed what.
2. You need approval steps before payment.
3. Bills arrive in volume as PDFs and keying them in is the bottleneck. Getting invoice fields out of PDFs is its own job, covered in converting a PDF invoice to Excel.
4. Your accountant wants AP in the general ledger, not alongside it.

At that point the bills module in your accounting software (QuickBooks, Xero and others all have one) is the better home, and the spreadsheet becomes an export you check against. Until then, a single table, a named as-of date and five SUMIFS give you an aging report you can trust.

Related guides

• Monthly bills spreadsheet for recurring household-style bills on a calendar
• Bookkeeping spreadsheet template for the income and expense side
• Accounts payable reconciliation template for the month-end tie-out
• Microsoft's SUMIFS reference and COUNTIFS reference