Excel for Accounts Payable: Invoices, Dates and Totals

Excel for accounts payable is about three things: knowing what you owe, knowing when it is due, and never paying the same invoice twice. You don't need advanced features for that. You need a tidy invoice log, date formulas for due dates and days overdue, SUMIF for totals and COUNTIFS for duplicates. This guide is for AP clerks, finance assistants and job seekers facing an accounts payable Excel test, and every example uses one small invoice table.

What Excel skills does accounts payable need?#

Accounts payable (AP) is the team that records and pays supplier invoices. Most of the daily Excel work fits in this table.

The same date and total skills appear in other operations roles. Compare Excel skills for bookkeepers and Excel skills for supply chain jobs if you are applying more widely.

How to set up an invoice log#

Use one row per invoice and one column per fact. Never merge cells or leave blank rows inside the data. Here is the log used in this guide, in A1:E7:

Dates are shown day/month/year. A real log would also hold the invoice date, PO number and payment date. Keep the Status values consistent: "Unpaid" and "unpaid " (with a space) are different to a formula. A drop-down list from Data > Data Validation stops typos.

How to calculate invoice due dates#

A due date is the invoice date plus the payment terms. Excel stores dates as numbers of days, so plain addition works. With the invoice date in a cell such as C2 and net 30 terms:

=C2+30

An invoice dated 1 March 2024 is due on 31 March 2024. Format the cell as a date if you see a number like 45382. The lesson on how Excel stores dates explains why.

Some suppliers use "end of month" terms, such as 30 days after the end of the invoice month. EOMONTH returns the last day of a month, so =EOMONTH(C2,0)+30 handles that. For an invoice dated 10 March 2024, that's 31 March plus 30 days: 30 April 2024.

How to calculate days overdue#

Days overdue is the report date minus the due date. For a live report, use TODAY(), which returns the current date and updates every day:

=TODAY()-C2

A positive result means the invoice is overdue by that many days; a negative result means it isn't due yet. For a fixed report, put the report date in a cell instead, say H1 = 15 April 2024, and write =$H$1-C2. Then INV-202 (due 12 March) is 34 days overdue and INV-206 (due 10 April) is 5.

To show 0 instead of negative numbers and blank for paid invoices, combine IF and MAX:

=IF(E2="Paid","",MAX(0,$H$1-C2))

The TODAY function guide covers formatting and the recalculation behaviour. For more date gaps, practise days between dates with DAYS and DATEDIF.

How to total unpaid invoices with SUMIF#

SUMIF adds the amounts where one condition is true. To total everything still unpaid:

=SUMIF(E2:E7,"Unpaid",D2:D7)

The result is 5,255 (840 + 2,100 + 1,325 + 990). For a second condition, such as unpaid invoices for one vendor, use SUMIFS:

=SUMIFS(D2:D7,B2:B7,"Acme",E2:E7,"Unpaid")

That gives 2,100, because Acme's other invoice is paid. Note that SUMIFS puts the sum range first, while SUMIF puts it last. The SUMIF function guide has more examples.

How to build an aging summary#

An aging report groups unpaid invoices by how late they are. Add a helper column with the days overdue (as above), then a bucket with IFS:

=IFS(F2<=0,"Current",F2<=30,"1-30",F2<=60,"31-60",TRUE,"Over 60")

With the 15 April report date, INV-202 (34 days) lands in 31-60, INV-203 (26 days) in 1-30. Then total each bucket with SUMIFS, or build a pivot table with the bucket in Rows and Amount in Values. IFS needs Excel 2019 or later; use nested IF in older versions.

How to match invoices to purchase orders#

Before an invoice is approved, AP often checks it against the purchase order (PO). If the PO list sits on another sheet called POs, with PO numbers in column A and approved amounts in column C, XLOOKUP pulls the approved amount next to each invoice:

=XLOOKUP(H2,POs!A:A,POs!C:C,"No PO")

Here H2 holds the invoice's PO number. A second column such as =D2-I2 then shows any difference between the invoice amount and the PO amount, so you can query it before paying. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions use =VLOOKUP(H2,POs!A:C,3,FALSE).

How to catch duplicate invoices before you pay#

Paying an invoice twice is one of the costliest AP mistakes. Flag any vendor and invoice number pair that appears more than once:

=COUNTIFS($B$2:$B$7,B2,$A$2:$A$7,A2)>1

TRUE means the pair is a duplicate. Check both columns, because two vendors can use the same invoice number. You can also highlight duplicates with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, though that checks one column at a time.

Many finance roles test these exact tasks. Try the finance Excel mock test to see how they are asked under time pressure. Sales and retail roles use similar totals, as shown in Excel skills for sales operations.

Practise it#

AP work is mostly SUMIF and dates, done carefully. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then work through how Excel stores dates and days between dates to make due dates and overdue days second nature. Try the unpaid-total exercise on this page first.