How to use a pivot table generator
Start with one record per row. Paste it into Paste CSV/TSV data, or load a CSV, TSV, TXT, XLSX, or XLS file. Place the fields in Row Fields, Column Fields, or Value Fields & Aggregation. The preview recalculates when you change an assignment or option.
- Paste data into Paste CSV/TSV data, or use Drop a CSV, TSV, or spreadsheet file here and browse files.
- Leave First row is a header selected when the first row contains field names. Clear it for data without a header row.
- Use +Row, +Col, or +Value, then use the arrow controls to change order.
- Choose Count, Sum, Average, Minimum, or Maximum for each field in Value Fields & Aggregation.
- Set Missing/blank values, Empty cell display, Case-sensitive grouping, and the subtotal and total options.
- Check Pivot table preview, then select Export CSV or Export Spreadsheet.
How to create a pivot table from CSV
Put a field in Row Fields for groups down the left and Column Fields for groups across the top. Put a numeric or record field in Value Fields & Aggregation, then choose its aggregation. With no value field, the table uses Count of records. Two or more row fields create nested groups.
region,product,quarter,amount
East,Widget,Q1,1200
East,Gadget,Q1,900
West,Widget,Q1,1100Use region as a row field, quarter as a column field, and amount with Sum. East and Q1 produce 2100 because the two East records add to that value. West and Q1 produce 1100. Grand totals appear when selected.
What the pivot table options change
For an assigned value field, Exclude from aggregation leaves blank cells out of Count, Sum, Average, Minimum, and Maximum. Treat as zero counts a blank as zero. Non-numeric text is skipped by Sum, Average, Minimum, and Maximum. Stats show missing and non-numeric counts.
Case-sensitive grouping keeps USA and usa separate. Clear it to combine them. The tool trims group labels, and a missing row or column value is labeled (blank). Empty cell display supplies text for a combination with no matching records.
Show row subtotals (2+ row fields) adds a subtotal after each outer row group when at least two row fields are assigned. Show grand total row and Show grand total column calculate totals across the matching records.
Pivot table input limits and parsing
- For XLSX and XLS files, only the first worksheet is loaded.
- For pasted text, the tool chooses tab only when the first line has more tabs than commas; otherwise it chooses comma. Keep the same delimiter on later lines.
- Short rows receive blank values until they match the widest row.
- The tool does not clean or validate source data before grouping.
Examples with sales and ticket data
Group a sales export by region and quarter, then sum an amount field. Count records by category and status in a ticket export. Multiple value fields can use separate aggregations.
Use Average, Minimum, or Maximum on a numeric column. Set Empty cell display for combinations with no records, then export the preview as a spreadsheet.
Pivot tables compared with other dataset transforms
Use Dataset Transform Tools for another browser-based transform. This page reads, groups, and exports content in the browser. Its script sends nothing through fetch, XMLHttpRequest, sendBeacon, or a form post. Choose Transpose Table to swap rows and columns without grouping.
Choose Unpivot Table to turn wide columns into long attribute/value records. Choose Rename Columns to map old headers to new headers while keeping the column order and cell values.
Frequently asked questions
How do I create a pivot table from CSV data?
Paste CSV/TSV data or upload a CSV, TSV, TXT, XLSX, or XLS file. Assign fields, choose an aggregation, and check Pivot table preview.
How does a pivot table handle blank values?
For an assigned value field, Exclude from aggregation leaves blank cells out of the calculation. Select Treat as zero to count blanks as zero for numeric aggregations. With no value field, Count of records counts each record.
What does Empty cell display do in a pivot table?
Empty cell display sets the text shown when a row and column combination has no matching records. A blank value inside a matching record is handled by Missing/blank values.
Can a pivot table show subtotals and grand totals?
Show row subtotals (2+ row fields) adds subtotals after outer row groups when at least two row fields are assigned. Show grand total row and Show grand total column control the overall totals.