Google spreadsheet data analysis for business teams

Google spreadsheet data analysis turns transaction rows into checked comparisons, such as sales by customer and month. Keep a source copy, build a clean working sheet, and prove that customer subtotals add back to the same monthly sales totals before explaining the movement.

Start with a consistent sales export

Use one row per posted sales document with document ID, posting date, customer ID and signed net sales. Keep the reporting currency and period consistent. Returns and credit memos must carry their accounting signs so the comparison measures net sales.

For this example, a US distributor compares January and February 2026 in USD. Put the following rows on a sheet named Raw, with headers in A1:D1:

The customer IDs here are readable labels; in a company workbook, use the source's stable customer ID rather than a name that can change. Record the extract date, period and document count beside the source copy.

The question is descriptive: which customers account for the monthly movement? Data analysis versus data analytics explains the wider distinction without changing this immediate task.

Prepare a clean working sheet

Duplicate the source into a sheet named Clean. Keep the original untouched so any correction remains reviewable. Check customer IDs for extra spaces and investigate duplicate document IDs against the source.

Google's data cleanup instructions describe trimming whitespace and removing duplicates using selected columns. They also note that trimming does not remove nonbreaking spaces. Choose the appropriate document key before using a duplicate-removal command; two legitimate invoices can have the same customer, date and amount.

On Clean, confirm that B2:B7 contains actual date values and D2:D7 numeric amounts. These controls should return zero:

=COUNTA(A2:A7)-COUNT(B2:B7)
=COUNTA(A2:A7)-COUNT(D2:D7)
=COUNTBLANK(C2:C7)

The first two compare the document count with the numeric date and amount counts. They identify missing or nonnumeric values; they do not prove that a date is the intended date. Check questionable entries against the export and document the correction.

For a longer file, extend all ranges through the same final source row. Recheck the range whenever a new export is pasted in. A correct formula covering an outdated range still produces an incomplete answer.

Build the customer-by-month comparison

On a Summary sheet, put customer IDs Alpha and Beta in A2:A3. Enter =DATE(2026,1,1) in B1 and =DATE(2026,2,1) in C1, formatted as month and year.

Enter this formula in B2, then copy across through C2 and down through row 3:

=SUMIFS(Clean!$D$2:$D$7,Clean!$C$2:$C$7,$A2,Clean!$B$2:$B$7,">="&B$1,Clean!$B$2:$B$7,"<"&EDATE(B$1,1))

Google documents SUMIFS as summing a range according to multiple criteria. This formula requires the customer to match, the posting date to be on or after the month start, and the posting date to be before the next month.

That exclusive upper boundary also handles dates containing times. A document posted late on the final day stays inside the month without an assumption about its timestamp.

In D2, enter =C2-B2 and fill down. Alpha's January net sales are 12,000 - 1,000 = $11,000. Beta's February net sales are 9,000 - 500 = $8,500. The change adds up: 4,000 + 500 = $4,500.

For a grouping workflow built around pivot tables, use sales pivot tables in Google Sheets. Keep the same source controls whichever layout you choose.

Prove the summary before reading the growth rate

In B4 and C4, sum the customer rows. In B6, independently total January from the date column without a customer condition, then copy across:

=SUMIFS(Clean!$D$2:$D$7,Clean!$B$2:$B$7,">="&B$1,Clean!$B$2:$B$7,"<"&EDATE(B$1,1))

In B7 enter =B4-B6, then copy across. Both differences must be zero. Also verify the full-file identity:

=SUM(B4:C4)-SUM(Clean!D2:D7)

It returns zero because 19,000 + 23,500 = $42,500, the six signed source amounts combined. Compare $42,500 and the six-document count with the original export controls as well.

Only then calculate total monthly growth with =(C4-B4)/B4. It is 4,500 / 19,000 = 23.68%, rounded to two decimal places. Most of the dollar increase belongs to Alpha; that observation does not establish whether price, quantity or timing caused it. Those questions need additional source columns.

Use Google Sheets what-if analysis when you are ready to test documented assumptions against this checked history.

Where it goes wrong

• Customer names split one account. Group by stable IDs and resolve label changes separately.
• Dates are text. Repair their interpretation before applying monthly criteria.
• Credit memos disappear. Keep signed sales so monthly comparisons remain net of reversals.
• Summary and controls share an incomplete source. Match both to the original export's scope, count and amount.
• Percentage growth becomes misleading. If the prior month is zero, show the dollar movement and label percentage growth as undefined.

Covirage is not affiliated with Google or Google Sheets.

Compare your own Google Sheet in Covirage

Bring your Google Sheet with customer IDs, dates and signed sales amounts. Covirage maps the columns, its tools compute and check the customer and monthly comparisons, and 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.

See spreadsheet analysis with Covirage for the supporting workflow.