Pivot Tables for CSV Data: A Practical Guide

The Core Idea: Rows to Categories, Values to Numbers

Start with raw transaction-level data: one row per sale, per event, per record. A pivot table asks three questions:

• What do you want as rows? (e.g. one row per product)
• What do you want as columns? (e.g. one column per month, optional)
• What number summarizes each cell? (e.g. sum of sales, count of orders, average price)

Pivot Table vs. Group By: When to Use Which

These two are closely related, and the distinction matters for choosing the right tool:

• Group By summarizes into one column of results per group, "total sales per product." Simpler, and enough for most one-dimensional summaries.
• Pivot Table summarizes across two dimensions at once, rows and columns, "sales per product, broken out by month." Use this when you need a cross-tabulation, not just a single summary column.

Rule of thumb: if your question has one "by" in it ("total sales by product"), use Group By. If it has two ("sales by product, by month"), use a pivot table.

Choosing the Right Aggregation

The summary function you pick changes what the numbers mean:

• Sum for totals (total revenue, total units)
• Count for frequency (how many orders, how many events)
• Average for typical value (average order size), but watch out for outliers skewing it
• Min/Max for range (earliest date, highest price)

Picking "sum" when you meant "average" is a common mistake that silently produces plausible-looking but wrong numbers, always sanity-check a pivoted total against a manual spot-check of the raw data.

Common Pitfalls

• Blank/inconsistent category values before pivoting create noisy, fragmented results, "Widget" and "widget " will pivot as two separate rows.
• Missing values in the value column can silently get excluded from a sum rather than treated as zero, check how your tool handles nulls before trusting totals.
• Too many distinct column values (e.g. pivoting on a nearly-unique ID column) produces an unreadable table with hundreds of columns, pivot on categorical, low-cardinality fields.

Doing This in How To CSV

The Pivot tool builds cross-tabulations directly from your CSV, and Group By handles the simpler one-dimensional case, both processed locally in your browser so large files don't need to round-trip through a server.