The Query Builder writes up to 50,000 rows per query, and custom functions return up to 5,000. A new query starts with a 1,000-row limit, which you can raise up to the maximum.
Pull limits
Where | Default | Maximum |
Query Builder, row-level | 1,000 rows | 50,000 rows |
Query Builder, grouped | 1,000 groups | 10,000 groups |
Custom functions, row-level | 1,000 rows, or a saved query's own limit | 5,000 rows |
Custom functions, grouped | 5,000 groups | 5,000 groups |
These limits apply to every data type. A formula that reaches its default limit stops there without an error, so add limit=5000 to an =MS.QUERY formula when you expect more rows. Your plan's order limit doesn't change them.
When the Query Builder cuts a pull short
If your query has more rows than your limit, the table still lands in your sheet, and the pane shows how much it wrote, such as "Showing the first 1,000 of 4,212 rows." Two buttons help:
Raise limit: Sets the limit to the full result, up to 50,000. Run the query again to write every row.
Group rows: Turns on Group and total so you get a summary in place of every row.
When a formula hits its cap
A formula that would return more than 5,000 rows shows #VALUE! with the message "5,000-row cap: filter or aggregate." Select the cell to see the details in the pane. To fix it, you can:
Add a filter, such as a shorter date range
Group and total the rows
Run the same query in the Query Builder, which pulls up to 50,000 rows
Getting more than 50,000 rows
Split the pull into smaller queries that each stay under the limit. For example, pull one quarter of order lines per query, or filter by Shop to pull one store at a time. Place each table on its own sheet or with space between them.
Too many groups
A grouped query can return up to 10,000 groups in the Query Builder and 5,000 in a formula. Past that, the pane says how many groups your query produced. Narrow your filters, remove a group, or use a coarser date bucket, such as Month instead of Day.
Choosing the right size
Pulling every row works when you need to filter, look up, or pivot in Excel. When you only need totals, a grouped query returns far fewer rows, so it runs faster and keeps your workbook small. Group and total your data shows how.