Excel like a finance pro.
Pivot tables · Data & refresh
Pivots on external data
Builds a pivot straight from a database, another workbook, or a Power Query result.
When to use it
A pivot can read straight from a database, another workbook, or a Power Query result, without the data living on a sheet.
The shape of it
- How
Insert, PivotTable, From External Data Source, or load a query to the Data Model with Only Create Connection.
Worked examples
Database source
Insert, PivotTable, From External Data Source, Choose Connection → A pivot on a database table or query
Live data.
Query source
Power Query, Close and Load To, Only Create Connection, Add to Data Model, then Insert PivotTable from the model → A pivot on transformed data that never touches a sheet
The modern pattern.
Scheduled refresh
Data, Queries and Connections, Properties, Refresh every 30 minutes → Automatic refresh
Scheduled.
Worth knowing
- Connection-only queries keep files small.
- Credentials are stored per user; recipients may need to re-enter them.
- Refresh All updates the query then the pivot.
Where it goes wrong
- The source must be reachable on refresh.
- Sharing the file shares the connection string, not the access.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.