Home>Blogs>Excel Tips and Tricks>Accounts Payable Aging Report in Excel: Step by Step with Aging Buckets and SUMIFS
Excel Tips and Tricks

Accounts Payable Aging Report in Excel: Step by Step with Aging Buckets and SUMIFS

Short answer: an accounts payable aging report in Excel needs five things: a due date from the vendor terms, a balance, days past due, an aging bucket and a few SUMIFS summaries. This tutorial builds all of them step by step, so you can see on one page how much you owe, how much is overdue and which bills to pay first.

The example is Cascade Outdoor Gear Co., a fictional outdoor clothing and gear brand in Portland, Oregon, with 10 vendors on Net 15, 30, 45 and 60 terms and 64 bills. Total accounts payable is $76,329.58, of which $35,695.80 (46.8%) is overdue and $6,956.00 is due in the next 7 days.

Finished accounts payable aging report in Excel with KPI cards, aging chart and vendor table
The finished AP aging report built in this tutorial

Watch the video tutorial

Video overview

The video starts with the finished report, then builds it from a start file that holds only the bill register and the vendor terms. You add the new table columns, colour the buckets, build the KPI cards, fill the aging table and the vendor summary, add a 90+ alert badge and a chart, and finally record two payments to watch the whole report update. It is a build-along, so there is no free practice file; the ready-made version is linked below.

Get the ready-to-use Accounts Payable Aging Dashboard
All features, charts and vendor summary already built

What you will build

  • Payment terms per bill with XLOOKUP from the vendor table
  • Due date and balance for every bill
  • Days past due that never go negative, and paid bills that are never late
  • Aging buckets Current, 1-30, 31-60, 61-90, 90+ and Paid
  • KPI cards: total AP, overdue, overdue % and due in the next 7 days
  • An aging table and a vendor by bucket summary
  • A red or green alert badge and an aging chart

The start file: bills, vendors and terms

The start file has a bill register (an Excel table called tblBills) and a vendor table (tblVendors) with the terms. A bill date is not a due date: the vendor terms decide when you must pay, so the first job is to bring the terms into the bill list.

Start file with the bill register and vendor terms
The start file: the bill register and the vendor terms

Step 1: Terms, due date, balance and days past due

Type each formula in the first row of the table and Excel fills it down.

Terms:

=XLOOKUP([@Vendor],tblVendors[Vendor],tblVendors[Terms])

Due date:

=[@[Bill Date]]+[@Terms]

Balance:

=[@Amount]-[@Paid]

Days past due:

=IF([@Balance]=0,0,MAX(0,$C$5-[@[Due Date]]))

MAX(0, …) stops negative days for bills that are not due yet, and the balance test makes sure a paid bill is never late. Check one row: BILL-4113 from Ridgeline has a balance of $9,057.15, was due on 09/03/2026 and is 74 days past due.

Bills table with terms, due date, balance and days past due columns
Step 1: the new columns inside the bill table

Step 1b: Aging buckets with IFS

=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+"))

The balance is checked first so that a paid bill says Paid and not 90+. IFS then walks the conditions from left to right and stops at the first match.

Bucket column filled with IFS
The Bucket column filled for every bill

Step 2: Colour the buckets

Select the bucket column, choose New Rule, use a formula and add one rule per bucket, for example:

=$K9="90+"

Red with white text for 90+, salmon for 61-90, peach for 31-60, light yellow for 1-30, light green for Current and grey for Paid.

Bill register with bucket colours
Late bills now jump out of the list

Step 3: KPI cards with SUMIFS

The four cards on the dashboard sheet:

=SUM(tblBills[Balance])

=SUMIFS(tblBills[Balance],tblBills[Days Past Due],">0")

=C11/B11

=SUMIFS(tblBills[Balance],tblBills[Due Date],">="&C8,tblBills[Due Date],"<="&C8+7)

When a criterion comes from a cell or a calculation, put the operator in quotes and join it with an ampersand. The results are $76,329.58 total AP, $35,695.80 overdue, 46.8% overdue and $6,956.00 due in the next 7 days.

KPI cards for total AP, overdue, overdue percent and due next 7 days
Step 3: the four KPI cards

Step 4: The aging table

=COUNTIFS(tblBills[Bucket],$B15)

=SUMIFS(tblBills[Balance],tblBills[Bucket],$B15)

=D15/$D$20

This gives Current 16 bills $40,633.78 (53.2%), 1-30 5 bills $17,473.50, 31-60 4 bills $5,956.50, 61-90 3 bills $11,352.90 and 90+ 2 bills $912.90, 30 open bills in total.

Aging table with bills, amount and percent of AP
Step 4: bills, amount and share of AP per bucket

Step 5: Vendor by bucket summary with one formula

=SUMIFS(tblBills[Balance],tblBills[Vendor],$B24,tblBills[Bucket],C$23)

The dollar sign before B locks the column of the vendor names and the dollar sign before 23 locks the row of the bucket names, so one formula filled right and down builds the whole matrix. Add a colour scale to see where the overdue money sits: Ridgeline Outdoor Footwear owes $28,752.40, of which $17,437.40 is overdue.

Vendor by bucket summary with colour scale
Step 5: one SUMIFS formula fills the vendor by bucket matrix

Step 6: The 90+ alert badge and the aging chart

=IF(C19>0,"PAY NOW: "&C19&" BILLS OVER 90 DAYS","NO BILLS OVER 90 DAYS")

Two conditional formatting rules colour the badge: red when the text starts with PAY and green when it starts with NO. Then insert a clustered column chart from the bucket names and the amounts, remove the legend and add data labels.

Aging chart and red alert badge
Step 6: the alert badge and the aging chart

Pay the oldest bills and watch the report update

Record full payments for the two oldest bills in the Paid column. The 90+ bucket drops to zero bills, total AP falls to $75,416.68, overdue to $34,782.90 (46.1%) and the badge turns green: NO BILLS OVER 90 DAYS.

Bill register after recording two payments
Paid bills switch to the Paid bucket and the dashboard follows

Go further

If you would rather start from a finished file, the Accounts Payable Aging Dashboard in Excel has all of this built with extra features. To learn how to build dashboards like this with pivot tables, charts and slicers, see the Excel Pivot Tables & Dashboards course on the Next Gen Templates Academy.

Related tutorials: Inventory Management System in Excel VBA, Business Subscription Tracker in Excel and Gantt Chart in Excel 2026.

Get the ready-to-use Accounts Payable Aging Dashboard
All features, charts and vendor summary already built

Frequently asked questions

What is an accounts payable aging report?

It lists every unpaid vendor bill and groups it by how long it is past its due date, usually Current, 1-30, 31-60, 61-90 and 90+ days. It shows what you owe, how much is overdue and which vendors to pay first.

How do I calculate days past due in Excel?

First work out the due date as bill date plus the payment terms, then use the As Of date minus the due date, wrapped in MAX(0, …) so bills that are not due yet show zero instead of a negative number. A bill with a balance of zero is Paid and never late.

Why use IFS for the aging buckets?

IFS checks the conditions from left to right and returns the first match. Check the balance first so a paid bill says Paid, then 0 days is Current, up to 30 is 1-30, and so on up to 90+.

How do I fill the vendor by bucket table with one formula?

Use SUMIFS with the vendor in a column-locked reference and the bucket in a row-locked reference, then fill it right and down. The mixed references are the whole trick.

Is there a free practice file?

No. This is a build-along: you build the report from the bill register shown in the video. The finished, ready-to-use version is the Accounts Payable Aging Dashboard in Excel from Next Gen Templates.

By PK, Excel trainer and creator of Next Gen Templates.

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com