Pivot a regional sales CSV and reconcile its totals
Admin ·
A pivot table can answer several questions from the same export. Use a sum to reconcile decimal amounts, then switch to averages or counts to see how blank records affect the result.
Reconcile amounts by region and quarter
Load the example. It selects region for rows, quarter for columns and amount for values. Build the table with Sum. North Q1 adds 9007199254740993.10 and 0.20 to give the exact result 9007199254740993.3. Trailing decimal zeros are optional in this output format.
Check that North and South are in the order they first appear, with Q1 before Q2. One South Q2 amount is blank. The numeric sum skips that blank and keeps the other amount, 5.125.
region,Q1,Q2
North,9007199254740993.3,2.345
South,-1.005,5.125
Compare averages and record counts
Change the aggregation to Average and keep two decimal places. Build the table again. North Q1 is 4503599627370496.65 and North Q2 rounds to 2.35. South Q1 rounds from -1.005 to -1.01. Halfway values round away from zero after the group’s exact sum is divided by its numeric count.
Change to Count records. North Q1 and South Q2 each show 2. The South Q2 count includes the blank amount, while its average uses only the numeric value.
region,Q1,Q2
North,4503599627370496.65,2.35
South,-1.01,5.13
Check group labels and the exported table
If the export seems to split one region into two rows, compare the group cells as exact text. Case, spaces and leading zeros affect membership. A blank label can be a group too.
Download the complete CSV and reopen it with commas as the separator. Check unexpected empty numeric cells against the source records. Correct a nonblank invalid number at its reported record and field before rebuilding.
Common questions
Can I make a one-dimensional pivot?
Choose Single series for the column field to produce one value column and one row per distinct row-field value.
How does the tool handle numbers with decimal exponents?
It interprets decimal scientific notation exactly when the resulting value fits the supported integer and fractional places. Use a period as the decimal separator.