Building a Pivot Table From a Database Query in Excel

A pivot table is the quickest way most people summarize data, and a database is where the data lives. Joining the two is a matter of a few dialogs, and it saves the usual round trip of exporting a CSV, pasting it and rebuilding the pivot next month. This post builds a pivot from a SQL query, explains the choice between a sheet and the Data Model, and lists the traps that show up on the second refresh. It is a spoke of pivot tables and their SQL equivalents.

Connect and write the query

On the Data tab choose Get Data, From Database, and pick your database: From SQL Server Database, From PostgreSQL Database, From MySQL Database and so on. The dialog asks for the server and database name. Open Advanced options and paste a SQL statement in the box. If you leave the box empty, Excel lists the tables and you pick one, which loads the whole table.

Write the query rather than loading tables. A query lets the database do the work it is good at:

SELECT o.order_date,
       c.region,
       p.category,
       o.quantity * o.unit_price AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p  ON p.id = o.product_id
WHERE o.order_date >= DATE '2025-01-01';

This returns one row per order line with the region and category already attached, which is the shape a pivot wants: one row per fact, descriptive columns beside it. Leave the summing to the pivot. If the query itself groups to one row per region and month, the pivot has nothing left to cross-tabulate, and you lose the ability to drill into a different slice without editing SQL.

Excel asks for credentials the first time: database login, Windows account or a connection string depending on the source. Use an account that can only read (giving Excel users a read-only database account).

Choose where the data goes

After the preview, click the arrow beside Load and choose Load To. There are three choices that matter:

• Table. Writes the rows to a sheet. Limited to 1,048,576 rows, and the workbook grows with the data.

• Only Create Connection. Stores the query without loading rows. The data exists only when something asks for it.

• Add this data to the Data Model. Loads into the in-memory columnar store that PivotTables can read. It is not limited by sheet rows and compresses well.

For a pivot, choose Only Create Connection and tick Add this data to the Data Model, or choose PivotTable Report directly if offered. The pivot then reads from the model. A sheet copy is useful only when you want the raw rows visible beside the pivot; it doubles the file size for nothing otherwise. Section making a pivot table from data larger than Excel allows covers the larger cases.

Insert the pivot

With the query in the Data Model, go to Insert, PivotTable, From Data Model. The field list shows the query as a table. Drag region to Rows, category to Columns and revenue to Values. The pivot shows a sum by default. Right-click a value, Summarize Values By, to change it to count, average, minimum or maximum.

The result for three regions and two categories looks like a small cross-tab, one number per cell, with totals on the right and bottom. Behind it is the database query; the numbers match what a GROUP BY region, category would return (writing the same Excel pivot table as a GROUP BY query).

Joins in SQL or in the model

Excel offers a second place to join: the Data Model lets you load orders, customers and products as separate tables and draw relationships between them. Both work. A guide to choosing:

• Join in SQL when the tables are few and the join is stable. The query is one readable statement, the pivot sees one table, and you can copy the SQL elsewhere.

• Join in the model when you want to keep dimension tables separate so several pivots share them, or when a pivot needs measures across tables. It also keeps a wide fact table from repeating the same customer text on every row.

Relationships in the model are one-to-many from a lookup column. If the lookup column has duplicates, the relationship cannot be created or the totals double. Check the keys first.

Refresh and parameters

Data, Refresh All re-runs the query and updates the pivot. Set the connection to refresh when the file opens if the people reading it expect current numbers (refreshing database data in Excel on open).

A hard-coded date in the SQL goes stale. Use a parameter, a cell in the workbook, so people can change the period without editing SQL (using parameters in a database query from Excel). The usual approach is a named table with one cell that Power Query reads and inserts into the statement. When a SQL string is built from text, quote the value properly; a typed date from a cell cannot carry injected SQL, but a free-text field can.

Traps on the second refresh

New rows past the pivot range. A pivot based on a sheet range does not see rows added below it. A pivot based on a query or a Table does, because the source is the query, not a range.

A renamed column. If the SQL changes revenue to net_revenue, the pivot field list loses the old one and the layout drops it. Rebuild the field and fix the layout, or keep stable aliases in the SQL (pivot table refresh errors and missing fields).

Number formats. A pivot can lose its formatting after a refresh unless Preserve cell formatting on update is ticked in PivotTable Options. Set the number format on the field, via Value Field Settings, Number Format, instead of formatting cells.

Slow refresh. A query that returns millions of rows takes minutes on every refresh. Filter in SQL, return only the columns the pivot uses, and index the filter columns in the database (slow Excel refresh from a database).

Shared files. A workbook sent to a colleague carries the connection but not the driver or the saved password. They need the same driver and read access, and Excel will prompt for credentials on first refresh.

A check against the database

Run the same grouping in SQL once and compare it with the pivot grand total. For the example, the database check is:

SELECT SUM(o.quantity * o.unit_price) AS revenue
FROM orders o
WHERE o.order_date >= DATE '2025-01-01';

The number should match the pivot's grand total to the cent. If it does not, the join dropped or multiplied rows: an inner join that excludes orders whose customer is missing, or a join on a column with duplicates. A pivot that disagrees with its source by a small amount is a reason to look at the joins, not to round.

When a pivot table on a database query is not the right fit

The report needs row level security. Anyone with the workbook has what the connection can read, and cached data inside the file. For restricted data use a service with proper permissions, not a shared file.

The data is too large for a laptop. A pivot over hundreds of millions of rows belongs in a BI tool or a SQL query that returns the summary.

The audience needs live numbers. A pivot is as fresh as its last refresh. A dashboard reading the database directly serves that need better.

The logic is complicated. Window functions, recursive queries and cohort logic are clearer in SQL, with the pivot only presenting the result.

Microsoft documents the steps on its page about importing data from a database and the PivotTable fields on its page for creating a PivotTable. To tidy a sample result as JSON before you check it, use the CSV to JSON converter.