Formula Description:
Array formulas let you perform multiple calculations on one or more sets of values (arrays) instead of working with single cells at a time.
They can:
Return a single result (e.g., sum of squares).
Return multiple results (e.g., applying a function to each element).
(Since Excel 365 and Excel 2021, dynamic arrays spill results automatically without pressing CTRL+SHIFT+ENTER)
Guidelines for Using Array Formulas
1. Spilled Results
> In modern Excel, formulas that return multiple results spill into adjacent cells automatically.
> Example: =SEQUENCE(5) fills 5 rows with numbers 1–5.
2. Don’t Overwrite Spill Ranges
> If something blocks the spill range, Excel shows #SPILL!.
3. Use Functions That Support Arrays
> Many built-in functions like SUM, AVERAGE, IF, INDEX, FILTER, and MMULT handle arrays.
4. Control with Functions like @ and #
> @ prevents spilling (legacy implicit intersection).
> # references the whole spilled range (e.g., =A1#).
5. Be Careful with Performance
> Large array formulas can slow Excel, especially in older versions.
Examples of Array Formulas
1. Sum of Squares
=SUM(A1:A5^2)
Squares each value in A1:A5 and sums the result.
2. Filter Values Greater Than 10
=FILTER(A1:A10, A1:A10>10)
Returns only the values > 10. (Dynamic array only).
3. Unique Values
=UNIQUE(A1:A20)
Extracts distinct values.
4. Row-Wise Multiplication (Two Ranges)
=SUM(A1:A5 * B1:B5)
Computes the dot product (multiply each pair, then sum).
5. Multiple Results with SEQUENCE
=SEQUENCE(3, 4, 10, 2)
Creates a 3×4 array starting at 10, incrementing by 2.
6. Matrix Multiplication
=MMULT(A1:B2, C1:D2)
Performs matrix multiplication of two ranges.
7. Conditional Sum (Without SUMIF)
=SUM((A1:A10>100) * B1:B10)
Adds values in B1:B10 only if the corresponding A is >100.
Other tips : In Excel 365/2021, you don’t need CTRL+SHIFT+ENTER for these; older versions do.
Download other template
Loan Interest and EMI Calculator in Excel
Useful Practical MIS Excel Formulas : Editable Spreadsheet
Automatic PO Result By Excel Check Box Formula
Age Calculator in Excel Template – Year, Month, Days, Hours, Minute, Week
Auto Fill Date Series in Excel
Using Calculation Operators in Excel Formulas: A Complete Guide with Examples
How to use indirect formula in excel
Using functions and nested functions in Excel formulas
 instead of working with single cells at a time.){kind=link}