Formula Array Literal - Excel Glossary

Formula array literals represent a modern approach to array handling in Excel, particularly valuable in Excel 365 with dynamic array functionality. They enable you to define inline data structures—rows, columns, or matrices—directly within formulas without requiring helper columns or named ranges. This feature integrates seamlessly with functions like FILTER, SORT, and SEQUENCE, reducing formula verbosity and improving readability. Array literals enhance data manipulation workflows by allowing complex calculations to remain self-contained and easier to audit.

Definition

A formula array literal is a set of values enclosed in curly braces {} that Excel treats as a single array within a formula. Introduced in Excel 365, it allows you to create dynamic arrays without referencing a cell range, enabling cleaner, more flexible calculations and reducing formula complexity.

Key Points

1Array literals use curly braces {} to define inline data; rows are separated by semicolons (;) and columns by commas (,).
2They work natively with dynamic array functions and eliminate the need for external data ranges in many scenarios.
3Supported in Excel 365 and newer versions; not available in legacy Excel or earlier spreadsheet applications.

Practical Examples

→Creating a price list: {"Product","Price"; "Laptop",1200; "Mouse",25} generates a structured array for analysis.
→Dynamic filtering: FILTER(data, {TRUE; FALSE; TRUE}) selectively displays rows based on the array literal condition.

Detailed Examples

Sales performance dashboard

Use an array literal like {"Q1",5000; "Q2",7200; "Q3",6800} to define quarterly sales targets directly in a formula. This enables SORT or CHART functions to process the data without maintaining separate input cells, keeping dashboards cleaner and formulas transparent.

Multi-criteria filtering

Combine array literals with FILTER: FILTER(employees, (salaries>{60000; 70000; 80000})) to test multiple salary thresholds simultaneously. This advanced technique reduces formula complexity by embedding decision matrices inline.

Best Practices

✓Use array literals for static or frequently referenced data sets to improve formula clarity and reduce cell range dependencies.
✓Maintain consistent delimiter usage: semicolons for rows, commas for columns; mixing conventions causes errors.
✓Combine array literals with dynamic functions (FILTER, SORT, UNIQUE) for maximum flexibility and reduced formula footprint.

Common Mistakes

✕Confusing row and column delimiters: using commas instead of semicolons for row separation results in syntax errors. Always use semicolons between rows and commas between columns.
✕Attempting array literals in older Excel versions: this feature requires Excel 365; legacy versions lack support and will return #NAME? errors.

Tips

✓Test array literals in the formula bar with simple structures first; incrementally add complexity to avoid debugging large formulas.
✓Leverage array literals for lookup tables or reference matrices to replace VLOOKUP in many scenarios with cleaner syntax.

Related Excel Functions

FILTER SORT UNIQUE SEQUENCE XLOOKUP LET

Frequently Asked Questions

What Excel versions support formula array literals?

Excel 365 (Microsoft 365 subscription) supports formula array literals natively. Legacy Excel versions (2019 and earlier) do not support this feature and will return errors.

How do I separate rows and columns in an array literal?

Use semicolons (;) to separate rows and commas (,) to separate columns. For example, {1,2,3; 4,5,6} creates a 2-row, 3-column array.

Can I use array literals with text and numbers together?

Yes, array literals support mixed data types. You can combine text, numbers, and logical values: {"Name",100,TRUE; "Item",50,FALSE} works seamlessly in Excel 365.