# Aging Report PDF to Excel: Buckets That Still Total

- Author: FastBankConvert (https://www.fastbankconvert.com)
- Published: 2026-06-08
- Updated: 2026-06-08
- Category: Other financial documents
- Canonical: https://www.fastbankconvert.com/blog/aging-report-pdf-to-excel

> Turn an aging report PDF to Excel: customer rows, 30, 60 and 90 day buckets, invoice detail lines, and grand totals that must still agree.
To turn an aging report PDF to Excel, keep one row per customer or invoice, with each aging bucket (current, 1 to 30, 31 to 60, 61 to 90 and over 90 days) in its own column. Delete subtotal rows, fill customer names down, then confirm that rows and columns both add up to the grand total.

## What does an aging report PDF hold?

An aging report PDF lists money owed, grouped by how long it has been outstanding. A receivables aging shows what customers owe you; a payables aging shows what you owe suppliers. Either comes as a summary, with one line per customer or vendor, or as a detail report, with every open invoice listed under its name.

The report is printed from an accounting system as of a single date, usually shown in the header. Every bucket is measured from that date. A report "as of September 30" puts an invoice due on September 15 in the 1 to 30 bucket. The same invoice on a report as of October 31 moves to 31 to 60. Write the as-of date on the sheet, because the buckets mean nothing without it.

### Fields on a summary aging

- **Customer or vendor name**.
- **Current**: not yet due, or due within the report's first band.
- **1 to 30, 31 to 60, 61 to 90 days** past due.
- **Over 90 days** past due.
- **Total** for the customer.
- **Grand total** row at the bottom.

### Extra fields on a detail aging

A detail report adds an invoice number, invoice date, due date, the open amount and sometimes the days past due for each line. Customer subtotals sit under each block. Detail is more work to convert, but it is the version you need if you want to chase invoices or re-age the report yourself.

### Aged by due date or invoice date?

Some systems age from the invoice date, others from the due date. The report header or the column labels usually say which. The difference matters: on 30-day terms, an invoice aged by invoice date looks 30 days older than one aged by due date. Note which method the report used before comparing it with another.

## Customer rows and 30, 60 and 90 day aging buckets in Excel

Aging buckets in Excel belong in separate number columns, one per band, with the customer in the first column and a total in the last. Keep the band labels as printed, since some systems use 0 to 30 and others 1 to 30. Each cell must hold a number, with zero rather than a dash for empty buckets.

### Dashes, blanks and brackets

Aging reports often print a dash or nothing at all in an empty bucket. After conversion, a dash is text, and SUM skips it without complaint, but a filter on zero will not find it. Replace dashes with 0 across the bucket columns using Find and Replace, matching the entire cell contents so you do not damage negative numbers.

Negative amounts, such as credits, may appear in brackets, like (250.00). If they come across as text, Excel will not total them. Convert them to real negative numbers before you check any totals.

### Column shift, the classic conversion fault

The most common fault in a converted aging is a value landing one bucket to the left or right. It happens when a customer name wraps onto two lines, or when an empty bucket has no text at all, so the converter has nothing to anchor the column on. The row total still looks right, which is why it goes unnoticed. Only the cross-foot check described below catches it.

## Invoice detail lines under each customer in an aging report

Invoice detail lines in an aging report sit under a customer heading, followed by a subtotal row. To make the sheet sortable, copy the customer name onto every invoice line, then delete both the heading rows and the subtotal rows. Keep the subtotals elsewhere first, since they are your check that each block came across whole.

### Steps for a detail report

1. Insert a Customer column at the left.
2. On each heading row, put the customer name in that column.
3. Fill the name down onto the invoice lines beneath it, using Go To Special on blanks and a formula pointing at the cell above.
4. Paste the column as values.
5. Copy the subtotal rows to a separate sheet, then delete them and the heading rows from the main table.
6. Delete page headers, repeated column titles and page numbers.

### Checking each block

With subtotals saved on their own sheet, a SUMIFS by customer on the cleaned table should equal each saved subtotal. A customer whose sum is off has lost an invoice to a page break, or gained a subtotal that was not deleted. Fix those before you go on, because the grand total check only tells you something is wrong, not where.

### Customers who matter for credit

A detail aging also shows which customers pay slowly. For a large account that keeps sliding into the 61 to 90 bucket, a business credit report adds the outside view; the guide to [a credit report PDF turned into Excel](/blog/credit-report-pdf-to-excel) covers pulling its payment history into the same workbook.

## Do the aging buckets still add up to the aging report grand total?

The aging buckets add up correctly when two checks agree: every row's buckets sum to that row's total, and every bucket column sums to the grand total row printed on the report. This is called cross-footing. If both pass, no amount was dropped and none moved to the wrong bucket.

### A worked example

The summary below uses example customers and amounts, not a real ledger.

| Customer | Current | 1-30 | 31-60 | 61-90 | Over 90 | Total |
| --- | --- | --- | --- | --- | --- | --- |
| Example Hardware | 2,400.00 | 1,150.00 | 0.00 | 0.00 | 0.00 | 3,550.00 |
| Lakeside Cafe | 0.00 | 640.00 | 820.00 | 0.00 | 0.00 | 1,460.00 |
| North Ridge School | 5,200.00 | 0.00 | 0.00 | 310.00 | 975.00 | 6,485.00 |
| Grand total | 7,600.00 | 1,790.00 | 820.00 | 310.00 | 975.00 | 11,495.00 |

Across: each customer's buckets add to its total. Down: each bucket column adds to its grand total, and the totals column adds to 11,495.00. The two directions meet in the bottom right corner.

### What a shift looks like

Suppose the converter had put Lakeside Cafe's 820.00 into 61-90 instead of 31-60. The row would still add to 1,460.00, so the row check passes. But the 31-60 column would sum to 0.00 against a printed 820.00, and 61-90 to 1,130.00 against 310.00. The column check fails in two adjacent columns by the same amount, which is the signature of one value in the wrong bucket.

### Formulas to keep

Add a column to the right with `=SUM(B2:F2)-G2` for each row, and a row underneath the grand total with `=SUM(B2:B4)-B5` for each column. Every result should be zero. Keep these checks in the sheet, so anyone who edits it later sees immediately if a number moves.

## Rebuilding aging buckets in Excel from invoice due dates

Rebuilding aging buckets in Excel means computing days past due for each invoice from its due date and an as-of date you choose, then assigning a bucket with a formula. You need a detail aging for this. It lets you re-age an old report to today, or compare two reports on the same basis.

### The formulas

Put the as-of date in a single cell, say J1. For an invoice with its due date in D2:

- Days past due: `=$J$1-D2`
- Bucket: `=IFS(H2<=0,"Current",H2<=30,"1-30",H2<=60,"31-60",H2<=90,"61-90",TRUE,"Over 90")`

Then a PivotTable with Customer in rows, Bucket in columns and the open amount in values rebuilds the summary report. Order the bucket columns by hand, since the pivot sorts them alphabetically. Its grand total must equal the sum of the open amounts.

### When the rebuilt report differs

If your rebuilt buckets differ from the printed ones, check three things. The as-of date may not match the report's. The report may age by invoice date, not due date. Or its bands may be 0 to 30 rather than 1 to 30, which moves invoices due exactly on the as-of date. Match those settings and the numbers should line up.

## Credits and unapplied payments that skew an aging report

Credits and unapplied payments appear in an aging report as negative amounts, and they can make a customer's buckets look better or worse than the truth. A payment received but not yet matched to an invoice sits in one bucket as a negative, while the invoice it pays still sits in another, older one.

### How it distorts the picture

Say a customer owes 975.00 over 90 days and has paid 975.00 that nobody applied yet. The report may show 975.00 in Over 90 and minus 975.00 in Current. The customer total is zero, which is right. But the Over 90 column looks like a collection problem that does not exist, and the Current column looks smaller than it is.

### What to do in the sheet

Do not net them out by editing the buckets. Add a Note column and mark rows with unapplied cash or open credits. Report the bucket totals as printed, with a line underneath for unapplied amounts. The fix itself belongs in the accounting system, where the payment gets applied to the invoice.

### Payables agings and recurring bills

On a payables aging, the same issue appears with supplier credits. Recurring vendors, such as your phone carrier or your card issuer, sit on it month after month. The aging tells you how much is owed but not what it was for. For that detail, [a phone bill PDF turned into Excel rows](/blog/phone-bill-pdf-to-excel) breaks out lines and charges, and the guide to [an American Express statement in Excel with each card member's charges](/blog/american-express-statement-to-excel) does the same for card spending. FastBankConvert is built for bank and card statements rather than aging reports, and it checks that the balances add up before you download. It is not open yet; [the homepage](/) shows a sample run.