Back to Blog
Tutorial

Excel Array Formulas: Modern vs Legacy

March 10, 2026 • 5 min read • Excel, LibreOffice Calc, Google Sheets

Excel Array Formulas: Modern vs. Legacy - A Deep Dive for ExcelFormula Pro Users

Welcome back to the ExcelFormula Pro blog! Today, we're diving into a topic that can sometimes feel a bit intimidating but is incredibly powerful once you grasp it: Excel Array Formulas. If you've ever wanted to perform calculations on multiple items in a range simultaneously, or return multiple results from a single formula, you're in the right place. We'll explore the evolution of array formulas, from the traditional "legacy" methods to the more modern, dynamic array approach, and show you how they work with practical examples.

Whether you're using Microsoft Excel, LibreOffice Calc, or Google Sheets, understanding array formulas will significantly boost your data analysis capabilities. At ExcelFormula Pro, our AI is designed to help you generate these complex formulas with ease, but knowing the underlying concepts will make you an even more formidable Excel user!

What Exactly Are Array Formulas?

At their core, array formulas allow you to work with multiple data points at once. Instead of a formula that operates on a single cell, an array formula can operate on a range of cells, an array of values, or even return an array of results.

Think of it this way:

  • Traditional formulas: Like a single worker performing one task at a time.
  • Array formulas: Like a team of workers, all tackling different parts of a job simultaneously, or a single worker who can perform a complex, multi-step operation in one go.

Historically, there's been a distinction between how array formulas were entered and how they behave. Let's break down the "legacy" versus the "modern" approach.

Legacy Array Formulas: The Ctrl+Shift+Enter Era

For many years, if you wanted to enter an array formula in Excel, you had to press a special key combination: Ctrl + Shift + Enter. This was the signal to Excel that you were entering an array formula, and it would automatically wrap your formula in curly braces `{}`.

Key characteristics of legacy array formulas:

  • Manual Entry: Required the explicit Ctrl+Shift+Enter keystroke.
  • Fixed Size: The output range of the array formula had to be pre-selected before entering the formula.
  • Overwriting: If you tried to enter a legacy array formula into a single cell, it would only return the first element of the resulting array, ignoring the rest.
  • Braces: You could never type the curly braces yourself; Excel added them.

Legacy Array Formula Example: Sum of Products

Let's say you have two columns of numbers, Column A (Quantity) and Column B (Price), and you want to calculate the total value (Quantity * Price) for each row and then sum all those individual values.

Here's how you'd do it with a legacy array formula:

Scenario:

  • Column A: A1:A5 (Quantities)
  • Column B: B1:B5 (Prices)
  • Desired Output: A single cell showing the total sum of (Quantity * Price).

The Formula:

=SUM(A1:A5*B1:B5)

How to enter it (Legacy):

  1. Select the cell where you want the total sum to appear.
  2. Type the formula: =SUM(A1:A5*B1:B5)
  3. Instead of just pressing Enter, press Ctrl + Shift + Enter.

Excel will then display the formula in the formula bar as:

{=SUM(A1:A5*B1:B5)}

What's happening:

  1. The part A1:A5*B1:B5 tells Excel to multiply each element in the range A1:A5 by the corresponding element in B1:B5. This creates an *array* of results (e.g., {10, 20, 30, 40, 50}).
  2. The SUM function then adds up all the elements in that resulting array.

This was the standard way to perform such calculations for years. It worked, but it required a specific, sometimes easily forgotten, entry method.

Modern Dynamic Array Formulas: The Game Changer

Microsoft introduced Dynamic Arrays starting with Excel for Microsoft 365. This feature revolutionized how we work with arrays. The key difference? Formulas that return multiple results (arrays) now "spill" into adjacent cells automatically, without requiring Ctrl+Shift+Enter.

Key characteristics of modern dynamic array formulas:

  • Automatic Entry: Simply press Enter.
  • Spilling: If a formula produces an array of results, it automatically fills the necessary adjacent cells.
  • Spill Range: Excel highlights the "spill range" (the cells filled by the dynamic array) and shows a blue border.
  • Error Handling: If the spill range is blocked by existing data, you'll get a `#SPILL!` error.
  • New Functions: Many new functions were introduced that leverage dynamic arrays (e.g., `FILTER`, `SORT`, `UNIQUE`, `SEQUENCE`, `RANDARRAY`).
  • Compatibility: While originating in Excel 365, dynamic array behavior is also standard in recent versions of LibreOffice Calc and is how Google Sheets has always handled array-like operations.

Modern Dynamic Array Formula Example: Sum of Products (Again!)

Let's revisit our Sum of Products example. With dynamic arrays, the formula itself often becomes simpler, and the *way* you enter it is just like any other formula.

Scenario: Same as before.

The Formula:

=SUM(A1:A5*B1:B5)

How to enter it (Modern):

  1. Select the cell where you want the total sum to appear.
  2. Type the formula: =SUM(A1:A5*B1:B5)
  3. Press Enter.

Excel will display the formula as =SUM(A1:A5*B1:B5). No curly braces needed from you!

What if we wanted to see the individual products per row?

Let's say you want to display the product of Quantity and Price for each row in a new column, say Column C.

The Formula (to be entered in C1):

=A1:A5*B1:B5

How to enter it:

  1. Select cell C1.
  2. Type the formula: =A1:A5*B1:B5
  3. Press Enter.

Excel will automatically "spill" the results down into cells C1, C2, C3, C4, and C5, showing the product for each corresponding row. This is the magic of dynamic arrays!

If you tried to enter this same formula =A1:A5*B1:B5 using the legacy Ctrl+Shift+Enter method in a single cell (say, C1), it would only show the result for the first row (A1*B1) and ignore the rest. The dynamic array approach makes it intuitive to return multiple results.

Powerful New Dynamic Array Functions

The introduction of dynamic arrays also brought a suite of powerful new functions that are a joy to use:

  • `FILTER`: Filters a range based on criteria you define.
  • `SORT`: Sorts a range of data.
  • `UNIQUE`: Returns a list of unique values from a range.
  • `SEQUENCE`: Generates a list of sequential numbers.
  • `RANDARRAY`: Generates an array of random numbers.
  • `SORTBY`: Sorts a range based on

    Generate Excel Formulas with AI

    Need help creating formulas? Use ExcelFormula Pro to generate them instantly with AI!

    Try Free