Excel

Array Formula

A formula that performs multiple calculations on one or more sets of values and returns either a single result or multiple results, enabling complex operations in a single cell.

Examples

=SUM(A1:A10*B1:B10) for weighted totals
=FILTER(A:C, B:B>1000) for filtered results
=UNIQUE(A2:A100) for unique values
  • 1D Array (row): {1, 2, 3, 4, 5}
  • 1D Array (column): {1; 2; 3; 4; 5}
  • 2D Array: {1, 2, 3; 4, 5, 6}

    ## Types of Array Formulas

    ### Single-Result Array Formulas

    • Return one value from multiple calculations: 
    =SUM(A1:A10*B1:B10)
    Multiplies each A value by corresponding B value, then sums all products.

    ### Multi-Result Array Formulas (Dynamic Arrays) Return multiple values that "spill" into adjacent cells: 

    =UNIQUE(A2:A100)
=FILTER(A:C, B:B="North")
=SORT(A2:C100, 2, 1)

    ## Excel: Legacy vs Dynamic Arrays

    ### Legacy CSE Arrays (Excel 2019 and earlier)

  • Required Ctrl+Shift+Enter to confirm
  • Displayed with curly braces { }
  • Couldn't spill to multiple cells naturally

    ### Dynamic Arrays (Excel 365/2021)

  • Automatic spilling
  • No special entry required
  • New functions: FILTER, SORT, UNIQUE, SEQUENCE, RANDARRAY

    ## Google Sheets: ARRAYFORMULA

    In Sheets, wrap formulas with ARRAYFORMULA to process ranges:

    =ARRAYFORMULA(A2:A*B2:B)
    • =ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))

    ## Key Array Functions

    ### Excel Dynamic Arrays | Function | Purpose | |----------|---------| | FILTER | Return rows matching criteria | | SORT | Sort a range | | SORTBY | Sort by another range | | UNIQUE | Remove duplicates | | SEQUENCE | Generate number sequences | | RANDARRAY | Generate random arrays | | XLOOKUP | Modern lookup (array-aware) |

    ### Google Sheets | Function | Purpose | |----------|---------| | ARRAYFORMULA | Apply formula to range | | FILTER | Return matching rows | | SORT | Sort results | | UNIQUE | Remove duplicates | | QUERY | SQL-like data operations |

    ## Examples

    ### Sum of Products 

    =SUM(Quantity*Price)
// Or explicitly
=SUMPRODUCT(Quantity, Price)

    ### Count with Multiple Criteria 

    =SUM((A2:A100="North")*(B2:B100>1000))

    ### Filter and Sort 

    =SORT(FILTER(A2:D100, B2:B100="Active"), 3, -1)

    ### Create Multiplication Table 

    =SEQUENCE(10)*SEQUENCE(1,10)

    ## The Spill Range Operator (#)

    Reference all values in a spill range: 

    =UNIQUE(A2:A100)     // In cell C2, spills down
=SUM(C2#)            // Sum all spilled values

    ## Common Errors

    ### #SPILL! Spill range is blocked by existing data. Clear the cells.

    ### #CALC! Calculation error in the array formula. Check your logic.

    ### Wrong Size Result Formula returns different size than expected. Verify your ranges.

    ## Performance Tips

    1. Limit range sizes: Use A2:A1000 instead of A:A when possible 2. Avoid volatile functions: NOW(), TODAY() in arrays recalculate constantly 3. One array formula: Better than 1000 individual formulas 4. Use dynamic arrays: More efficient than legacy CSE

    ## Best Practices

    1. Understand spilling: Know where results will appear 2. Leave room: Don't put data where arrays might spill 3. Name your ranges: Makes array formulas more readable 4. Test with small data: Verify logic before applying to large datasets 5. Document complex formulas: Comments or a documentation sheet