How to Create Ageing Buckets in Excel: Easy Guide

Sharing is caring!

Managing accounts receivable or outstanding invoices requires visibility into how long payments have been due. One of the most useful tools for this is an ageing bucket. In Microsoft Excel, creating ageing buckets helps track overdue amounts based on the number of days outstanding.

In this guide, we will show you how to create ageing buckets in Excel using simple steps, formulas, and built-in tools. Whether you’re handling customer payments, tracking vendor invoices, or building a receivables aging report, this tutorial is for you.

What Are Ageing Buckets?

Ageing buckets are categories that group outstanding balances or invoices based on how long they’ve been unpaid. Typically, the buckets follow common intervals like:

These buckets help businesses analyze overdue accounts, prioritize collections, and improve cash flow management.

Step 1: Prepare Your Data

Start with a clear data table that includes the following columns:

Ensure your dates are in date format and not text. You can do this by selecting the column, right-clicking, and choosing Format Cells > Date.

Step 2: Calculate Days Outstanding

To create ageing buckets, you need to calculate the number of days past due for each invoice.

Create a new column called Days Outstanding and enter the following formula:

=IF([@[Payment Status]]="Paid", 0, TODAY() - [@[Due Date]])

This formula checks if the payment is already made. If so, it returns 0. Otherwise, it calculates how many days have passed since the due date using the TODAY() function.

For example:

Step 3: Define Ageing Buckets

Now, you’ll create a new column called Ageing Bucket to categorize each invoice based on the Days Outstanding.

Use this formula in the Ageing Bucket column:

=IF([@[Days Outstanding]]<=30,"Current",
IF([@[Days Outstanding]]<=60,"31–60 Days",
IF([@[Days Outstanding]]<=90,"61–90 Days","Over 90 Days")))

This formula segments each entry into the proper ageing group. You can modify the thresholds if your business uses different intervals.

Example Output:

Step 4: Use Conditional Formatting (Optional)

Conditional formatting helps highlight which invoices are more critical.

Here’s how to add color coding:

1. Select the Ageing Bucket column.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Format only cells that contain.
4. Set the rule to format cells equal to "Over 90 Days" and apply a red fill.
5. Repeat for other buckets using different colors (yellow for 31–60, green for Current, etc.).

This visual aid helps you quickly spot overdue accounts.

Step 5: Create a Summary Table

To summarize total amounts by ageing bucket, use a Pivot Table.

1. Select your data table including the Ageing Bucket and Amount.
2. Go to Insert > PivotTable.
3. Drag Ageing Bucket to the Rows area.
4. Drag Amount to the Values area.
5. Change the aggregation to Sum if it’s not already.

Your result will look like this:

This table helps management and finance teams review ageing trends and unpaid balances.

Step 6: Create a Dynamic Ageing Report with Named Ranges

If you want to automate ageing buckets, you can use Excel Tables and named ranges. Excel Tables automatically adjust as new rows are added.

1. Select your dataset and press Ctrl + T to create a table.
2. Name the table in the Table Design tab (e.g., InvoiceTable).
3. Update formulas to use structured references (already used in earlier examples).

Using named tables ensures your formulas auto-update with new invoices.

Step 7: Group by Custom Bucket Ranges

If your business uses custom ranges like “1–15”, “16–30”, etc., you can adjust the formula like this:

=IF([@[Days Outstanding]]<=15,"1–15 Days",
IF([@[Days Outstanding]]<=30,"16–30 Days",
IF([@[Days Outstanding]]<=45,"31–45 Days",
IF([@[Days Outstanding]]<=60,"46–60 Days","Over 60 Days"))))

Custom buckets provide granular insights into overdue accounts.

Step 8: Ignore Negative Days (Optional)

Some invoices might have a future due date, resulting in negative values.

To ignore them in the bucket formula, adjust like this:

=IF([@[Days Outstanding]]<0,"Not Due",
IF([@[Days Outstanding]]<=30,"Current",
IF([@[Days Outstanding]]<=60,"31–60 Days",
IF([@[Days Outstanding]]<=90,"61–90 Days","Over 90 Days"))))

This helps avoid confusion between future and overdue invoices.

Step 9: Filter Ageing Buckets for Analysis

Use AutoFilter to analyze specific buckets:

1. Click the filter drop-down in the Ageing Bucket column.
2. Select only "Over 90 Days" to focus on critical invoices.
3. You can sort by Days Outstanding or Amount.

This allows easy review and follow-up on long-pending payments.

Step 10: Visualize the Data

Creating a bar chart or pie chart helps you present the ageing data clearly.

To insert a pie chart:

1. Use the summary pivot table created earlier.
2. Select the Ageing Bucket and Total Amount columns.
3. Go to Insert > Charts > Pie.

You’ll get a clear view of how much money is tied up in each bucket category.

Excel Features That Help with Ageing Buckets

Common Use Cases for Ageing Buckets

• Accounts Receivable: Track unpaid invoices by customer.
• Accounts Payable: Monitor how long vendor bills have remained unpaid.
• Loan Tracking: Analyze how overdue loan repayments are.
• Credit Risk Analysis: Assess customer creditworthiness.
• Debt Collection: Prioritize older invoices for collection efforts.

Tips for Better Ageing Reports

• Use freeze panes so headers stay visible while scrolling.
• Protect formulas by locking cells in Excel to avoid accidental changes.
• Include invoice numbers and customer names to identify problem accounts.
• Review and update the file monthly or weekly for accuracy.
• Store files in OneDrive or SharePoint for team access.

Final Thoughts

Creating ageing buckets in Excel helps businesses monitor outstanding balances, manage cash flow, and improve collection efficiency. With simple formulas, a structured table, and a few Excel features, you can build a powerful ageing report that saves time and enhances decision-making.

Keep your data clean, automate with structured references, and summarize with pivot tables to make the most of Excel’s capabilities. Whether you’re a bookkeeper, accountant, or small business owner, this tool is a must-have in your financial workflow.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.