A pivot table is a tool that summarises a list of data by grouping it according to the criteria you choose. Faced with five thousand rows of sales, with date, agent, area, product and amount, a pivot tells you in a few seconds how much each agent sold in each quarter, without writing a single formula. It exists in Excel, in LibreOffice Calc (where it used to be called DataPilot) and in Google Sheets, with different interfaces but the same logic.
Before the pivot: the right data
The quality of the pivot depends almost entirely on the structure of the source data. A few conditions are needed:
- one row per event (a sale, a transaction, an attendance);
- one column per attribute, with a unique, non-empty header in the first row;
- no total or subtotal rows in the middle of the data;
- no merged cells and no empty rows breaking up the list;
- consistent values: dates must be real dates, amounts real numbers, and the same agent must always be written the same way.
If your sheet has months laid out in side-by-side columns (January, February, March…), it is not ready for a pivot: it must first be turned into a list with a "Month" column and an "Amount" column.
A practical tip in Excel: turn the list into a table with Insert, Table. That way, when you add new rows, the pivot includes them automatically at the next refresh.
Creating the pivot
In Excel you select any cell in the data and choose Insert, PivotTable. In Google Sheets the command is in the same Insert menu; in LibreOffice Calc it is under Insert, Pivot Table. The program offers to create it in a new sheet: that is almost always the best choice, to keep data and summaries apart.
At this point a list of fields appears, that is the column headers, together with a few areas to drag them into.
The four areas
| Area | What it does | Example |
|---|---|---|
| Rows | creates a row for each distinct value | one row per agent |
| Columns | creates a column for each distinct value | one column per area |
| Values | calculates a numeric summary | sum of amounts |
| Filters | limits the data considered | a single year |
With "Agent" in rows, "Area" in columns and "Amount" in values you get a grid with what each agent sold in each area, with row and column totals already calculated.
Choosing the right calculation
In the Values area the default calculation is sum for numeric fields and count for text fields. You can change it in the field settings: average, minimum, maximum, count. Count is useful for questions like "how many orders did each agent handle?".
If a field that should be summed is counted instead, take it as a warning: the column probably contains empty cells or values stored as text. It is better to fix the source data than to force the calculation.
Showing percentages
Besides the absolute value, pivots let you show values relative to something. In Excel the option is called "Show Values As" and offers, among others, percentage of grand total, of row total or of column total. That way you immediately see what share of revenue comes from each area, with no extra formulas.
Grouping dates
A date column with one value per day would produce hundreds of rows. By selecting a date in the pivot and choosing Group you can aggregate them by months, quarters or years. Recent Excel versions often group automatically when you drag a date into rows. In Google Sheets you use the option to create a pivot date group from the context menu.
Grouping only works if every cell in the column is a valid date: a single date written as text is enough to make the option unavailable.
Refreshing and checking
A pivot does not update by itself when the source data changes, unlike formulas. In Excel and LibreOffice you have to use the Refresh command; Google Sheets, on the other hand, recalculates it automatically. Forgetting to refresh is the most common cause of summaries that do not add up.
To check a result in Excel, you can double-click a value in the pivot: a new sheet opens with all the rows that make it up. It is a valuable check when a number looks odd.
A complete example
With a list of annual sales:
- drag "Date" into rows and group by quarter;
- drag "Agent" into columns;
- drag "Amount" into values, as a sum;
- drag "Product" into filters and select a single line.
In under a minute you get the quarterly sales of each agent for that product line. Change the filter and the table recalculates instantly. This is the kind of analysis that with formulas would take dozens of SUMIFS.
In short
- A pivot summarises a list by grouping the data by rows, columns and filters.
- The source data must be a clean list: one row per event, unique headers, no subtotals.
- In the Values area you can choose sum, count, average and percentages of the total.
- Dates group by month, quarter or year if they are all valid dates.
- In Excel and LibreOffice remember to refresh the pivot when the data changes.
