Pivot table sales analysis in Google Sheets: a checked setup

Pivot table sales analysis in Google Sheets starts with a clean sales export, then groups net sales by customer, product and month. Set the value field to SUM and prove that the pivot's grand total equals the original export before interpreting a change. Keep filtered views tied to a separate source subtotal.

Start with sales lines you can reconcile

A pivot table summarizes the rows you give it. Decide whether those rows represent orders, invoices or recognized revenue, and put that definition above the finished table. The example below uses posted invoice lines, net of discounts and returns, for a fictional US distributor. All amounts are USD.

Create a tab named Raw. Paste these headers and rows into A1:F9. Dates must be actual spreadsheet dates; enter the displayed dates as dates, rather than text labels.

Do not paste an export's subtotal or grand-total rows underneath its sales lines. They would be summed again. Keep one currency in this example and retain the line ID so a suspicious amount can be traced back.

Before building anything, run these checks in spare cells:

=SUM(Raw!F2:F9)
=COUNT(Raw!A2:A9)=COUNTA(Raw!B2:B9)
=COUNT(Raw!F2:F9)=COUNTA(Raw!B2:B9)

The sum is $65,000. Both comparisons should be TRUE: every populated line has a numeric date and a numeric amount. They check data types, rather than whether a date or amount is commercially correct.

Build the customer-by-month pivot

Select Raw!A1:F9, then choose Insert > Pivot table and place it on a new sheet. Google requires a header for each source column and documents these controls in its pivot table instructions.

In the pivot table editor, use the following settings:

To group the dates, right-click a date cell within the pivot and select Create pivot date group. Choose the year-and-month grouping so January in different years stays separate. Google's customization guide covers date groups, sorting and filters.

You should get this result; the month labels below are shortened for readability.

Atlas's January amount is 12,000 + 6,000 = $18,000. Beacon's February amount is 11,000 + 5,000 = $16,000. The monthly totals add to 30,000 + 35,000 = $65,000, matching the export.

If dates will not group, repair the source dates first. For a controlled alternative, add a MonthStart header in G1 and enter this in G2, filling down:

=DATE(YEAR(A2),MONTH(A2),1)

Extend the pivot's source range to Raw!A1:G9, replace Date with MonthStart in Columns and format the dates as month and year. This keeps a real date value behind each monthly label.

Compare products without changing the sales base

Create a second pivot from the same range, replacing Customer with Product in Rows and retaining the monthly columns. Its results are:

Both pivots describe the same sales. Customer and product totals are separate views, so do not add their grand totals together. Use the Excel pivot counterpart when your team maintains the source workbook in Excel.

For a recurring actual-versus-target view, the Google Sheets sales dashboard adds a target table and variance calculations to the sales base.

Check filtered results and appended rows

Filter Region to East. The customer pivot should now show Atlas Supply alone, totaling $37,000. Compare it with a source formula using the same definition:

=SUMIFS(Raw!F2:F9,Raw!E2:E9,"East")

SUMIFS sums amounts whose rows meet all specified criteria. This check is independent of the pivot's visible totals. For a particular month, add date boundaries rather than comparing a month name.

Google Sheets refreshes a pivot when cells within its source range change. A row pasted below a fixed range is outside that range. After the next export, open the pivot editor, inspect Select data range, extend the range to the final sales line and repeat the checks. If you filter by values, reopen the filter choices and include any new customers, products or regions that belong in the view.

Remove the East filter and confirm $65,000 returns. Then compare both pivots' grand totals with the source sum. The five spreadsheet total checks give a broader review before a sales meeting.

Where it goes wrong

• Counting amounts. COUNT reports populated entries rather than revenue. Confirm SUM and repair numeric-looking text in the source.
• Combining years. A month-only label can combine separate January periods. Keep year and month together.
• Mixing sales definitions. An order export and an invoice export measure different events. Label the basis and compare consistent periods.
• Forgetting active filters. A filtered subtotal can look like the company total. Display the selected region beside the pivot and reset it during reconciliation.
• Missing credits or new rows. Excluded credit lines overstate sales; appended rows outside the source range understate them. Check both the range and the export's inclusion rules.

Covirage is not affiliated with Google or Microsoft.

Check your own sales file in Covirage

Bring your sales export or Google Sheet with dates, customers, products and net amounts. Covirage maps the columns, and its tools compute and check the sales figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; creating the account takes about a minute.

See Covirage for sales teams for the business context.