When should I use Ctrl+Shift+Enter in Excel?

Use Ctrl+Shift+Enter (CSE) only when you are on Excel 2016 or earlier and need a formula to process multiple cells simultaneously — for example, summing values that meet multiple conditions, or multiplying two ranges and summing the results. In Excel 2019 and Microsoft 365, most CSE formulas can be replaced by cleaner dynamic array functions like FILTER, UNIQUE, or SUMPRODUCT without needing Ctrl+Shift+Enter.

How-ToAugust 2026 · 11 min read

Excel Array Formulas: Complete Guide with 8 Examples

Q: What is an array formula in Excel?

An array formula in Excel processes multiple values at once instead of a single value. A legacy array formula is entered with Ctrl+Shift+Enter and appears with curly braces {=FORMULA()}. Dynamic array formulas (Excel 2019 and Microsoft 365) are entered with just Enter and automatically spill results into neighbouring cells.

Q: What is the difference between array formulas and SUMPRODUCT?

SUMPRODUCT is a regular function that internally processes arrays — it does not need Ctrl+Shift+Enter. It is often used to sum products of conditions, the same result you would get from a CSE SUM formula. SUMPRODUCT is more compatible across Excel versions and slightly easier to read. Dynamic array functions like FILTER and UNIQUE go further, returning entire arrays of results rather than a single calculated value.

Array formulas let Excel process multiple values at once — doing in a single cell what would normally take a helper column or a loop. Since Excel 2019, dynamic arrays changed how this works. Here is everything you need to know about both.

Quick Answer

Legacy arrays use Ctrl+Shift+Enter and show curly braces: {=SUM(IF(...))}. Dynamic arrays (Excel 2019 and Microsoft 365) just press Enter and spill results automatically — functions like UNIQUE, FILTER, SORT, BYROW. If you are on Excel 2016 or older, you need CSE. On Microsoft 365, dynamic functions cover most use cases without CSE.

Legacy Array Formulas (CSE) vs Dynamic Arrays

Until Excel 2019, the only way to process multiple values in one formula was to use Ctrl+Shift+Enter (CSE). Excel would wrap the formula in curly braces to signal it was an array formula. These still work in all Excel versions, but they are harder to read and edit.

Starting with Excel 2019 and fully available in Microsoft 365, dynamic array functions were introduced: UNIQUE, FILTER, SORT, SORTBY, SEQUENCE, RANDARRAY, BYROW, BYCOL, MAKEARRAY, and MAP. These functions return an array of results directly and spill them automatically into neighbouring cells — no Ctrl+Shift+Enter needed.

For new workbooks on Microsoft 365, prefer dynamic functions. For compatibility with older versions, stick to SUMPRODUCT, COUNTIFS, SUMIFS, and CSE patterns.

How Ctrl+Shift+Enter Works

When you finish a formula with Ctrl+Shift+Enter instead of just Enter, Excel marks it as an array formula. Instead of evaluating the formula once, Excel evaluates it for each element of the array in the formula. The result can be a single value (if the formula reduces to one number) or it can fill multiple cells if entered across a selected range.

To edit a CSE formula: select the cell, press F2, make your changes, and press Ctrl+Shift+Enter again. Pressing just Enter will break the array entry and calculate incorrectly.

To delete a CSE formula that spans multiple cells: select the entire range, press Delete. You cannot delete just one cell of a multi-cell array formula.

8 Array Formula Examples

1. Count cells that meet multiple conditions (without COUNTIFS)

CSE formula (Ctrl+Shift+Enter)

{=SUM((A2:A100="East")*(B2:B100>1000))}

Modern equivalent

=SUMPRODUCT((A2:A100="East")*(B2:B100>1000))

The array formula multiplies two TRUE/FALSE arrays — each TRUE becomes 1, FALSE becomes 0. The product is 1 only when both conditions are true. SUM adds all the 1s to give a count. The SUMPRODUCT version does the same without Ctrl+Shift+Enter.

Note: In Excel 2019+, use COUNTIFS instead: =COUNTIFS(A2:A100,"East",B2:B100,">1000")

2. Sum values in a range that match a condition from another range

CSE formula (Ctrl+Shift+Enter)

{=SUM(IF(A2:A100="Q1",B2:B100,0))}

Modern equivalent

=SUMIF(A2:A100,"Q1",B2:B100)

The IF runs across each row of the range, returning the B value if A is Q1, or 0 otherwise. SUM then adds the result. This is the original way to do conditional summing before SUMIF existed.

Note: SUMIF is simpler and faster — use the array version only when you need multiple conditions that SUMIFS cannot handle.

3. Find the longest text string in a range

CSE formula (Ctrl+Shift+Enter)

{=MAX(LEN(A2:A100))}

Modern equivalent

=MAX(LEN(A2:A100))

LEN applied to a range normally returns only the first value. As an array formula, LEN calculates the length of every cell, then MAX picks the largest. In Excel 365, this works without Ctrl+Shift+Enter because of implicit intersection handling.

4. UNIQUE — extract a list with no duplicates (dynamic arrays)

Formula

=UNIQUE(A2:A100)

UNIQUE is a dynamic array function — no Ctrl+Shift+Enter required. It returns every distinct value from the range and spills the results automatically into as many rows as needed. If you add new values to the source, the result updates automatically.

Note: UNIQUE requires Excel 2019 or Microsoft 365. Use a PivotTable or remove-duplicates workflow on older versions.

5. FILTER — return rows that meet a condition

Formula

=FILTER(A2:C100, B2:B100>1000, "No results")

FILTER returns all rows where the condition is TRUE and spills them automatically. The third argument sets what to show when no rows match. Unlike VLOOKUP, FILTER can return multiple rows and multiple columns at once.

Note: FILTER requires Excel 2019 or Microsoft 365.

6. SORT — sort a range and spill the result

Formula

=SORT(A2:C100, 2, -1)

SORT returns a sorted copy of the data, spilled into the cells below the formula. Arguments: the range, which column to sort by (2 = second column), and sort order (1 = ascending, -1 = descending). The source data stays unchanged.

Note: SORT requires Excel 2019 or Microsoft 365.

7. BYROW — run a formula on each row independently

Formula

=BYROW(A2:C10, LAMBDA(row, MAX(row)))

BYROW applies a LAMBDA function to each row of a range and spills the results. This example returns the maximum value from each row. Without BYROW, you would need a helper column or a CSE formula to achieve the same across an entire range.

Note: BYROW requires Microsoft 365.

8. Two-way lookup with INDEX and MATCH arrays

CSE formula (Ctrl+Shift+Enter)

{=INDEX(B2:D10, MATCH(G1,A2:A10,0), MATCH(G2,B1:D1,0))}

Modern equivalent

=XLOOKUP(G1,A2:A10,XLOOKUP(G2,B1:D1,B2:D10))

The double-MATCH finds the row and column position of the intersection value. This is the original solution for two-way lookups — matching on both a row header and a column header to find a single cell. The nested XLOOKUP version is cleaner and works without array entry.

Common Array Formula Errors

Array formulas introduce a few error patterns that regular formulas don't:

#VALUE! — often caused by arrays of different sizes. If you multiply two ranges that have different row counts, Excel can't align them. Ensure both arrays are the same dimensions.
#SPILL! — a dynamic array result cannot spill because a cell in the output range is already occupied. Clear the cells in the spill range.
#CALC! — FILTER or another dynamic function found no results. Add a fallback value as the last argument: =FILTER(A1:B10, C1:C10="Yes", "No results found").
Curly braces visible in formula bar but formula is wrong — the formula was entered with Ctrl+Shift+Enter but calculates as if it weren't. This happens when you edit the formula and accidentally press just Enter. Re-enter with Ctrl+Shift+Enter.

For auditing array formulas across a large workbook — checking which cells are arrays, which have spill errors, and which have broken references — ExcelErrorFinder will catch all error cells in a single scan, including #SPILL! and #CALC! errors introduced by dynamic arrays.

Inheriting a workbook full of array formulas?

ExcelErrorFinder audits the whole workbook at once — flagging every error cell, broken reference, and hardcoded value across all sheets in one pass. No install needed.

Audit My Spreadsheet Free →

Frequently Asked Questions

What is an array formula in Excel?

An array formula processes multiple values at once. Legacy array formulas use Ctrl+Shift+Enter and appear with curly braces. Dynamic array formulas (Excel 2019+) enter with just Enter and spill results automatically.

When should I use Ctrl+Shift+Enter?

Only when you are on Excel 2016 or earlier and need to process multiple values in a single formula. On Excel 2019 or Microsoft 365, dynamic array functions like FILTER, UNIQUE, and SORT are cleaner alternatives that don't need Ctrl+Shift+Enter.

What is the difference between array formulas and SUMPRODUCT?

SUMPRODUCT internally handles arrays without Ctrl+Shift+Enter. It is often the right choice for conditional sums and counts compatible with older Excel. Dynamic functions like FILTER and UNIQUE go further — they return entire arrays of results rather than a single aggregated value.

Related Guides

Dynamic Arrays Guide

UNIQUE, FILTER, SORT, SEQUENCE, RANDARRAY — every new dynamic array function explained.

Read →

SUMPRODUCT Guide

The function that handles arrays without Ctrl+Shift+Enter — 8 real examples.

Read →

Common Excel Errors

Every error code explained — including #SPILL! and #CALC! from dynamic arrays.

Read →

Formula Auditing

Trace Precedents and Evaluate Formula — the built-in tools for debugging complex arrays.

Read →