Cap QUERY spill
=ARRAY_CONSTRAIN(QUERY(Data!A:D, "select *", 1), 50, 4) shows at most fifty rows of query output.
What is ARRAY_CONSTRAIN in Google Sheets?
ARRAY_CONSTRAIN wraps an array formula result and returns only the first num_rows and num_columns of that output. It prevents huge spills, fits charts to fixed ranges, and stabilizes ranges for IMPORTRANGE wrappers when you need predictable dimensions.
When to use it
Use ARRAY_CONSTRAIN when a QUERY or FILTER might spill more rows than your dashboard slot allows, or when a chart needs a fixed height block.
When to skip it
Skip ARRAY_CONSTRAIN when you want the full dynamic spill. Truncating data silently hides new rows from reports.
How it works
-
1
Wrap the array-producing formula as the first argument.
-
2
Set num_rows to the maximum rows to keep from the top of the spill.
-
3
Set num_columns to cap width similarly.
-
4
Pair with QUERY LIMIT when SQL can limit at source instead of truncating after.
-
5
Document that rows beyond the cap are hidden from view, not deleted at source.
-
6
Increase caps when legitimate growth exceeds the constrained size.
Examples in Google Sheets
Chart source block
=ARRAY_CONSTRAIN(SORT(A2:C), 12, 3) feeds twelve-month chart range even if source grows.
IMPORT trim
=ARRAY_CONSTRAIN(IMPORTRANGE(id, "Raw!A:Z"), 1000, 10) limits wide import while prototyping.
Build this without starting from a blank cell
Use a Better Sheets tool for ARRAY_CONSTRAIN, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Constraining so small that new data never appears in reports.
- Using ARRAY_CONSTRAIN instead of fixing QUERY LIMIT at the source.
- num_columns too narrow, cutting off critical fields without error.
- Wrapping scalar formulas unnecessarily.
- Forgetting constrained range still recalculates full inner formula cost.
Frequently asked questions
- ARRAY_CONSTRAIN vs QUERY LIMIT?
- QUERY LIMIT reduces work in SQL. ARRAY_CONSTRAIN trims any array output.
- Does it hide errors?
- Errors in visible constrained cells still show. Truncation is silent for extra rows.
- ARRAY_CONSTRAIN and charts?
- Fixed-size output helps chart ranges stay stable on dashboards.
- Too few rows symptom?
- New source rows exist but do not appear below the cap.
- ARRAY_CONSTRAIN with FILTER?
- Yes. FILTER then constrain to max dashboard height.
- Can cap be dynamic?
- Row and column counts can be cell references if they resolve to numbers.
- ARRAY_CONSTRAIN vs TAKE?
- TAKE is a newer function for first n rows or columns with similar goals.
Related Tutorials
Watch how ARRAY_CONSTRAIN works
Spreadsheet Automation 101 Lesson 2: Arrays
Related blog posts
Guides that explain ARRAY_CONSTRAIN in more depth.
How to Gain Confidence in Google Sheets
Boost your confidence, master formula usage and overcome errors in this step-by-step guide. Join us to demystify spreadsheets.
Read post →Protect Your Formulas from Accidental Deletion in Google Sheets
Ever spent hours crafting the perfect formula in Google Sheets, only to accidentally delete it? It's a frustrating experience that can derail your productivity. Fear not! In this guide, we'll unveil some clever tricks to safeguard your hard work.
Read post →How to create a formula for every row in a column in a Google Spreadsheet?
3 possible ways to create a formula for every row in a column in a Google Spreadsheet. And two bonus ways to make it look nicer. - Auto fill - Double Click - ArrayFormula
Read post →Related glossary terms
ARRAYFORMULA
ARRAYFORMULA tells Google Sheets to treat a formula as an array operation so one expression fills many cells at once. It is common with IF, VLOOKUP, and math on full columns before dynamic arrays made many patterns automatic. It still matters for legacy sheets and explicit control.
Read guide →QUERY
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →UNIQUE
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
Read guide →Done reading about ARRAY_CONSTRAIN?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.