How to summarize sales data by region and product using a browser pivot generator
- Step 1Check Developer tier and flatten the export — Confirm you are on Developer (the run is rejected otherwise). Make sure the export is flat: a single header row then one row per order, with at least
Region,Product(or SKU), andRevenue(or Units) columns. - Step 2Put the data on the first sheet — Only sheet one is read. If the transactions live on a
DataorRawtab, move it to the first position; otherwise the pivot is built from whatever is on sheet one (often a cover or summary tab). - Step 3Upload the .xlsx or .csv — Drop the file onto the tool. SheetJS parses it in your browser — nothing is sent to a server. CSV exports from a CRM or POS work directly without converting to a workbook first.
- Step 4Set Region as row, Product as column — Type
RegionintorowFieldandProduct(orSKU) intocolField. These must match the header text exactly, case included —Regionandregionare treated as different headers. - Step 5Set Revenue as the value with SUM — Type
RevenueintovalueFieldand leaveaggFnonsum. SUM ignores any non-numeric value cells — so strip currency symbols and thousands separators ($1,200won't parse past the$) from the Revenue column beforehand. - Step 6Download and sanity-check the totals — Open
pivot-table.xlsx. The bottomGrand Totalrow should equal your total booked revenue, and the right-hand Grand Total column gives each region's full-line total. Cross-check the grand total against your source sum before sharing.
Field mapping for a sales pivot
Map your transaction columns to the three required inputs. Swap these values to produce different sales views from the same file.
| Pivot input | Typical sales column | Alternative views |
|---|---|---|
rowField | Region | Rep, Territory, Account, Category |
colField | Product | Quarter, Channel, Month, Segment |
valueField | Revenue | Units, Margin, Discount, OrderValue |
aggFn | sum (revenue total) | average (avg order value), count (deal count), countdistinct (unique accounts) |
What SUM does to the sales grid
Behaviours specific to a SUM revenue pivot, grounded in the aggregation logic.
| Situation | Result in the pivot |
|---|---|
| Region+Product pair with several orders | Their Revenue values are summed into one cell |
| Region+Product pair with no orders | Cell shows 0 (dense grid) |
| Revenue cell that is blank | Skipped; doesn't affect the sum |
Revenue cell like $1,200 or 1,200 | parseFloat stops at the symbol/comma — may parse as 1 or NaN and be skipped; clean to plain numbers first |
Right-hand __Grand Total column | Present (SUM only) — each region's total across all products |
Bottom Grand Total row | Always present — each product's total across all regions |
Tier and access
The pivot run is Developer-gated; lower tiers are rejected regardless of file size.
| Tier | Pivot run | Excel file/row limit |
|---|---|---|
| Free | Rejected | 5 MB / 10,000 rows |
| Pro | Rejected | 50 MB / 100,000 rows |
| Pro-media | Rejected | 200 MB / 500,000 rows |
| Developer | Allowed | 500 MB / unlimited rows |
Cookbook
Realistic sales-export shapes and the exact option text to type. All revenue figures illustrative.
Region x Product revenue (SUM) with both totals
The standard management view. The Grand Total column appears because the aggregation is sum; products are sorted alphabetically across the top.
Input (sheet 1): Region,Product,Revenue EMEA,Widget,1200 EMEA,Gadget,800 APAC,Widget,500 EMEA,Widget,300 APAC,Gadget,400 Options: rowField=Region colField=Product valueField=Revenue aggFn=sum Output (pivot-table.xlsx): Region,Gadget,Widget,__Grand Total APAC,400,500,900 EMEA,800,1500,2300 Grand Total,1200,2000,3200
Deal count per region+product (COUNT)
Switch the aggregation to count to see how many orders, not how much revenue, fell into each cell. There is no Grand Total column under count.
Input: Region,Product,Revenue EMEA,Widget,1200 EMEA,Widget,300 APAC,Gadget,400 Options: rowField=Region colField=Product valueField=Revenue aggFn=count Output: Region,Gadget,Widget APAC,1,0 EMEA,0,2 Grand Total,1,2
Unique accounts per region+product (COUNT DISTINCT)
Point valueField at the account column and use countdistinct to see how many distinct customers bought each product per region — repeat orders from the same account count once.
Input: Region,Product,AccountId EMEA,Widget,A1 EMEA,Widget,A1 EMEA,Widget,A2 APAC,Gadget,A3 Options: rowField=Region colField=Product valueField=AccountId aggFn=countdistinct Output: Region,Gadget,Widget APAC,1,0 EMEA,0,2 Grand Total,1,2
Rep x Quarter from the same file
No re-export needed — just change the option text. Same transaction file, different lens: revenue per rep across quarters.
Input: Rep,Quarter,Region,Product,Revenue Ana,Q1,EMEA,Widget,1200 Ben,Q1,APAC,Gadget,400 Ana,Q2,EMEA,Widget,900 Options: rowField=Rep colField=Quarter valueField=Revenue aggFn=sum Output: Rep,Q1,Q2,__Grand Total Ana,1200,900,2100 Ben,400,0,400 Grand Total,1600,900,2500
Filtering to one region (do it in the source)
There is no in-tool filter. To report on EMEA only, remove non-EMEA rows from the source first, then pivot. The Grand Totals then reflect just the filtered scope.
Step 1 - filter source to Region=EMEA (delete other rows): Region,Product,Revenue EMEA,Widget,1200 EMEA,Gadget,800 EMEA,Widget,300 Step 2 - pivot: rowField=Region colField=Product valueField=Revenue aggFn=sum Output: Region,Gadget,Widget,__Grand Total EMEA,800,1500,2300 Grand Total,800,1500,2300
Edge cases and what actually happens
Revenue stored with currency symbols or thousands separators
By designSUM parses values with parseFloat, which stops at the first non-numeric character. $1,200 parses to NaN (and is skipped) and 1,200 parses to 1. If region totals look far too low, the Revenue column likely contains formatted text — strip $, ,, and % to plain numbers before uploading.
Region or Product header typed with wrong case
Invalid fieldHeader matching is case-sensitive. region won't match a Region header — the row dimension collapses to a single (blank) bucket and the body fills with zeros. Copy the exact header text, including capitalisation, from the first row of your file.
Transactions on a tab other than the first
Wrong sheetOnly the first sheet is read. A workbook with a summary cover tab in front of the raw data will be pivoted from the cover tab. Move the transaction sheet to position one before uploading.
Blank Region or Product cells
PreservedOrders missing a Region or Product are bucketed under (blank) rather than dropped, surfacing CRM data-entry gaps. Clean or filter them in the source if you want them excluded from the totals.
Empty region+product combination
ExpectedA region that never sold a given product shows 0 in that cell, not blank. The grid stays rectangular so it charts cleanly; the 0 is a fill value, not a recorded zero-revenue order.
Too many distinct products
Wide outputEvery distinct Product becomes a column. A catalogue of thousands of SKUs as colField produces an unwieldy workbook. Roll SKUs up to a Category column (or put the high-cardinality field on the rows) for a readable pivot.
Wanting both revenue and units in one pivot
Not supportedOnly one valueField per run. Generate a Revenue pivot and a Units pivot separately, then place them side by side or join them. There is no value-area stacking like Excel's pivot.
Account below Developer tier
RejectedThe run throws Pivot Table Generator requires Developer tier. on Free, Pro, and Pro-media. For cleaning or reshaping the sales export on a lower tier first, use a sibling tool, then pivot on Developer.
Multi-line or merged header in the export
Parse mismatchThe tool treats the literal first row as the header. CRM exports with a two-row banner header or merged title cell will produce odd field names; delete banner rows so the real headers sit in row one.
Frequently asked questions
How do I get a Region x Product revenue table?
Type Region into rowField, Product into colField, Revenue into valueField, and keep aggFn on sum. Download pivot-table.xlsx — each cell is the summed revenue for that region+product, with a Grand Total row and (because it's SUM) a Grand Total column.
What if a region-product combination has no sales?
It shows 0. Empty (row, column) combinations are filled with zero so the grid is fully rectangular and ready for charts or formulas. The 0 is a fill, not a recorded zero-revenue transaction.
Can I filter to specific regions or products?
There's no in-tool filter. Filter the source before uploading — delete rows for the regions or products you want to exclude — then pivot. The Grand Totals will then reflect only the filtered scope. For more complex routing, split the file first with the Conditional Splitter.
Why is my regional revenue total too low?
Almost always non-numeric Revenue cells. SUM uses parseFloat, which can't read $1,200 (skipped as NaN) or 1,200 (read as 1). Strip currency symbols and thousands separators so the column is plain numbers, then re-run.
Can I see revenue and units in the same pivot?
No — one value field per run. Make a Revenue pivot (valueField=Revenue) and a Units pivot (valueField=Units) separately, then place them side by side. You could also join the two outputs afterwards with the Sheet Joiner.
How do I get average order value instead of total revenue?
Set aggFn=average and point valueField at the order-value column. Each cell becomes the mean of the numeric values in that bucket. Note there is no Grand Total column for averages (a row-wise total of averages isn't meaningful) — only the per-cell means and a Grand Total row.
How do I count unique customers per product?
Set valueField to the account/customer ID column and aggFn=countdistinct. Each cell counts distinct IDs, so repeat orders from the same account count once. This is the unique-count Excel needs Power Pivot for. See the dedicated COUNT DISTINCT recipe.
Why are my products in a strange order?
Columns are sorted alphabetically, not by revenue or by your source order. Gadget comes before Widget regardless of which sold more. If you need a specific order, rename or prefix the products (e.g. 1-Widget, 2-Gadget) so the alphabetical sort matches your intended order.
Does it read every sheet in my sales workbook?
No, only the first sheet. Move your transaction data to the first tab before uploading. The output workbook has a single Pivot sheet.
Is my sales data sent anywhere?
No. SheetJS parses and aggregates entirely in your browser; the file never reaches a server. Only an anonymous usage counter is recorded. This keeps confidential pipeline and margin data on your machine.
What format and size of file can I upload?
Both .xlsx and .csv. On Developer tier the limit is 500 MB and unlimited rows. The tool is Developer-gated, so the lower tiers' smaller limits don't apply — the run simply won't proceed below Developer.
Do I need to install Excel to open the result?
No. pivot-table.xlsx opens in Excel, LibreOffice Calc, Google Sheets, or Numbers. You don't need Excel to generate or to open it — the whole point is producing a pivot without a desktop Office install.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.