Excel PivotTable: Summarize Invoice Amounts by Reference and Due Date
Question details
The user needs to combine outstanding invoice amounts for records that share the same invoice reference and due date.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- An invoice data sheet contains invoice references, due dates, and outstanding amounts that need to be summarized into grouped totals.
- Observed behavior
- The desired result is a flat summary showing one row for each invoice reference and due date combination, with the outstanding amounts summed.
Check that the source data has one header row and that invoice references, due dates, and outstanding amounts are in separate columns. Confirm that due dates are recognized as dates and outstanding amounts are numeric values before creating the PivotTable.
Create a PivotTable grouped by invoice reference and due date
Use this method to automatically combine outstanding amounts for every matching invoice reference and due date.
A PivotTable groups identical field combinations and applies an aggregation such as Sum to numeric values. Using Tabular Form produces a flat, report-friendly result instead of nested row labels.
Click any cell in the invoice data range, or select the full range including the headers for Invoice Reference, Due Date, and Outstanding Amount.
Open the Insert tab and choose PivotTable. Confirm the selected table or range, choose a new worksheet or an existing worksheet, and select OK.
In the PivotTable Fields pane, drag Invoice Reference to the Rows area, then drag Due Date below it in the Rows area.
Drag Outstanding Amount to the Values area. Open its Value Field Settings or Summarize Values By menu and select Sum.
Open the PivotTable Design or PivotTable Tools tab, choose Report Layout, select Show in Tabular Form, then choose Subtotals and select Do Not Show Subtotals if a flat list is required.
Convert the source range to a table before building the PivotTable
Use this setup when new invoice rows will be added regularly and the summary should be easier to refresh.
Summarize Invoice Amounts with a WPS Spreadsheet PivotTable
WPS Spreadsheet can create the same grouped invoice summary directly. Insert a PivotTable, place invoice reference and due date in Rows, add outstanding amount to Values, and format the report as a flat table.
- 1. Open the invoice worksheet: Launch WPS Spreadsheet and open the workbook containing the invoice reference, due date, and outstanding amount columns.
- 2. Insert a PivotTable: Select the source range, open the Insert tab, choose PivotTable, confirm the range, and select the worksheet location.
- 3. Build the grouped summary: In the PivotTable Fields pane, drag Invoice Reference and Due Date to Rows, then drag Outstanding Amount to Values and set the calculation to Sum.
- 4. Format and refresh the report: Use the PivotTable Design tools to select Tabular Form and remove subtotals if needed. Refresh the PivotTable after adding or changing invoice records.
Frequently Asked Questions
Why does my PivotTable count invoice amounts instead of adding them?
The amount column may contain text or the Values field may be set to Count. Open the field menu in Values, choose Value Field Settings, and select Sum. If Sum is unavailable, convert the source values to numbers and refresh the PivotTable.
How can I group invoices by both reference and due date?
In the PivotTable Fields pane, place Invoice Reference in the Rows area and place Due Date directly beneath it. The PivotTable will create a separate group for each reference-and-date combination.
How do I remove subtotal rows from the invoice summary?
Select the PivotTable, open the Design or PivotTable Tools tab, choose Subtotals, and select Do Not Show Subtotals. Use Report Layout and Show in Tabular Form for a flatter output.
What should I do if due dates are grouped by month or year automatically?
Right-click a date item in the PivotTable and choose Ungroup if you need each individual due date. Also verify that the source column contains valid dates rather than text values.
Will new invoice rows appear automatically in the PivotTable?
If the source is an Excel table, new rows are included in the source after refreshing. Right-click the PivotTable and select Refresh; for a fixed range, update the PivotTable data source first.
