Pivot Tables for Accountants: 6 Reconciliation and Reporting Examples

9/2/2026

Most pivot table tutorials use a sales dataset: regions, products, units sold. That is fine for learning the buttons and useless for learning what a pivot table can do in an accounting file. Accountants work with general ledger detail, open invoices, vendor payments, bank exports and payroll registers, and the questions they ask of that data are specific: does it tie, what changed, what is old, who crossed a threshold.

This post works through six pivot table examples drawn from that work. Each one gives the source data, the layout, the setting that makes it work and the mistake that most often breaks it. It assumes you know how to insert a basic PivotTable. If you do not, the Tackle Excel Pivot Tables webinar covers the mechanics.

Before Any Pivot: Set Up the Source Data Properly

Every example below depends on the same groundwork, and most pivot table errors in accounting files come from skipping it.

Use a Table as the source. Convert the data to an Excel Table (Ctrl+T) before inserting the pivot. When next month's rows are added, the pivot picks them up on refresh. A pivot built on a fixed range such as A1:H5000 silently ignores row 5001.

One header row, no blanks, no subtotals. Exports often include report titles, blank separator rows and subtotal lines. Strip them. A subtotal row left in the data is counted twice.

One data type per column. Dates must be real dates, and amounts must be numbers. If an amount column contains text or blanks, Excel defaults the value field to Count rather than Sum, which is often the first sign the column needs cleaning.

Refresh deliberately. Pivot tables do not recalculate when the source changes. Right-click and Refresh (or Refresh All on the Data tab) before relying on a result. A stale pivot is one of the most common causes of a schedule that does not tie.

Keep amounts signed consistently. Decide whether credits are negative in the source and stick to it, or totals will net incorrectly.

Example 1: Summarize the General Ledger by Account and Month

The question: what is the activity in each account by month, and does it tie to the trial balance?

Source data: a GL detail export with account number, account name, date, description and a single signed amount column (debits positive, credits negative).

Layout:

Rows: account number, then account name
Columns: date, grouped by month (and by year, if the data spans more than one)
Values: sum of amount

The setting that makes it work: right-click any date in the column area and choose Group, then select Months and Years. Without Years, January of two different years is combined into one column.

How to use it: compare the grand total for each account to the trial balance. Any difference points to entries missing from the export, a filter left on, or a date range that does not match. Turning the pivot into a monthly flux view is then a matter of adding a "Difference From" calculation (see Example 3).

The common mistake: exporting debits and credits as two separate columns and summing only one, or summing both without a sign. Build a single signed amount column in the source with a formula before pivoting.

Example 2: Build an Accounts Receivable Aging

The question: how much each customer owes, and how old it is.

Source data: open invoices with customer, invoice number, invoice date, due date and open balance. Add a helper column in the Table for days past due, calculated as the as-of date minus the due date. Put the as-of date in a labeled input cell so the aging can be rerun for any date.

Layout:

Rows: customer
Columns: days past due, grouped
Values: sum of open balance

The setting that makes it work: right-click a days-past-due value and choose Group. Set the starting point and an interval of 30 to create buckets. Many firms prefer explicit labels such as Current, 1-30, 31-60, 61-90 and Over 90; for that, add a bucket column to the source with a lookup to a small bucket table, and pivot on the label instead. A lookup table also makes the buckets easy to change.

How to use it: sort customers by the largest balance in the oldest bucket. Use "Show Values As, % of Row Total" to see what share of each customer's balance is overdue. The total of the pivot should equal the AR control account; if it does not, the open-invoice report and the GL disagree, and that difference is worth investigating before anyone relies on the aging.

The common mistake: calculating days past due with today's date built in, so the aging changes every time the file is opened. Use a fixed as-of date.

Example 3: Variance by Department, Budget Versus Actual

The question: which departments are over or under budget, by how much, and in which accounts?

Source data: stack actual and budget in one Table with the same columns (department, account, period, amount) plus a "Scenario" column with the value Actual or Budget. Stacking is the key step: a pivot compares values within one field far more easily than it compares two separate tables.

Layout:

Rows: department, then account
Columns: Scenario
Values: sum of amount

The setting that makes it work: add the amount field to Values a second time and set "Show Values As" to Difference From, with the base field Scenario and the base item Budget. The pivot now shows the dollar variance next to the actuals. Add it a third time with % Difference From for the percentage variance.

How to use it: add a slicer for period so the same pivot shows the month or the year to date. Filter to variances above a threshold to focus the commentary on what matters.

The common mistake: writing a calculated field such as =Actual-Budget on two separate amount columns and then using a ratio calculated field for percentages. Microsoft's documentation notes that calculated fields work on the sum of the underlying fields, not row by row. That is fine for a simple difference of totals but gives misleading results for anything that should be calculated per row and then added, such as a quantity times a varying price. Do row-level math in the source Table, and use Show Values As for comparisons.

Example 4: Vendor Totals for Year-End 1099 Review

The question: which vendors were paid enough during the year to need an information return?

Source data: a payments (disbursements) export for the calendar year with vendor name, vendor tax classification (from the vendor master), payment date, payment method and amount. Add the vendor's W-9 status if you track it.

Layout:

Rows: vendor
Filters: payment method, vendor tax classification
Values: sum of amount

The rules to apply, not to assume: for payments made after December 31, 2025, the reporting threshold for Forms 1099-NEC and 1099-MISC is $2,000, and the IRS says it may be adjusted for inflation beginning in 2027. Payments made by credit card, debit card or through third-party payment networks are generally reported on Form 1099-K by the payment settlement entity, so they come out of the payer's 1099-NEC totals. Payments to corporations are generally exempt from 1099-NEC reporting, but payments for legal services are reportable even when the law firm is a corporation. The IRS instructions for Forms 1099-MISC and 1099-NEC set out the full rules and exceptions.

The setting that makes it work: filter payment method to exclude card and third-party network payments, then use a Value Filter on vendor (Sum of amount, greater than or equal to 2000) to list vendors at or above the threshold. Review the classification filter separately for corporations, keeping attorneys in the list.

How to use it: the result is a review list, not a filing list. Each vendor still needs a check on what the payments were for and whether a W-9 is on file. Form 1099-NEC is due to both the IRS and the recipient by January 31, moving to the next business day when that date falls on a weekend or legal holiday. Our post on 1099 and W-2 filing deadlines covers the calendar, and the Basics of 1099 Reporting webinar covers the forms.

The common mistake: the same vendor appearing under two names ("Smith Plumbing" and "Smith Plumbing LLC"), so neither line crosses the threshold. Clean vendor names, or better, pivot on vendor ID, before filtering.

Example 5: Reconcile Two Lists That Should Agree

The question: which items appear in one list but not the other? Bank activity against the cash ledger, the AP subledger against the GL, intercompany balances between two entities.

Source data: stack both lists in one Table with a "Source" column (Bank or Book, for example), a matching key and a signed amount. Reverse the sign on one side, so that matching items net to zero. The matching key might be a check number, a reference, or a combination of date and amount built with a formula.

Layout:

Rows: matching key
Columns: Source
Values: sum of amount, plus a count of amount

The setting that makes it work: with signs reversed, the grand total column for each key should be zero. Apply a Value Filter on the row field to show only keys where the total does not equal zero. What remains is the list of reconciling items: outstanding checks, deposits in transit, bank fees, errors. The count column shows keys that appear more than once on one side, which usually means a duplicate entry.

How to use it: this approach handles thousands of lines in seconds and is easy to rerun. It pairs well with Power Query to pull and clean both exports. Rounding can create tiny residues, so round amounts in the source Table to two decimals before pivoting.

The common mistake: a key that is not unique. If two checks share the same amount and date, a date-amount key will net them together. Use the most specific identifier available, and investigate any key with a count above one on either side.

Example 6: Payroll Register Summary for Quarterly Tie-Outs

The question: do the payroll register totals agree with what was reported and deposited for the quarter?

Source data: a payroll register export with employee, pay date, earnings type and the withholding and employer tax columns, unpivoted into a long format (one row per employee, pay date and item). Power Query's Unpivot Columns command turns a wide register into this shape in a few clicks, and the long format is far easier to pivot.

Layout:

Rows: item (gross wages, federal income tax withheld, Social Security and Medicare wages and tax, and so on)
Columns: pay date, grouped by quarter and year
Values: sum of amount

The setting that makes it work: group pay dates by Quarters and Years. Quarterly federal payroll returns are prepared on wages paid in the quarter, so group on pay date rather than period-end date.

How to use it: compare each quarterly total to the amounts on the quarterly return and to the deposits made, and compare the year totals to the annual wage statements. Add employee to the rows to find an individual whose year-to-date figures look wrong. Our year-end payroll reconciliation checklist lists the tie-outs this pivot supports.

The common mistake: including voided or reversed checks in the export without their reversals, which inflates totals. Confirm the export includes both, or excludes both.

Pivot Table Settings Every Accountant Should Know

A short reference to the features used above.

Feature	Where it helps	Note
Group dates by Months and Years	GL activity, payroll quarters	Always include Years if data spans more than one
Group numbers by interval	AR aging buckets	A bucket column in the source gives custom labels
Show Values As: % of Row Total	Aging mix, account mix	Also % of Grand Total and % of Column Total
Show Values As: Difference From	Budget variance, period-over-period flux	% Difference From for percentages
Value Filter	Threshold reviews, non-zero reconciling items	Filters on the summarized value, not the rows
Slicers	Period or entity selection	Can control several pivots at once
Refresh / Refresh All	Every use	Pivots do not update on their own
GETPIVOTDATA	Pulling pivot results into a report	Keeps references stable when the pivot layout shifts
Calculated field	Simple totals-based calculations	Works on sums, not row by row
Distinct Count	Number of unique vendors or customers	Requires adding the data to the Data Model

When Not to Use a Pivot Table

Pivot tables are a summarizing tool, and some accounting jobs are better done another way.

A formatted report that must look identical every month, such as a financial statement package. Formulas such as SUMIFS against a mapped trial balance give you control over layout and are easier to review line by line.
Row-level calculations. Do them in the source Table, not in a calculated field.
Anything that needs to stay live without a refresh. Formulas recalculate on their own; pivots do not.

Many experienced accountants use pivots to explore and check, and formulas to report. The Essential Excel Skills course covers the formula side, and our posts on financial ratio analysis and working capital analysis show the kind of analysis that often starts with a pivot summary.

Practice With Your Own Data

The fastest way to learn pivot table analysis is to rebuild one schedule you already prepare by hand. Take last month's AR aging or GL flux, rebuild it as a pivot on a Table, and compare the two. Where they differ, you will find either a pivot setting you need to learn or an error in the manual version.

For structured training on the mechanics and the settings above, the Tackle Excel Pivot Tables webinar is our focused option, and the Excel training for accountants page lists the wider range.

Make Pivot Tables Part of Your Close

Used well, pivot tables turn hours of manual summarizing into minutes and make reconciling items stand out. The six examples above cover most of what an accountant needs: summarize, age, compare, threshold, reconcile and tie out. To build the skills in a structured way, look at the Tackle Excel Pivot Tables webinar.

Frequently Asked Questions

What is a good pivot table example for accountants?

A general ledger summary is the classic example: GL detail in rows by account, dates grouped by month in columns, and the sum of a signed amount column in values. It shows monthly activity per account and should tie to the trial balance. Other strong examples are an AR aging with days past due grouped into 30-day buckets, a budget-versus-actual variance using Difference From, and a vendor payment summary for year-end 1099 review.

How do I use a pivot table for reconciliation?

Stack the two lists in one Table with a Source column, a matching key and a signed amount, reversing the sign on one side. Pivot with the key in rows and Source in columns, then use a Value Filter to show only keys whose total is not zero. Those are your reconciling items. Add a count of amount to catch keys that appear more than once, which usually indicates a duplicate. Round amounts to two decimals first to avoid tiny residues.

Why is my pivot table showing Count instead of Sum?

Excel defaults a value field to Count when the source column contains text or blank cells. In accounting exports this usually means some amounts came through as text, or there are blank or subtotal rows. Clean the source column so every entry is a number, then change the field setting to Sum. Leaving it as Count, or forcing Sum on a column that still contains text amounts, gives totals that will not tie.

Why doesn't my pivot table update when the data changes?

Pivot tables do not recalculate automatically. You must refresh them, either by right-clicking and choosing Refresh or with Refresh All on the Data tab. If new rows are also missing after a refresh, the pivot is probably based on a fixed range rather than an Excel Table. Change the source to a Table so new rows are included every time you refresh.

Can a pivot table calculate 1099 vendor totals?

It can produce the review list, but the rules still need to be applied. Pivot calendar-year payments by vendor, filter out card and third-party network payments (generally reported on Form 1099-K), and use a Value Filter for vendors at or above the threshold, which is $2,000 for payments made after December 31, 2025. Then review vendor classification, since payments to corporations are generally exempt but legal services are reportable, and confirm W-9s are on file.

What is the problem with calculated fields in pivot tables?

Calculated fields operate on the sum of the underlying fields, not row by row. A formula that multiplies quantity by price, for example, multiplies total quantity by total price rather than adding up each line's result. For simple differences between totals that is fine, but for row-level calculations it gives wrong answers. Do row-level math in a column of the source Table, and use Show Values As for percentages and variances.