Excel PivotTables for Business: Build, Filter, and Check
Excel PivotTables for business help you summarize a clean transaction table without writing a separate formula for every category. This lesson takes a four-invoice dataset from source rows to a checked service-by-month summary.
Define the question and the record grain
Our question is: how much revenue did each service produce in each month? In this original example, one row represents one invoice, all amounts use the same currency, and the dates are invoice dates. The result therefore describes billed revenue by invoice month; it is not a cash-collection report.
Copy the following headers and rows into Excel. Convert the range to a table using Insert → Table, confirm that headers are present, and name it Sales. Keep InvoiceID as text if leading zeros matter. Use actual Excel dates and numeric amounts, rather than text that merely looks like them.
| Date | InvoiceID | Service | Customer | Revenue | DirectCost |
|---|---|---|---|---|---|
| 2026-01-05 | 001 | Bookkeeping | Alpha | 1,000 | 600 |
| 2026-01-12 | 002 | Advisory | Beta | 1,500 | 600 |
| 2026-02-03 | 003 | Bookkeeping | Alpha | 500 | 400 |
| 2026-02-15 | 004 | Advisory | Gamma | 2,000 | 800 |
Before creating the summary, verify four source records, revenue of 5,000, and direct cost of 2,400. There are no subtotal rows in the dataset. A report export with both detail and subtotal rows needs cleaning before it becomes a trustworthy source.
Create the first PivotTable
- Select a cell in Sales and choose Insert → PivotTable.
- Confirm the source table and place the PivotTable on a new worksheet.
- Drag Service to Rows, Date to Columns, and Revenue to Values.
- Check the Values setting is Sum of Revenue. If it is Count, investigate text amounts and choose the appropriate summary.
- For a standard desktop PivotTable, group valid dates by Months and Years. Include years so January in different years is not combined unintentionally. Grouping controls vary by platform and data source.
- Format the value field as a number or currency and give the report a descriptive title.
Reconcile the result
| Service | January 2026 | February 2026 | Total |
|---|---|---|---|
| Bookkeeping | 1,000 | 500 | 1,500 |
| Advisory | 1,500 | 2,000 | 3,500 |
| Total | 2,500 | 2,500 | 5,000 |
Check both directions: service totals 1,500 + 3,500 = 5,000 and month totals 2,500 + 2,500 = 5,000. Also compare the grand total with =SUM(Sales[Revenue]). A total can agree even when categories are mislabeled, so inspect at least one service and one date boundary.
Add useful filters
Place Customer in Filters, or use a slicer where your Excel version supports it. Selecting Alpha should show 1,500 revenue, all in Bookkeeping. Clear the filter to restore 5,000. Label any active filter when presenting the result. “Revenue is 1,500” is incomplete without saying it is Alpha’s revenue for the selected period.
To summarize expenses, use an expense dataset with an appropriate category and date field; do not treat DirectCost here as every operating expense. To summarize customer balances, use invoice amount less applied payments at a stated as-of date. The summary method is similar, but the business definition changes.
Refresh and test a new row
Add invoice 005 dated February 20 for Bookkeeping, customer Delta, revenue 700 and direct cost 300. Refresh the PivotTable. Expected revenue is now 5,700; Bookkeeping becomes 2,200 and February becomes 3,200. If the row is absent, check that it is inside the Sales table, the source is correct, and filters are cleared.
A table can expand with new rows, but the PivotTable still needs an appropriate refresh. Explicitly refresh and inspect the result rather than assuming an automatic-refresh option is enabled.
Common mistakes and interpretation limits
- Counting records when the question asks for a dollar total.
- Grouping text dates or blank dates without investigating them.
- Including source subtotal rows and double-counting revenue.
- Leaving a customer filter active during a company-wide review.
- Averaging invoice margins to report an overall margin.
For the original four rows, gross profit is 5,000 − 2,400 = 2,600 and aggregate gross margin is 52%. The simple average of row margins—40%, 60%, 20%, and 60%—is 45%, a different statistic. Use total profit divided by total revenue when that is the metric intended. A PivotTable organizes evidence; it does not establish the cause of a change.
Frequently asked questions
Why does Excel show Count instead of Sum?
Excel may have interpreted the field as text or mixed data. Inspect and correct the source values, then verify the summary setting.
Why group by both year and month?
It distinguishes January 2026 from January 2027 when the source spans multiple years.
Does refreshing fix bad source data?
No. It brings the current source into the summary; incorrect dates, duplicate records, and classifications still need investigation.
Can I use this for customer aging?
Use invoice balances and due dates at a clearly defined as-of date. A summary of original invoice amounts alone is not an aging report.
Sources and further learning
Official documentation reviewed November 3, 2026. Worked examples, learning sequences, and practice answers are original teaching illustrations. Product interfaces and availability can vary by version, platform, plan, and region.