From legacy Ctrl+Shift+Enter to modern dynamic arrays — understanding how array formulas work makes you a significantly better Excel user.
An array formula operates on multiple values simultaneously rather than one cell at a time. Instead of calculating one result, it processes an entire range and can return multiple results or a single summarised result.
Before Excel 365, array formulas required Ctrl+Shift+Enter to confirm. Excel wrapped them in curly braces {} to indicate array entry.
-- Sum of top 3 values (legacy):
{=SUM(LARGE(A2:A100,{1,2,3}))}
-- Enter with Ctrl+Shift+Enter, curly braces appear automatically
-- Count cells meeting two conditions (before COUNTIFS):
{=SUM((A2:A100="North")*(B2:B100>1000))}
-- Ctrl+Shift+Enter required in Excel 2016 and earlier-- Same formulas, just press Enter:
=SUM(LARGE(A2:A100,{1,2,3}))
=SUM((A2:A100="North")*(B2:B100>1000))
-- Plus the new dynamic array functions:
=FILTER(A2:C100, B2:B100="North")
=UNIQUE(A2:A100)
=SORT(A2:A100, 1, -1)
=SEQUENCE(10)=SUM(LARGE(A2:A100, {1,2,3}))
-- {1,2,3} is an array constant — three values inline
-- LARGE returns the 1st, 2nd, and 3rd largest
-- SUM adds them together
=SUMPRODUCT(A2:A10 * {1,2,3,4,5,6,7,8,9})
-- Multiply each value by its row number
-- Array constant must match the range size-- Sum of top N values:
=SUM(LARGE(A2:A100, SEQUENCE(5))) Excel 365
{=SUM(LARGE(A2:A100,{1,2,3,4,5}))} Legacy
-- Count unique values:
=COUNTA(UNIQUE(A2:A100))
-- Concatenate with condition:
=TEXTJOIN(", ", TRUE, IF(B2:B100="North", A2:A100, ""))
-- Join all names where region = North
-- Sum every other row:
=SUMPRODUCT((MOD(ROW(A2:A20)-ROW(A2)+1,2)=1)*A2:A20)
-- Sum rows 1,3,5,7... (odd rows in the range)In Excel 365, when a formula spills results into multiple cells, use # to reference the entire spill range: if UNIQUE is in A2 and spills to A8, =A2# references all of A2:A8. The reference automatically adjusts as the spill range grows or shrinks.
Practice these formulas in the Data Analyst track — 100 exercises covering dynamic arrays, data cleaning, and analysis. Free to start.
Start the Data Analyst track free →