A pivot table turns a flat table of transactions into an answer: totals by region, averages by product, counts by month. RixSheets includes a full pivot engine — you build pivots through the PivotDialog, arrange fields with drag and drop, and can attach pivot charts that visualize the same aggregation. Because it runs locally on top of the same Rust xlsx engine that reads and writes your files, pivots behave the way you expect and survive the trip back into .xlsx.
What a pivot does for you
Instead of writing a dozen SUMIFS formulas, you select the data once and describe the summary structurally:
- Rows and columns define the grouping — for example Region down the side, Product across the top.
- Values define the math — sum of Units, average of Price, count of Orders.
- Filters narrow the input — only 2026 rows, only closed deals.
Change the field arrangement and the whole summary re-flows instantly. That is the point of a pivot: exploration at the speed of thought, with no formulas to maintain.
Build your first pivot
- Give your source table a clean header row — Product, Region, Date, Units, Price — with no blank headers.
- Click any cell inside the data and open Insert, Pivot Table. The PivotDialog opens and picks up the surrounding range.
- In the field list, drag Region into Rows, Product into Columns and Units into Values.
- Click the value field to switch its summary from sum to count, average, minimum or maximum.
- Confirm the dialog. RixSheets places the pivot on the sheet, laid out and calculated.
- Drag more fields in and out until the shape of the summary matches the question you are asking.
Two habits make this smoother. First, name the source range in the Name Manager — a pivot pointed at a named range is easier to re-select later than one pointed at a remembered cell span. Second, keep the pivot on its own sheet. A summary sitting next to raw rows invites copy-paste shortcuts that break the moment the data refreshes.
Shape the summary to the question
The same source data answers very different questions depending on arrangement:
| Question | Rows | Columns | Values |
|---|---|---|---|
| Which region sells most? | Region | — | Sum of Units |
| Which product lags where? | Product | Region | Sum of Units |
| What is the average deal size? | Region | — | Average of Amount |
| How many orders per month? | Date grouped by month | — | Count of Orders |
| Which customers returned the most? | Customer | — | Count of Orders |
Arrangement is drag and drop — no formulas are written, none are maintained. When a manager asks "now by month?", you rearrange fields in seconds instead of rebuilding a report.
Add a pivot chart
Numbers answer one person at a time; charts answer a meeting. Select the pivot and insert a pivot chart: the chart plots the aggregated result — column, bar, line, combo or doughnut — rather than raw rows. When you rearrange fields or refresh the data, the chart follows the pivot. This pairing is the fastest route from "here is our raw export" to "here is the slide-ready picture".
Keep pivots honest
- Refresh after edits: the pivot is linked to its source, so appending rows or correcting values only shows up once you refresh.
- Watch the source range: if you add rows beyond the original range, extend the source in the PivotDialog so new records are included.
- Keep headers unique: duplicate or blank column names confuse field mapping.
- Clean data first: sort, deduplicate and validate the source before pivoting — see sorting, filtering and duplicate removal.
A fourth habit is worth naming: agree on what the numbers mean before arguing about them. A pivot counting rows reports orders; the same pivot summing Amount reports revenue; neither is wrong, and a one-line note above the table — "counts, not revenue" — prevents the most common misreading in meetings. Pivots are precise; the vocabulary around them usually is not.
Combine pivots with the rest of the toolkit
Pivots play well with the neighbouring features in RixSheets:
- Slicers and the timeline panel give pivots one-click filtering buttons and date-range controls, so colleagues can slice a report without touching the field list — see slicers and timelines.
- Subtotal handles the simpler case of grouped totals directly in the table when a full pivot is more than you need — see Subtotal and Consolidate.
- Conditional formatting can highlight the pivot's top values or flag below-target averages — see conditional formatting.
For source data that needs compound criteria before it is pivot-worthy — rows where region is North and the amount exceeds a threshold, or either of two urgent categories — Advanced Filter extracts exactly the matching set, and the pivot reads from the extract instead of the raw export.
Ask the AI about the result
Once a pivot exists, the AI chat panel becomes a reading aid: ask what stands out in the summary, request a two-sentence takeaway for a report, or ask whether the source range covers all twelve months. The assistant reads ranges, cells and formats through its workbook tools and answers from your actual data. AI pages are unlimited on every plan, so you can interrogate the pivot as long as you like.
What to explore next
- Slicers and timelines — add buttons and date controls to your pivot.
- Charts in RixSheets — column, bar, line, combo and doughnut options.
- Subtotal and Consolidate — grouped totals and multi-sheet merging.
- Conditional formatting — heat-map the pivot's value area.