What is an array literal in Google Sheets?
An array literal is an inline constant array written with curly braces, for example {1,2,3} across columns or {1;2;3} down rows. Semicolons separate rows and commas separate columns in US locale. Array literals feed functions expecting ranges without placing values on the grid first.
When to use it
Use array literals for small constant sets: weekday labels, scoring weights, boolean masks in one formula, or quick matrix math in advanced models.
When to skip it
Skip large literals in formulas when a Settings tab table is easier for non-technical editors to update without touching formula syntax.
How it works
-
1
Wrap constants in { } with comma for horizontal and semicolon for vertical stacking per your locale rules.
-
2
Combine literals with ranges like {A2:A5; B2:B5} to stack two columns into one array in some patterns.
-
3
MMULT, TRANSPOSE, and array-aware functions accept literal matrices for linear algebra shortcuts.
-
4
Wrong separator for locale causes
-
5
Spilled dynamic arrays reduced need for some literal tricks but literals remain useful for constants.
-
6
Document literal meaning in adjacent cell comment when magic numbers appear only inside braces.
Examples in Google Sheets
Weighted score
=MMULT({0.3,0.7}, {exam1; exam2}) combines literal weights with stacked scores in one expression.
Inline label vector
={"Q1","Q2","Q3","Q4"} headers output across columns from one formula for a small report row.
Boolean mask
Multiply range by {1,0,1,0} literal pattern where columns alternate inclusion in a calc.
Better Sheets resources
Common mistakes
-
Using comma instead of semicolon for rows in EU locale sheets and getting
-
Huge literals in one formula bar line that nobody can maintain next quarter.
-
Mismatched literal width vs range width in MMULT causing
-
Curly braces from Word documents pasted as wrong Unicode characters breaking parse.
-
Expecting literal to spill when wrapped in scalar-only function without array support.
Frequently asked questions
- Comma vs semicolon in arrays?
- In US locale comma separates columns, semicolon separates rows inside { }. Locale may invert.
- Array literal vs range?
- Literal lives in formula. Range references cells on the sheet editors can see and change.
- Array literal spill?
- Some formulas output literal arrays across cells; ensure spill path is clear.
- TEXTJOIN with literal?
- Combine literal headers with TEXTJOIN patterns for compact header rows.
- Array literal
- Check separators, uneven row lengths in matrix, and locale settings.
- ARRAYFORMULA with literal?
- Often unnecessary; inner function may already handle array or spill.
- Import array literal from Excel?
- Verify brace and separator conventions differ; test in scratch cell first.
- Literal vs named range table?
- Named range wins for maintainability when constants change monthly.
Related Tutorials
Watch how Array literal works
Spreadsheet Automation 101 Lesson 2: Arrays
Spreadsheet Automation 101 Lesson 2: Bracket Notation
Spreadsheet Automation 101: Parentheses
Related 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 →Locale separators
Locale controls whether function arguments separate with commas or semicolons and whether decimals use dot or comma. A sheet set to France may expect =SUM(A1;A10) while US expects =SUM(A1,A10). CSV imports and array literals also clash when decimal commas meet comma separators. File > Settings > Locale sets the default behavior for new formulas in that file.
Read guide →Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →#SPILL! error
#SPILL! means a dynamic array formula tried to fill multiple cells but something blocked the spill range. Another value, merged cells, or a table in the way stops the output. Clear the obstruction or move the formula to open space.
Read guide →Done reading about Array literal?
Membership unlocks 600+ 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.