digest
Short Summary
Main recommendation: Build an aging accounts receivable report with a PivotTable by first calculating days late with the TODAY function, looking up each category, and then using a unique identifier (the invoice number, or a self-built one if none exists) as the row field so duplicate customer rows are not summed. The video then uses the PivotTable's "Show Report Filter Pages" option to split the report into one sheet per aging category.
Key Points
- A PivotTable aggregates by default, so customers with multiple invoices on the same day collapse into one row unless each invoice has a unique identifier in the row area.
- Days late is calculated as TODAY minus the due date, which updates automatically each day the file is opened.
- A VLOOKUP against the lookup table returns the reporting category (such as 1 to 30, 31 to 60, 61 to 90, 91 to 365) for each invoice.
- Dragging the report label into the Filter area and using "Show Report Filter Pages" creates a separate pivot report on its own sheet for each category.
Details Worth Noting
- Concrete instructions:
- Days late: enter =TODAY()-due date in the days late column, then apply the General number format (Control Shift grave accent/tilde) to remove inherited date formatting.
- Report label: use =VLOOKUP(days late, lookup table range with F4 locking, 2) and leave the range lookup argument out so it defaults to approximate match.
- If no invoice number exists, build a unique ID: join the customer name, a " - " separator, and a COUNTIF counted over an expandable range (first cell locked with F4, e.g., Shift+colon range expanding downward) so each repeat gets a sequential number (name - 1, name - 2).
- Create the PivotTable with Alt, N, V, Enter (Alt, N, V, T, Enter in Excel 2010 and 2007). Put invoice number in Rows, days late in Values, and sort Values largest to smallest.
- Change Report Layout to Show in Tabular Form, turn off subtotals, rename the "Sum of" fields by editing the header and adding a trailing space, and set the invoice amount to Currency format.
- Move the report label to Filter, then go to Analyze, Options, Show Report Filter Pages and select the label to generate one sheet per category.
- The video notes that an earlier version of the series used the wrong formula (due date minus invoice date) and the presenter left an annotation about it.
- The data set in this video has an invoice number, so duplicates by customer name are not a problem; the self-built unique ID is only needed when the invoice number is missing.
- The formulas use dynamic TODAY, so the report changes each day the workbook is reopened.
Bottom Line
Using a unique identifier as the PivotTable row field, combined with a days-late calculation and a category lookup, lets Excel build a complete aging report automatically, with separate per-category sheets generated by a single PivotTable setting.
