OmniDocUsing Pivot Tables for Accounting and Receivables
Learn how to build a pivot table for accounting and accounts receivable, from exporting a PDF statement to Excel to building an aging summary.
If you keep receivables in a customer statement PDF, you already have the data for an aging summary. The hard part is getting it into a form where a pivot table can read it. Much of the work goes to converting the statement into a clean table and then making sure the numbers add up the way your accounting records do.
This guide is for bookkeepers, accounts receivable clerks, and small business owners who need a pivot table for accounting work, specifically a summary of who owes what and how long it has been owed. You will learn how to move a statement from a PDF into a spreadsheet, set up the columns a pivot needs, and build an aging layout that shows customers against aging buckets.
The short answer is to convert the statement into one-row-per-invoice data, add a days-past-due column and an aging bucket column, and then pivot customers against buckets using the sum of open balances. The table below shows how that plays out in common situations.
How do you set up a pivot table for accounting? The short answer
Use a pivot table only after the source data is a clean, flat table. Each row should be one open item, and each column should hold one kind of value.
| Your situation | What to do | Why |
|---|---|---|
| You have a digital PDF statement | Export it to Excel with the OmniDoc PDF editor, then clean the table | The text is already selectable, so the export keeps the rows and columns |
| You have a scanned statement | Run OCR on the PDF first, then export it to Excel | A scanned page is an image, so the table data is not available until text is recognized |
| You want a summary by customer and age | Rows = customer, columns = aging bucket, values = sum of open balance | This is the standard receivables aging layout |
| You only need total owed per customer | Rows = customer, values = sum of open balance, no bucket column | Simpler to build and enough for a quick check |
| The totals do not match the statement | Check for text-formatted numbers and negative adjustments before changing the pivot | Many mismatches come from the source data, not the pivot settings |
The scenario: a receivables statement stuck in a PDF
Imagine a 12-page customer statement that lists 80 open invoices across many customers. The statement is a PDF, and each page repeats the column headers and includes a subtotal at the bottom. You need an aging summary by customer for a Monday review meeting, and the statement is the only complete record you have.
Copying the table by hand is slow and error-prone, and pasting from the PDF often puts everything into one column. You also need the report to match the statement's total. The goal is a spreadsheet that holds one row per invoice, with enough columns for a pivot table to group the balances by customer and by how old each invoice is.
The workflow
- Check whether the PDF is digital or scanned. Open the file and try to select a line of text. If you can highlight the words, the PDF is digital. If the page behaves like a single picture, it is scanned.
- Export the statement to Excel. Open the PDF in the OmniDoc desktop client and use its PDF editor to export to Excel. If the statement is scanned, run OCR on it first, then export. Keep the original PDF unchanged.
- Clean the table. Delete repeated header rows from each page, remove subtotal lines, and keep one header row at the top. Confirm the columns are customer name, invoice number, invoice date, due date, and open amount.
- Add a days-past-due column. Subtract the due date from your report date, for example the statement close date. Negative numbers mean the invoice is not yet due.
- Add an aging bucket column. Use a formula that sorts each row into Current, 1 to 30, 31 to 60, 61 to 90, or over 90 days. Keep the bucket names identical across all rows, because a pivot treats "31-60" and "31 to 60" as two different categories.
- Create the pivot table. Select the full cleaned range, then use your spreadsheet's pivot table command. Put customer in rows, aging bucket in columns, and sum of open amount in values. Turn on grand totals.
- Reconcile before you share. Compare the pivot grand total with the total open balance on the original statement. Then save a dated copy of the workbook so you can see what the report looked like at each review.
Where it can go wrong
Numbers exported as text. If the sum column shows zero, or the pivot counts rows instead of summing them, the amounts are probably stored as text. Convert the column to numbers, then refresh the pivot. Look for amounts that are left-aligned in the cells, which is a common sign of this problem.
Adjustments and payments. Refunds, returns, and other reductions should be negative amounts, and partial payments should reduce the open balance of the invoice they apply to. If a reduction appears as a positive number, the aging totals will be overstated. Compare a few customers against the statement to confirm the signs.
Layout breaks in the export. A table that spans several pages can export with merged cells, shifted columns, or a customer name on its own row. Scroll through the sheet and fix any rows that do not match the one-invoice-per-row pattern before you build the pivot.
Stale pivots. If you add new invoices or change a bucket formula, the pivot will not update on its own. Refresh it after every change, and check that the source range still includes the new rows.
Date basis. Decide whether aging is measured from the invoice date or the due date, and use the same basis every time. Mixing them makes month-to-month comparisons unreliable. Always check results against your own accounting rules.
🎯 Which approach fits you?
If you have a clean, digital statement
Export the PDF to Excel, clean the table, and build the pivot directly. This is the quickest path, and the reconciliation step usually takes only a few minutes.
If your statement is scanned
Run OCR on the PDF before exporting, then review the recognized numbers closely. Check any amount that looks unusual against the printed statement, because recognition errors are easy to miss in a long table.
If you only need a customer total
Skip the aging buckets and pivot customers against the sum of open balances. You can add buckets later if the review needs more detail.
If you report receivables every month
Keep one cleaned template workbook with the same column names and bucket formulas. Paste each month's export into it, refresh the pivot, and save a dated copy. This keeps the layout consistent and makes comparisons easier.
❓ FAQ
1. Can a pivot table show aging without a bucket column?
Usually not directly. A bucket column gives the pivot a category to group by, so the aging layout depends on it. You can still pivot total open balances by customer without buckets.
2. Why does my pivot table show a count instead of a sum?
The spreadsheet treats the values as text or sees blank cells in the amount column. Convert the amounts to numbers, remove any stray text, and refresh the pivot.
3. Should I use invoice date or due date for aging?
Use whichever basis your accounting policy specifies, and apply it to every row. Due date aging shows how late an invoice is, while invoice date aging shows how old it is.
4. Does the PDF export keep my formulas or formatting?
Treat the export as a source of raw data. Expect to re-enter or rebuild any formulas, such as the days-past-due and bucket columns, after you export the table.
📝 Summary
A pivot table for accounting works well once the statement is a clean, one-row-per-invoice table with a days-past-due column and an aging bucket column. Export digital PDFs to Excel with the OmniDoc PDF editor, run OCR first on scanned ones, and always reconcile the grand total against the original statement before you share the report.
