How to Build an Accounts Payable Spreadsheet in Excel

Accounts payable (AP) is simply the money your business owes to vendors for goods and services you have already received. When you have a handful of suppliers, you can keep it in your head. Once you have twenty open bills with different terms, you need a system, or you start paying late fees, missing early-payment discounts and getting surprised by a cash crunch.

Dedicated AP software exists, but a well-built Excel sheet covers most small businesses perfectly well. Below we build one in four parts: the bills register, the due date and aging formulas, a status workflow, and a one-glance summary.

Part 1: Set up the bills register

Create a sheet named Bills and format the range as an Excel Table (select it and press Ctrl+T), then name the table Bills under Table Design. Tables keep formulas consistent down each column and let you reference columns by name. Use these columns:

Filling this register is the slow part. Rather than typing each bill, you can run vendor invoices through an invoice to Excel converter using the one-row-per-document layout, which gives you vendor, invoice number, dates and total in exactly this shape. Paste the rows into your table and add terms where the invoice does not state a due date.

Part 2: Due date, balance and days past due

Due date

If the invoice shows a due date, type it in. Otherwise calculate it from the invoice date plus terms. In a table, the formula in the Due Date column is:

=[@[Invoice Date]]+[@[Terms (days)]]

If you prefer regular cell references, the same thing in row 2 is =C2+D2. Format the column as a date. Some vendors use terms like "due on the 10th of next month"; for those, =DATE(YEAR(C2),MONTH(C2)+1,10) does the job.

Balance

=[@Amount]-[@Paid]

Tracking a Paid amount rather than just a Paid checkbox matters, because partial payments are common. A bill with a balance above zero is still open, whatever the status says.

Days past due

The core aging calculation is TODAY() minus the due date. Wrap it so paid bills and not-yet-due bills show zero instead of a confusing number:

=IF([@Balance]<=0, 0, MAX(0, TODAY()-[@[Due Date]]))

TODAY() recalculates every time the workbook opens, so this column is always current. That also means it will not tell you how late a bill was when it was paid; if you want that history, add a separate column with =L2-E2 using the actual paid date.

Part 3: Aging buckets

An AP aging report groups open balances by how overdue they are. The standard buckets are Current, 1 to 30, 31 to 60, 61 to 90 and over 90 days. IFS (available in Excel 2019 and Microsoft 365) makes this readable:

=IF([@Balance]<=0, "Paid",
  IFS([@[Days Past Due]]=0, "Current",
      [@[Days Past Due]]<=30, "1-30",
      [@[Days Past Due]]<=60, "31-60",
      [@[Days Past Due]]<=90, "61-90",
      TRUE, "90+"))

On older Excel versions without IFS, nest IF statements instead: =IF(H2<=0,"Paid",IF(J2=0,"Current",IF(J2<=30,"1-30",IF(J2<=60,"31-60",IF(J2<=90,"61-90","90+"))))).

Bills coming due soon

Overdue is only half the story. To plan cash, you also want to see what is due in the next week. Add a Due Soon column:

=AND([@Balance]>0, [@[Due Date]]>=TODAY(), [@[Due Date]]-TODAY()<=7)

Then apply conditional formatting (Home > Conditional Formatting > New Rule > Use a formula) with =$K2="90+" in red and the Due Soon column TRUE in amber, so problems jump out without filtering.

Part 4: The summary sheet

Add a sheet named Summary. List each vendor down column A (in Microsoft 365, =SORT(UNIQUE(Bills[Vendor])) does it automatically) and the bucket labels across row 1: Current, 1-30, 31-60, 61-90, 90+. Then one SUMIFS formula fills the grid:

B2:  =SUMIFS(Bills[Balance], Bills[Vendor], $A2, Bills[Aging], B$1)

Total owed to vendor:
G2:  =SUMIFS(Bills[Balance], Bills[Vendor], $A2)

Total due in the next 7 days (all vendors):
=SUMIFS(Bills[Balance], Bills[Due Date], ">="&TODAY(), Bills[Due Date], "<="&TODAY()+7)

The mixed references ($A2 and B$1) let you copy the first formula across and down the whole grid. Add a totals row at the bottom and you have a working AP aging report that updates every time you open the file.

A PivotTable is an alternative: insert one from the Bills table with Vendor in Rows, Aging in Columns and Balance in Values. Pivots are faster to build but need a manual Refresh after you add bills, while SUMIFS updates instantly.

A simple status workflow

Use Data > Data Validation > List to restrict the Status column to a fixed set of values. Five statuses cover most small businesses:

1. Entered: the bill is in the sheet but nobody has checked it yet.
2. Approved: someone confirmed the goods arrived and the price is right. For stock purchases, that means comparing the invoice to the purchase order and packing slip.
3. Scheduled: payment is set up in your bank or bill-pay tool for a specific date.
4. Paid: money has left the account. Enter the Paid amount and reference.
5. Disputed: something is wrong (short shipment, wrong price, duplicate). Note why in a comments column and hold payment.

The value of the workflow is in the Approved step. It is where you catch duplicate invoices, price changes and items that never arrived. Our guide to automating invoice data entry covers the basics of matching invoices to purchase orders and receiving records.

Catch duplicate bills automatically

Paying the same invoice twice is one of the most common AP mistakes, often because a vendor emails a bill and then mails a paper copy. Add a Duplicate? column:

=COUNTIFS(Bills[Vendor], [@Vendor], Bills[Invoice '#], [@[Invoice '#]])>1

Note the apostrophe before the # sign: inside structured references, the # character has to be escaped this way. If you would rather avoid that, simply name the column "Invoice No" instead. Any TRUE result is worth a look before payment.

Keeping it healthy over time

• Enter bills when they arrive, not when you pay them. AP only shows what you owe if every received bill is in it.
• Reconcile monthly against vendor statements so missing invoices and unapplied credits surface early. See how to reconcile vendor statements.
• Archive paid bills by year on a separate sheet once the table gets large, and keep the source PDFs in a folder named by vendor.
• Keep one vendor name per vendor. "ABC Supply" and "ABC Supply Co." will split your SUMIFS totals. A short vendor list on a lookup sheet with a dropdown fixes this.
• Prefer Google Sheets? Every formula above works there too, except structured table references; use plain ranges such as C2:C500. You can send extracted invoices straight into a sheet with the invoice to Google Sheets converter.

Once AP lives in a structured sheet, moving to accounting software later is easy, because your data is already in import-friendly columns.

A weekly 15-minute AP routine

The spreadsheet only works if someone looks at it. Pick a fixed time each week, such as Monday morning, and run through the same short checklist:

1. Add every bill received since last week, and confirm the inbox or paper tray is empty.
2. Filter the Duplicate? column for TRUE and resolve anything flagged.
3. Review the Due Soon list and decide what gets scheduled this week, based on your bank balance.
4. Check for early-payment discounts. Terms like 2/10 Net 30 mean 2% off if you pay within 10 days; add a Discount Deadline column (=C2+10) for vendors that offer them.
5. Look at the 61-90 and 90+ columns on the Summary sheet. Anything there should be either paid, disputed with a note, or on an agreed payment plan.