Working with Large Datasets from SQL Server in Excel: Performance Tips

Filter at the Database, Not Excel

The most important rule: don't pull more data than you need.

Instead of importing all records and filtering in Excel, add WHERE clauses to your query:

SELECT * FROM Orders WHERE OrderDate >= '2025-01-01'

Database filtering is orders of magnitude faster than transferring and filtering in Excel.

Select Only Needed Columns

SELECT * pulls every column, including large text fields you might not need.

Specify exactly what you need:

SELECT OrderID, CustomerName, Total FROM Orders

Use Power Query's Query Folding

Power Query tries to push transformations back to the database (query folding). This means filtering and aggregations happen server-side.

To check if a step folds: right-click a step in Power Query → View Native Query. If visible, that step runs on the database.

Some operations break folding (like adding custom columns). Order your steps to keep folded operations first.

Load to Data Model Instead of Worksheet

Excel's Data Model (Power Pivot) handles large datasets better than worksheets:

When loading a query: choose “Only Create Connection” and check “Add to Data Model”

Data stays compressed in memory. Use PivotTables to analyze without putting every row on a sheet.

Aggregate Before Import

If you only need summaries, aggregate in the SQL query:

SELECT Region, SUM(Sales) as TotalSales FROM Orders GROUP BY Region

This returns 5 rows instead of 500,000.

Official Resources

• Create PivotTables — analyze large datasets
• Power Query overview — data transformation tool