Pivot Tables and Summaries

What a pivot table is

A pivot table is a spreadsheet tool that summarizes a list of records into a compact table: you choose which column becomes the row labels, which becomes the column labels, and which is added up, counted or averaged where they meet. The spreadsheet does the grouping and the arithmetic; you write no formulas. Rearranging the summary ("pivoting" it) is a matter of dragging a field to another place.

Every month Lucía Romero, a billing clerk at Kestrel Energy, builds part of the key performance indicator (KPI) pack for Margriet de Wit, the chief financial officer, with a pivot table: the amount billed per tariff per month. Her source is a list of invoices, one row per invoice, with the tariff already looked up for each contract (see Spreadsheet Formulas). In the sample data it has 37 invoices for June to September 2026. The first rows look like this:

In SQL terms a pivot table is a GROUP BY with the groups of one column spread out sideways, plus subtotals and a grand total. That comparison runs through this page, because SQL is also how you check a pivot table's numbers.

Fields and the four areas

A pivot table field is one column of the source data, offered by its header name in the pivot table's field list so that you can place it in one of four areas. The header name is all the pivot table knows about a column, which is one reason the source needs exactly one header row.

Excel calls the areas Filters, Columns, Rows and Values; LibreOffice Calc's layout dialog calls them Filter Fields, Column Fields, Row Fields and Data Fields; Google Sheets uses Rows, Columns, Values and Filters. More than one field can go into an area: tariff and then status in Rows gives a status breakdown inside each tariff, with a subtotal per tariff.

With tariff in Rows, period in Columns and the sum of amount_eur in Values, the pivot table Lucía sees looks like this. It is shown here as a table because pivot tables cannot be run on this site; the query below it produces exactly the same numbers.

The same layout in SQL: one filtered sum per month, and ROLLUP for the grand-total row (see Subtotals with ROLLUP and GROUPING SETS). The examples on this page run in DuckDB 1.5 on Kestrel's sample data.

SELECT CASE WHEN grouping(c.tariff) = 1 THEN 'Grand total' ELSE c.tariff END AS tariff,
       sum(i.amount_eur) FILTER (WHERE i.period = DATE '2026-06-01') AS "2026-06",
       sum(i.amount_eur) FILTER (WHERE i.period = DATE '2026-07-01') AS "2026-07",
       sum(i.amount_eur) FILTER (WHERE i.period = DATE '2026-08-01') AS "2026-08",
       sum(i.amount_eur) FILTER (WHERE i.period = DATE '2026-09-01') AS "2026-09",
       sum(i.amount_eur) AS total
FROM invoices i
JOIN contracts c USING (contract_id)
GROUP BY ROLLUP (c.tariff)
ORDER BY grouping(c.tariff), c.tariff;

┌─────────────┬───────────────┬───────────────┬───────────────┬───────────────┬───────────────┐
│   tariff    │    2026-06    │    2026-07    │    2026-08    │    2026-09    │     total     │
│   varchar   │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │
├─────────────┼───────────────┼───────────────┼───────────────┼───────────────┼───────────────┤
│ day_night   │          NULL │        537.10 │        536.15 │        636.60 │       1709.85 │
│ dynamic     │          NULL │        689.95 │        689.90 │        765.85 │       2145.70 │
│ fixed       │         92.40 │        347.85 │        348.60 │        372.50 │       1161.35 │
│ variable    │          NULL │        176.20 │        176.05 │        189.40 │        541.65 │
│ Grand total │         92.40 │       1751.10 │       1750.70 │       1964.35 │       5558.55 │
└─────────────┴───────────────┴───────────────┴───────────────┴───────────────┴───────────────┘

Two things stand out. June has a single invoice, because the sample holds only one contract's June bill: the jump to July is an artifact of the data, not growth. And the empty cells in the pivot table are NULL in SQL: there were no day/night invoices in June, which is not the same as invoices of €0 (more on that under blank or zero).

Summarizing values

A value field setting is the choice of how a field in the Values area is summarized (Sum, Count, Average, Max, Min and a few more) and how the result is shown (as a plain number, or as a percentage of a total, a difference from another item, a running total). In Excel it is in the Value Field Settings dialog, opened from the field's drop-down; in LibreOffice Calc you double-click the data field in the layout dialog; Google Sheets offers "Summarize by" and "Show as" on each value.

Excel proposes Sum for a numeric column but switches to Count when the column contains text or empty cells. A count of invoices where you expected euros is a quick sign that some amounts were stored as text (see Data Types and Data Quality). Count and Sum answer different questions, and both belong in Margriet's pack: of the 37 invoices, 10 are open (€1,132.85), 3 overdue (€184.80) and 24 paid (€4,240.90).

Averages and the grand total

Average is the summary that most often misleads. This query is what a pivot table with segment in Rows and amount_eur in Values three times (as Count, Sum and Average) computes, and it also shows why the segment has to come from a lookup that can fail:

SELECT CASE WHEN grouping(cu.segment) = 1 THEN 'Grand total'
            ELSE coalesce(cu.segment, '(blank)') END AS segment,
       count(*) AS invoices,
       sum(i.amount_eur) AS billed,
       round(avg(i.amount_eur), 2) AS avg_invoice
FROM invoices i
JOIN contracts c USING (contract_id)
LEFT JOIN customers cu USING (customer_id)
GROUP BY ROLLUP (cu.segment)
ORDER BY grouping(cu.segment), cu.segment NULLS LAST;

┌─────────────┬──────────┬───────────────┬─────────────┐
│   segment   │ invoices │    billed     │ avg_invoice │
│   varchar   │  int64   │ decimal(38,2) │   double    │
├─────────────┼──────────┼───────────────┼─────────────┤
│ business    │        9 │       3645.65 │      405.07 │
│ household   │       25 │       1706.05 │       68.24 │
│ (blank)     │        3 │        206.85 │       68.95 │
│ Grand total │       37 │       5558.55 │      150.23 │
└─────────────┴──────────┴───────────────┴─────────────┘

The pivot table's grand total for Average is right: €150.23 is the average of all 37 invoices. The mistake happens outside it, when someone copies the three segment averages and averages those: (405.07 + 68.24 + 68.95) / 3 = €180.75, because nine business invoices then weigh as much as 25 household invoices. Adding averages up is never meaningful. If a summary of averages is needed, compute it from the underlying rows, as the pivot table does, or from sums and counts.

The (blank) row is the label Excel gives records whose row field is empty. Here it holds the three invoices of contract CT-20014, whose customer C-10099 does not exist, so the segment lookup found nothing. An inner join would have dropped those rows without a trace; the pivot table at least shows them. Do not hide the row with a filter: it is a data-quality finding, and its €206.85 belongs in the grand total.

Calculated fields

A calculated field is a new field defined inside the pivot table by a formula on other fields, such as overdue amount divided by billed amount. In Excel it is under PivotTable Analyze ▸ Fields, Items & Sets ▸ Calculated Field. LibreOffice Calc's pivot table has no equivalent; there you add a helper column to the source instead.

The catch: Excel evaluates a calculated field on the sums of the fields in its formula, not row by row. For a ratio that is exactly right: "overdue ÷ billed" per month becomes sum(overdue) ÷ sum(billed), the correct percentage for every cell and total. For a product it is wrong. A calculated field "kWh × price" on daily readings multiplies the total kWh by the sum of the prices of all rows:

WITH price (tariff, eur_per_kwh) AS (
  VALUES ('fixed', 0.27), ('variable', 0.26), ('dynamic', 0.22)
)
SELECT c.tariff,
       count(*) AS readings,
       sum(r.kwh_import) AS kwh,
       round(sum(r.kwh_import * p.eur_per_kwh), 2) AS energy_eur,
       round(sum(r.kwh_import) * sum(p.eur_per_kwh), 2) AS sum_times_sum
FROM daily_readings r
JOIN meters m USING (meter_id)
JOIN contracts c USING (contract_id)
JOIN price p USING (tariff)
GROUP BY c.tariff
ORDER BY c.tariff;

┌──────────┬──────────┬───────────────┬───────────────┬───────────────┐
│  tariff  │ readings │      kwh      │  energy_eur   │ sum_times_sum │
│ varchar  │  int64   │ decimal(38,3) │ decimal(38,2) │ decimal(38,2) │
├──────────┼──────────┼───────────────┼───────────────┼───────────────┤
│ dynamic  │       90 │      2057.276 │        452.60 │      40734.06 │
│ fixed    │       89 │      1074.240 │        290.04 │      25813.99 │
│ variable │       91 │       597.561 │        155.37 │      14138.29 │
└──────────┴──────────┴───────────────┴───────────────┴───────────────┘

energy_eur multiplies each reading by its price and then adds up: €290.04 for the fixed tariff in September (the day/night tariff is left out because it has two rates). The calculated field would show €25,813.99, 89 times too much, because the fixed tariff has 89 readings and so a price sum of 89 × 0.27. Row-by-row arithmetic belongs in a helper column in the source, or in SQL. In Excel's Data Model the equivalent of a calculated field is a measure written in DAX, which is how BI tools define such numbers (see Measures).

Grouping dates

Date grouping combines the individual dates of a date field into larger periods, such as months, quarters or years, so that a pivot table shows one row or column per period instead of one per day. Recent versions of Excel group a date field automatically when you drag it into Rows or Columns; you change the grouping with right-click ▸ Group and undo it with Ungroup. In LibreOffice Calc you select a date in the pivot table and choose Data ▸ Group and Outline ▸ Group; Google Sheets offers "Create pivot date group" on the right-click menu.

Grouping only works when the column holds real dates. A column of text that looks like dates (01-09-2026 imported as text, see CSV, JSON and Parquet) cannot be grouped: the option is grayed out or Excel reports that it cannot group that selection. The fix is in the source, not in the pivot table.

The portable alternative to built-in grouping is a helper column in the source with the period you want, which works in every program and also in formulas. Margriet's quarterly view, from the monthly totals:

  | A          | B                       | C
1 | period     | billed                  | quarter
2 | 2026-06-01 | 92.40                   | =YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0)
3 | 2026-07-01 | 1751.10                 | =YEAR(A3)&"-Q"&ROUNDUP(MONTH(A3)/3,0)
4 | 2026-08-01 | 1750.70                 | =YEAR(A4)&"-Q"&ROUNDUP(MONTH(A4)/3,0)
5 | 2026-09-01 | 1964.35                 | =YEAR(A5)&"-Q"&ROUNDUP(MONTH(A5)/3,0)
7 | 2026-Q2    | =SUMIFS(B2:B5,C2:C5,A7) |
8 | 2026-Q3    | =SUMIFS(B2:B5,C2:C5,A8) |

  | A          | B       | C
1 | period     | billed  | quarter
2 | 2026-06-01 | 92.4    | 2026-Q2
3 | 2026-07-01 | 1751.1  | 2026-Q3
4 | 2026-08-01 | 1750.7  | 2026-Q3
5 | 2026-09-01 | 1964.35 | 2026-Q3
7 | 2026-Q2    | 92.4    |
8 | 2026-Q3    | 5466.15 |

The grid shows what LibreOffice Calc 25.2 displays after typing these cells: dates typed in ISO 8601 form stay in that form, and 92.40 is shown as 92.4 until you give the column a number format with two decimals. & joins text, and ROUNDUP(MONTH(date)/3,0) turns months 1–12 into quarters 1–4. SUMIFS is explained on Spreadsheet Formulas.

Percent of total

Percent of total shows each value as a share of a total instead of as an amount: of the grand total, of its row's total, or of its column's total. In Excel it is under Value Field Settings ▸ Show Values As (% of Grand Total, % of Column Total, % of Row Total); Calc and Google Sheets have the same choices under similar names. Which total is the denominator changes the meaning completely: "% of row total" in the month-by-tariff pivot says how a tariff's billing is spread over the months; "% of column total" says how each month is split over the tariffs.

The September split over tariffs, checked with formulas on the 12 September invoices:

   | A         | B      | C          | E         | F                                 | G
 1 | tariff    | status | amount_eur | tariff    | billed                            | share
 2 | fixed     | open   | 86.35      | day_night | =SUMIFS($C$2:$C$13,$A$2:$A$13,E2) | =ROUND(F2/$F$6,3)
 3 | day_night | open   | 88.10      | dynamic   | =SUMIFS($C$2:$C$13,$A$2:$A$13,E3) | =ROUND(F3/$F$6,3)
 4 | dynamic   | open   | 66.95      | fixed     | =SUMIFS($C$2:$C$13,$A$2:$A$13,E4) | =ROUND(F4/$F$6,3)
 5 | variable  | open   | 62.30      | variable  | =SUMIFS($C$2:$C$13,$A$2:$A$13,E5) | =ROUND(F5/$F$6,3)
 6 | dynamic   | paid   | 640.10     | Total     | =SUM(F2:F5)                       | =ROUND(F6/$F$6,3)
 7 | day_night | open   | 95.60
 8 | fixed     | paid   | 191.40
 9 | variable  | open   | 55.20
10 | fixed     | open   | 94.75
11 | day_night | open   | 452.90
12 | dynamic   | open   | 58.80
13 | variable  | open   | 71.90
14 |           |        | =SUM(C2:C13)

   | A         | B      | C          | E         | F       | G
 1 | tariff    | status | amount_eur | tariff    | billed  | share
 2 | fixed     | open   | 86.35      | day_night | 636.6   | 0.324
 3 | day_night | open   | 88.1       | dynamic   | 765.85  | 0.39
 4 | dynamic   | open   | 66.95      | fixed     | 372.5   | 0.19
 5 | variable  | open   | 62.3       | variable  | 189.4   | 0.096
 6 | dynamic   | paid   | 640.1      | Total     | 1964.35 | 1
 7 | day_night | open   | 95.6       |           |         |
 8 | fixed     | paid   | 191.4      |           |         |
 9 | variable  | open   | 55.2       |           |         |
10 | fixed     | open   | 94.75      |           |         |
11 | day_night | open   | 452.9      |           |         |
12 | dynamic   | open   | 58.8       |           |         |
13 | variable  | open   | 71.9       |           |         |
14 |           |        | 1964.35    |           |         |

Format column G as a percentage to see 32.4%, 39.0%, 19.0% and 9.6%. Two checks are built in: the shares add up to 1, and the total of the summary (F6) equals the total of the source (C14). The dollar signs keep the ranges fixed when the formula is filled down (absolute references).

Percentages of a filtered total

A filter changes the denominator. Put status in Filters and choose "open", and "% of grand total" now means "% of the open amount". Nothing on the pivot table says so except the small filter cell above it. The same September invoices, both ways:

SELECT c.tariff,
       sum(i.amount_eur) AS billed,
       round(100 * sum(i.amount_eur) / sum(sum(i.amount_eur)) OVER (), 1) AS pct_of_all,
       sum(i.amount_eur) FILTER (WHERE i.status = 'open') AS open_eur,
       round(100 * sum(i.amount_eur) FILTER (WHERE i.status = 'open')
             / sum(sum(i.amount_eur) FILTER (WHERE i.status = 'open')) OVER (), 1) AS pct_of_open
FROM invoices i
JOIN contracts c USING (contract_id)
WHERE i.period = DATE '2026-09-01'
GROUP BY c.tariff
ORDER BY c.tariff;

┌───────────┬───────────────┬────────────┬───────────────┬─────────────┐
│  tariff   │    billed     │ pct_of_all │   open_eur    │ pct_of_open │
│  varchar  │ decimal(38,2) │   double   │ decimal(38,2) │   double    │
├───────────┼───────────────┼────────────┼───────────────┼─────────────┤
│ day_night │        636.60 │       32.4 │        636.60 │        56.2 │
│ dynamic   │        765.85 │       39.0 │        125.75 │        11.1 │
│ fixed     │        372.50 │       19.0 │        181.10 │        16.0 │
│ variable  │        189.40 │        9.6 │        189.40 │        16.7 │
└───────────┴───────────────┴────────────┴───────────────┴─────────────┘

Day/night is 32.4% of everything billed in September but 56.2% of what is still open, because the two business customers on other tariffs pay by direct debit and were already paid. Both numbers are correct; a slide that says "day/night: 56%" without "of open invoices" is not. sum(…) OVER () is a window function over all result rows: the SQL way to write "% of grand total".

Filters and slicers

A field in the Filters area restricts which records the whole pivot table summarizes; the drop-down buttons on the row and column labels hide individual items. A slicer is a visual filter control: a panel of buttons, one per value of a field (paid, open, overdue), that filters one or more pivot tables or charts with a click and shows at a glance which values are selected. Excel offers slicers for pivot tables and tables (PivotTable Analyze ▸ Insert Slicer), plus a timeline, a slicer for date ranges; Google Sheets has Data ▸ Add a slicer. In LibreOffice Calc you use the pivot table's filter fields and drop-down buttons.

A slicer connected to several pivot tables keeps them in step, which makes a small dashboard possible inside a workbook. That is also where the risk lies: whoever reads a printed or pasted copy cannot see which buttons were pressed. Write the active filter into the title of anything you hand on ("Open invoices, September 2026"), the same rule as stating "data as of" on a dashboard.

Refresh, blanks and hidden copies

A pivot table is a snapshot

In Excel and LibreOffice Calc a pivot table does not recalculate when its source changes. Excel keeps its own copy of the source data (the pivot cache) and summarizes that; the copy is updated only when you refresh: PivotTable Analyze ▸ Refresh, or Data ▸ Refresh All for every pivot table and connection in the workbook. In Calc it is Data ▸ Pivot Table ▸ Refresh, or Refresh in the pivot table's context menu. Excel can also refresh when the file is opened (PivotTable Options ▸ Data). Google Sheets pivot tables update automatically.

Two refresh problems account for most wrong pivot numbers:

• Forgetting to refresh. Lucía pastes the new month's invoices into the source sheet, and the pivot table still shows last month. Nothing looks broken.
• A fixed source range. A pivot table built on A1:F37 keeps reading 37 rows when the source grows to 49. Refresh then gives a wrong total without complaint. Base the pivot table on a spreadsheet table (Excel's Format as Table) or a named range that grows with the data, as described on Spreadsheets Done Right.

A control total catches both: next to the pivot table, a plain =SUM() over the source column, and a cell that compares it with the pivot table's grand total.

Blank or zero

Where no records exist for a combination, a spreadsheet pivot table leaves the cell empty, DuckDB's PIVOT and a filtered sum return NULL, and pandas returns NaN unless you pass fill_value=0 (see Reshaping in pandas). Excel can show something else in empty cells (PivotTable Options ▸ Layout & Format ▸ "For empty cells show"), and it is tempting to type 0 there. Think first: "no day/night invoices in June" is not "day/night invoices worth €0 in June". An empty cell also stays out of Count and Average, where a 0 would pull an average down. Use 0 only where zero is the true value, and say in a note what an empty cell means. The general rules for missing values are on Data Types and Data Quality.

GROUPBY and PIVOTBY formulas

The GROUPBY function is an Excel formula that groups rows by one or more fields and aggregates a value per group, returning the result as a dynamic array with a total row: a pivot table written as a formula. Its companion PIVOTBY adds column fields. Unlike a pivot table, both recalculate immediately when the source changes, so there is nothing to refresh.

   | A         | B          | D
 1 | tariff    | amount_eur | =GROUPBY(A2:A13,B2:B13,SUM)
 2 | fixed     | 86.35      |
 3 | day_night | 88.10      |
   … (the 12 September invoices, as in the grid above)

   | D         | E
 1 | day_night | 636.6
 2 | dynamic   | 765.85
 3 | fixed     | 372.5
 4 | variable  | 189.4
 5 | Total     | 1964.35

The third argument is a function name such as SUM, COUNTA or AVERAGE. PIVOTBY takes row fields, column fields, values and a function: =PIVOTBY(tariff_range,month_range,amount_range,SUM) would rebuild the month-by-tariff table above.

The portable alternative is a list of distinct keys plus SUMIFS. UNIQUE and SORT work in Excel 2021 and later, Google Sheets and LibreOffice Calc 24.8 and later. Excel spills the list into the cells below by itself; in LibreOffice 25.2 you select D2:D5 and enter the formula with Ctrl+Shift+Enter as an array formula.

   | A         | B          | D                     | E
 1 | tariff    | amount_eur | tariff                | billed
 2 | fixed     | 86.35      | =SORT(UNIQUE(A2:A13)) | =SUMIFS(B$2:B$13,A$2:A$13,D2)
 3 | day_night | 88.10      |                       | =SUMIFS(B$2:B$13,A$2:A$13,D3)
 4 | dynamic   | 66.95      |                       | =SUMIFS(B$2:B$13,A$2:A$13,D4)
 5 | variable  | 62.30      |                       | =SUMIFS(B$2:B$13,A$2:A$13,D5)
 6 | dynamic   | 640.10     | Total                 | =SUM(E2:E5)
 7 | day_night | 95.60
 8 | fixed     | 191.40
 9 | variable  | 55.20
10 | fixed     | 94.75
11 | day_night | 452.90
12 | dynamic   | 58.80
13 | variable  | 71.90

   | A         | B          | D         | E
 1 | tariff    | amount_eur | tariff    | billed
 2 | fixed     | 86.35      | day_night | 636.6
 3 | day_night | 88.1       | dynamic   | 765.85
 4 | dynamic   | 66.95      | fixed     | 372.5
 5 | variable  | 62.3       | variable  | 189.4
 6 | dynamic   | 640.1      | Total     | 1964.35
 7 | day_night | 95.6       |           |
 8 | fixed     | 191.4      |           |
 9 | variable  | 55.2       |           |
10 | fixed     | 94.75      |           |
11 | day_night | 452.9      |           |
12 | dynamic   | 58.8       |           |
13 | variable  | 71.9       |           |

The result matches the GROUPBY output and the pivot table's September column. Unlike GROUPBY, the SUMIFS column does not grow by itself when a fifth tariff appears: the fixed four rows are the weak spot to check.

When SQL is the better tool

A pivot table is the fastest way to explore a list you already have open. It is the wrong tool when:

• The summary must be repeated. A monthly pack rebuilt by hand is twelve chances a year to forget a refresh or drag a field to the wrong place. A query gives the same answer every time it runs and can be scheduled (see Data Pipelines).
• Several tables must be joined first. Lucía looks up the tariff and the segment with formulas before she can pivot; in SQL the joins are part of the query, and a left join shows unmatched rows on purpose instead of by accident.
• The data does not fit. A worksheet holds 1,048,576 rows; Kestrel's 15-minute meter readings are 38.4 million rows a day (see When a Spreadsheet Is Not Enough).
• Someone must be able to check it. A query can be read, reviewed and versioned; a pivot table's layout lives in dialog boxes.

DuckDB has a PIVOT statement that reads almost like the pivot table's field list: the rows of the source, the field whose values become columns (ON), the summary (USING) and the row field (GROUP BY):

PIVOT (
  SELECT c.tariff, strftime(i.period, '%Y-%m') AS month, i.amount_eur
  FROM invoices i
  JOIN contracts c USING (contract_id)
)
ON month
USING sum(amount_eur)
GROUP BY tariff
ORDER BY tariff;

┌───────────┬───────────────┬───────────────┬───────────────┬───────────────┐
│  tariff   │    2026-06    │    2026-07    │    2026-08    │    2026-09    │
│  varchar  │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │
├───────────┼───────────────┼───────────────┼───────────────┼───────────────┤
│ day_night │          NULL │        537.10 │        536.15 │        636.60 │
│ dynamic   │          NULL │        689.95 │        689.90 │        765.85 │
│ fixed     │         92.40 │        347.85 │        348.60 │        372.50 │
│ variable  │          NULL │        176.20 │        176.05 │        189.40 │
└───────────┴───────────────┴───────────────┴───────────────┴───────────────┘

This PIVOT syntax is DuckDB's own (SQL Server, Oracle and Snowflake have a PIVOT with a different syntax that needs the column values listed; PostgreSQL has none), and it creates one column per distinct value it finds, so next month's query gets a column more without changes. It leaves missing combinations as NULL and adds no totals; for those, use the FILTER and ROLLUP form at the top of this page, which also runs on PostgreSQL. In pandas the same summary is pivot_table (see Data Analysis with Python).